Oracle8i Data Cartridge Developer's Guide
Release 8.1.5
A68002-01

Library
Product
Contents
Index

Prev Next

16
Reference -- Extensible Optimizer Interface

This chapter describes the interfaces that are visible to the user and specifies the prototypes of all user-defined functions and procedures:

Extensible Optimizer -- Interface

EXPLAIN PLAN

EXPLAIN PLAN has been enhanced to show the user-defined CPU and I/O costs for domain indexes in the OTHER column of PLAN_TABLE. For example, suppose we have a table Emp_tab and a user-defined operator Contains. Further, suppose that there is a domain index EmpResume_indx on the Resume_col column of Emp_tab, and that the indextype of EmpResume_indx supports the operator Contains. Then, the query 

SELECT * FROM Emp_tab WHERE Contains(Resume_col, 'Oracle') = 1

might have the following plan:

OPERATION   OPTIONS   OBJECT_NAME   OTHER  
SELECT STATEMENT  

 

 

 

TABLE ACCESS  

BY ROWID  

EMP_TAB  

 

DOMAIN INDEX  

 

EMPRESUME_INDX  

CPU: 300, I/O:4  

INDEX Hint

The index hint will apply to domain indexes. In other words, the index hint will force the optimizer to use the hinted index for a user-defined operator, if possible.

ORDERED_PREDICATES Hint

A new hint, called ORDERED_PREDICATES, will be introduced. This hint will force the optimizer to preserve the order of predicate evaluation (except those used for index keys) as specified in the WHERE clause of a SQL DML statement.

Example

Consider an example of how the statistics functions might be used. Suppose, in the schema SCOTT, we define the following: 

CREATE OPERATOR Contains binding (VARCHAR2(4000), VARCHAR2(30))
   RETURN NUMBER USING Contains_fn;

CREATE TYPE stat1 (
   ...,
   STATIC FUNCTION ODCIStatsSelectivity(pred ODCIPredInfo, sel OUT NUMBER,
      args ODCIArgDescList, start NUMBER, stop NUMBER, doc VARCHAR2(4000),
      key VARCHAR2(30)) return NUMBER,
   STACTIC FUNCTION ODCIStatsFunctionCost(func ODCIFuncInfo, cost OUT
      ODCICost, args ODCIArgDescList, doc VARCHAR2(4000), key VARCHAR2(30))
      return NUMBER,
   STATIC FUNCTION ODCIStatsIndexCost(ia ODCIIndexInfo, sel NUMBER,
      cost OUT ODCICost, qi ODCIQueryInfo, pred ODCIPredInfo,
      args ODCIArgDescList, start NUMBER, stop NUMBER,
      key VARCHAR2(30)) return NUMBER,
   ...
);

CREATE TABLE T (resume VARCHAR2(4000));

CREATE INDEX T_resume on T(resume) INDEXTYPE IS indtype;

ASSOCIATE STATISTICS WITH FUNCTIONS Contains_fn USING stat1;

ASSOCIATE STATISTICS WITH INDEXES T_resume USING stat1;

When the optimizer encounters the query 

SELECT * FROM T WHERE Contains(resume, 'ORACLE') = 1,

it will compute the selectivity of the predicate by invoking the user-defined selectivity function for the functional implementation of the Contains operator. In this case, the selectivity function is stat1.ODCIStatsSelectivity. It will be called as follows: 

stat1.ODCIStatsSelectivity (
   ODCIPredInfo('SCOTT', 'Contains_fn', NULL, 29),
   sel,
   ODCIArgDescList(
      ODCIArgDesc(ODCIConst.ArgLit, NULL, NULL, NULL),
      ODCIArgDesc(ODCIConst.ArgLit, NULL, NULL, NULL),
      ODCIArgDesc(ODCIConst.ArgCol, 'T', 'SCOTT', '"resume"'),
      ODCIArgDesc(ODCIConst.ArgLit, NULL, NULL, NULL)),
      1,
      1,
      NULL,
      'ORACLE')

Suppose the selectivity function returns a selectivity of 3 (percent). When the domain index is being evaluated, then the optimizer will call the user-defined index cost function as follows: 

stat1.ODCIStatsIndexCost (
   ODCIIndexInfo('SCOTT', 'T_resume',
      ODCIColInfoList(ODCIColInfo('SCOTT', 'T', '"resume"', NULL, NULL))),
      3,
      cost,
      NULL,
      ODCIPredInfo('SCOTT', 'Contains', NULL, 13),
      ODCIArgDescList( ODCIArgDesc(ODCIConst.ArgLit, NULL, NULL, NULL),
                       ODCIArgDesc(ODCIConst.ArgLit, NULL, NULL, NULL),
                       ODCIArgDesc(ODCIConst.ArgLit, NULL, NULL, NULL)),
      1,
      1,
      'ORACLE')

Suppose that the optimizer decides not to use the domain index because it is too expensive. Then it will call the user-defined cost function for the functional implementation of the operator as follows: 

stat1.ODCIStatsFunctionCost (
   ODCIFuncInfo('SCOTT', 'Contains_fn', NULL, 1),
   cost,
   ODCIArgDescList(  ODCIArgDesc(ODCIConst.ArgCol, 'T', 'SCOTT', '"resume"'),
                     ODCIArgDesc(ODCIConst.ArgLit, NULL, NULL, NULL)),
   NULL,
   'ORACLE')
The following sections describe each statistics type function in greater detail.

User-Defined ODCIStats Functions

User-defined ODCIStats functions are used for table columns, functions, package, type, indextype or domain indexes. These functions are described below.

ODCIStatsCollect

User-defined statistics are collected by defining a function with the prototypes: 

FUNCTION ODCIStatsCollect(col ODCIColInfo, options ODCIStatsOptions,
   statistics OUT RAW) return NUMBER
FUNCTION ODCIStatsCollect(ia ODCIIndexInfo, options ODCIStatsOptions,
   statistics OUT RAW) return NUMBER
Table 16-1 ODCIStatsCollect Parameters
Parameter  Meaning 

col  

column for which statistics are being collected  

ia  

domain index for which statistics are being collected  

options  

options passed to ANALYZE  

statistics  

user-defined statistics collected  

The function returns ODCIConst.Success, ODCIConst.Error, or ODCIConst.Warning.

Usage Notes

This function should be implemented as a static type method.

ODCIStatsDelete

User-defined statistics are deleted by the ANALYZE command by calling the following user-defined functions: 

   FUNCTION ODCIStatsDelete(col ODCIColInfo) return NUMBER
   FUNCTION ODCIStatsDelete(ia ODCIIndexInfo) return NUMBER
Table 16-2 ODCIStatsDelete Parameters
Parameter  Meaning 

col  

column for which statistics are being deleted  

ia  

domain index for which statistics are being deleted  
The function returns ODCIConst.Success, ODCIConst.Error, or ODCIConst.Warning.

Usage Notes

This function should be implemented as a static type method.

ODCIStatsSelectivity

A user-defined selectivity function can be specified for a user-defined function or type method. The prototype for a user-defined selectivity function is as follows: 

FUNCTION ODCIStatsSelectivity(pred ODCIPredInfo, sel OUT NUMBER, args
   ODCIArgDescList, start <function_return_type>, stop <function_return_type>,
   <list of function arguments>) return NUMBER
Table 16-3 ODCIStatsSelectivity Parameters
Parameter  Meaning 

pred  

predicate for which the selectivity is being computed  

sel  

the computed selectivity, expressed as a percent, in whole numbers between (and including) 0 and 100  

args  

descriptor of start, stop, and actual arguments with which the function, type method, or operator was called. If the function has n arguments, the args array will contain n+2 elements, the first element describing the start value, the second element describing the stop value, and the remaining n elements describing the actual arguments of the function, method, or operator  

start  

lower bound of the function (e.g., 2 for a predicate fn(...) > 2)  

stop  

upper bound of the function (e.g., 5 for a predicate fn(...) < 5)  

<list of function arguments>  

list of actual parameters to the function or type method; the number, position, and type of each argument must be the same as in the function, type method, or operator  
The function returns ODCIConst.Success, ODCIConst.Error, or ODCIConst.Warning.

Usage Notes

This function should be implemented as a static type method.

ODCIStatsFunctionCost

The cost of a function is computed by a function with the following prototype: 

   FUNCTION ODCIStatsFunctionCost(func ODCIFuncInfo, cost OUT ODCICost,
      args ODCIArgDescList, <list of function arguments>) return NUMBER
Table 16-4 ODCIStatsFunctionCost Parameters
Parameter  Meaning  
func
 

function or type method for which the cost is being computed  

 
cost
 

computed cost (must be positive whole numbers)  

 
args
 

descriptor of actual arguments with which the function or type method was called. If the function has n arguments, the args array will contain n elements, each describing the actual arguments of the function or type method  

 
<list of function 
arguments>
 

list of actual parameters to the function or type method; the number, position, and type of each argument must be the same as in the function or type method  
The function returns ODCIConst.Success, ODCIConst.Error, or ODCIConst.Warning.

Usage Notes

This function should be implemented as a static type method.

ODCIStatsIndexCost

The cost of using a domain index is computed by a function with the following prototype: 

FUNCTION ODCIStatsIndexCost(ia ODCIIndexInfo, sel NUMBER,
   cost OUT ODCICost, qi ODCIQueryInfo, pred ODCIPredInfo, args
   ODCIArgDescList, start <operator_return_type>,
   stop <operator_return_type>, <list of operator value arguments>)
   return NUMBER
Table 16-5 ODCIStatsIndexCost Parameters
Parameter  Meaning  
ia
 

domain index for which statistics are being collected  

 
sel
 

the user-computed selectivity of the predicate  

 
cost
 

computed cost (must be positive whole numbers)  

 
qi
 

information about the query  

 
args
 

descriptor of start, stop, and actual value arguments with which the operator was called. If the operator has n arguments, the args array will contain n+1 elements, the first element describing the start value, the second element describing the stop value, and the remaining n-1 elements describing the actual value arguments of the operator (i.e., the arguments after the first)  

 
start
 

lower bound of the operator (e.g., 2 for a predicate fn(...) > 2)  

 
stop
 

upper bound of the operator (e.g., 5 for a predicate fn(...) < 5)  

 
<list of function 
arguments>
 

list of actual parameters to the operator (excluding the first); the number, position, and type of each argument must be the same as in the operator  
The function returns ODCIConst.Success, ODCIConst.Error, or ODCIConst.Warning.

Usage Notes

This function should be implemented as a static type method.


Prev
Next
Oracle
Copyright © 1999 Oracle Corporation.
All Rights Reserved.

Library
Product
Contents
Index