18
Reference: Extensible Optimizer Interface

This chapter describes the functions and procedures that comprise the interface to the extensible optimizer.

Note on the New Interfaces

In Oracle9i, the extensible optimizer interfaces have changed to support working with partitioned tables and domain indexes. The changes are of two kinds:

• Additional attributes have been added to some of the system-defined object types that are parameters to the ODCIStats interface methods. For example the ODCIColInfo type is enhanced to add information about the column's partition.
• Arguments or semantics of the arguments have changed for some ODCIStats methods. For example, the ODCIStatsDelete interface is changed to add an OUT argument to contain updated aggregate statistics.

You do not need to change your code unless you want to use the new functionality. You must, however, recompile your files and reload the shared library on the server machine, and you must not attempt to use the additional information being passed in any newly added system-type attributes.

If, on the other hand, you do want to use the new Oracle9i functionality, you must update your code for the new attributes added to the various system-defined types, and you must code for the new arguments added to various ODCIStats functions. You must also return 'SYS.ODCISTATS2' in the OUT argument in the ODCIGetInterfaces routine. This tells the server to invoke the version of the ODCIStats methods that uses the new arguments.

Note that you must update your code for the Oracle9i, ODCIStats2 version of the ODCIStats interfaces to use your statistics type with an indextype that implements the ODCIIndex2 version of the extensible indexing interfaces.

The 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 CPU_COST and IO_COST columns 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:

SELECT STATEMENT

TABLE ACCESS

BY ROWID

EMP_TAB

DOMAIN INDEX

EMPRESUME_INDX

300

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

The hint ORDERED_PREDICATES forces the optimizer to preserve the order of predicate evaluation (except predicates 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')

User-Defined ODCIStats Functions

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

ODCIGetInterfaces

Syntax

ODCIGetInterfaces(ifclist OUT ODCIObjectList) RETURN NUMBER

Purpose

ODCIGetInterfaces is invoked by the server to discover which version of the ODCIStats interface the user has implemented in the methods of the user-defined statistics type.

Table 18-1 ODCIGetInterfaces Parameters

Returns

ODCIConst.Success on success, ODCIConst.Error otherwise.

Usage Notes

Different versions of ODCIStats functions are used by Oracle8i and Oracle9i: the Oracle9i version adds parameters to some functions to support working with statistics on partitions of a table or domain index.

For the Oracle9i version of ODCIStats functions, ODCIGetInterfaces must return the string 'SYS.ODCISTATS2' in the ODCIObjectList parameter. This value indicates that the statistics type uses the second version of the ODCIStats interface. Accordingly, the server invokes statistics, cost, or selectivity functions using the Oracle9i interface.

ODCIStatsCollect (Column)

Syntax

FUNCTION ODCIStatsCollect(col ODCIColInfo, options ODCIStatsOptions, statistics OUT RAW) return NUMBER

Purpose

ODCIStatsCollect is called by the ANALYZE command to collect user-defined statistics on a table or a partition of a table.

Table 18-2 ODCIStatsCollect Parameters

Returns

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

Usage Notes

This function should be implemented as a static type method.

If statistics are being collected for only one partition, the TablePartition field in the ODCIColInfo type is filled in with the name of the partition. Otherwise (if statistics need to be collected for all the partitions or for the entire table), the TablePartition field is null.

If the ANALYZE command is executed to collect user-defined statistics on a partitioned table, then n+1 ODCIStatsCollect calls are made, where n is the number of partitions in the table. The first n calls are made with the TablePartition attribute in ODCIColInfo filled in with the partition name and the ODCIStatsOptions.CallProperty set to IntermediateCall. The last call is made with ODCIEnv.CallPropertyflag set to FinalCall to allow you to collect aggregate statistics for the entire table. The OUT statistics in the first call are ignored by the server. The OUT statistics in the subsequent n calls are inserted into the USTATS$ table corresponding to the partitions. The OUT statistics in the last call are the aggregate statistics for the table. The ODCIColInfo.Partition field is NULL in the first and last calls.

If user-defined statistics are being collected for only one partition of the table, two ODCIStatsCollect calls are made. In the first, you should collect statistics for the partition. For this call, the TablePartition attribute of the ODCIColInfo structure is filled in and the ODCIEnv.CallProperty is set to FirstCall. The statistics in the OUT arguments in the ODCIStatsCollect call are inserted into the USTATS$ table corresponding to the partition.

In the second call you can update the aggregate statistics of the table based upon the new statistics collected for the partition. In this call, the ODCIEnv.CallPropertyflag is set to FinalCall to indicate that it is the second call. If you do not want to modify the aggregate statistics, read the aggregate statistics of the table from the catalog and pass that back in the statistics field as the OUT argument. Whatever value is present in the statistics argument is written in the USTATS$ by the server. The ODCIColInfo.TablePartition is filled in with the partition name in both the calls.

Return 'SYS.ODCISTATS2' in the ODCIGetInterfaces call to indicate that you are using the Oracle9i version of the ODCISTATS interface, that supports partitioning.

ODCIStatsCollect (Index)

Syntax

FUNCTION ODCIStatsCollect(ia ODCIIndexInfo, options ODCIStatsOptions, statistics OUT RAW) return NUMBER

Purpose

ODCIStatsCollect is called by the ANALYZE INDEX command to collect user-defined statistics on an index or a partition of an index.

Table 18-3 ODCIStatsCollect Parameters

Returns

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

Usage Notes

This function should be implemented as a static type method.

If statistics are being collected for the entire partitioned index, the IndexPartition field is null, and n+2 calls are made to the ODCIStatsCollect function. This scenario is similar to that described for the column version of ODCIStatsCollect.

If the statistics are being collected for a single partition of the index, the IndexPartition field contains the name of the partition, and two calls are made to the ODCIStatsCollect function. The first call is made to obtain the statistics for the index partition, and the second call is made to obtain the aggregate statistics for the domain index.

To collect statistics on a non-partitioned domain index only a single call is made to the ODCIStatsCollect function.

Return 'SYS.ODCISTATS2' in the ODCIGetInterfaces call to indicate that you are using the Oracle9i version of the ODCISTATS interface, that supports partitioning.

ODCIStatsDelete (Column)

Syntax

FUNCTION ODCIStatsDelete(col ODCIColInfo, statistics OUT RAW, env ODCIEnv) return NUMBER

Purpose

ODCIStatsDelete is called by the ANALYZE <table> DELETE STATISTICS command to delete user-defined statistics on a table or a partition of a table.

Table 18-4 ODCIStatsDelete Parameters

Returns

ODCIConst.Success, ODCIConst.Error, or ODCIConst.Warning.

Usage Notes

This function should be implemented as a static method.

When the function is called for a non-partitioned table, the statistics argument in the ODCIStatsDelete interface is ignored.

If the statistics are being deleted for a partitioned table, the ODCIStatsDelete is called n+1 times. The first n calls are with the partition name filled in in the ODCIColInfo structure and the ODCIEnv.CallProperty set to IntermediateCall. The last call is made with the ODCIEnv.CallProperty set to FinalCall.

The order of operations that you must perform for a delete are the inverse of what you do to collect statistics: In the first call, delete the table-level statistics from your statistics tables; in the intermediate n calls, delete the statistics for the specific partitions; and in the last call drop or clean up any structures created for holding statistics for the deleted table. The ODCIColInfo.TablePartition is set to null in the first and last calls. In the intermediate n calls, the TablePartition field is filled in.

If statistics are being deleted for only one partition, two ODCIStatsDelete calls are made. In each call, ODCIColInfo.TablePartition is filled in with the partition name. On the first call, delete any user-defined statistics collected for that partition. On the second call, update the aggregate statistics for the table and return these aggregate statistics as an OUT argument.

Return 'SYS.ODCISTATS2' in the ODCIGetInterfaces call to indicate that you are using the Oracle9i version of the ODCISTATS interface, that supports partitioning.

ODCIStatsDelete (Index)

Syntax

FUNCTION ODCIStatsDelete(ia ODCIIndexInfo, statistics OUT RAW, env ODCIEnv) return NUMBER

Purpose

ODCIStatsDelete is called by the ANALYZE <table> DELETE STATISTICS command to delete user-defined statistics on an index or a partition of an index.

Table 18-5 ODCIStatsDelete Parameters

Returns

ODCIConst.Success, ODCIConst.Error, or ODCIConst.Warning.

Usage Notes

This function should be implemented as a static method.

When the function is called for a non-partitioned index, the statistics argument in the ODCIStatsDelete interface is ignored.

If statistics are being deleted for a partitioned index, ODCIStatsDelete is called n+2 times. The first and the last call are made with the ODCIEnv.CallProperty set to FirstCall and FinalCall respectively and do not have the partition name set in the ODCIIndexInfo type. The intermediate n calls are made with the partition name filled in in the ODCIIndexInfo structure and the ODCIEnv.CallProperty set to IntermediateCall.

The order of operations that you must perform to delete statistics are the inverse of what you do to collect statistics: In the first call, delete the index-level statistics from your statistics tables; in the intermediate n calls, delete the statistics for the specific partitions; and in the last call drop or clean up any structures created for holding the deleted statistics. The ODCIIndexInfo.IndexPartition is set to null in the first and last calls. In the intermediate n calls, the IndexPartition field is filled in.

If statistics are being deleted for only one partition, two ODCIStatsDelete calls are made. In each call, ODCIIndexInfo.IndexPartition is filled in with the partition name. On the first call, delete any user-defined statistics collected for that partition. On the second call, update the aggregate statistics for the index and return these aggregate statistics as an OUT argument.

Return 'SYS.ODCISTATS2' in the ODCIGetInterfaces call to indicate that you are using the Oracle9i version of the ODCISTATS interface, that supports partitioning.

ODCIStatsFunctionCost

Syntax

FUNCTION ODCIStatsFunctionCost(func ODCIFuncInfo, cost OUT ODCICost, args ODCIArgDescList, <list of function arguments>) return NUMBER

Purpose

Computes the cost of a function.

Table 18-6 ODCIStatsFunctionCost Parameters  

func

cost

args

<list of function 
arguments>

Returns

ODCIConst.Success, ODCIConst.Error, or ODCIConst.Warning.

Usage Notes

This function should be implemented as a static type method.

ODCIStatsIndexCost

Syntax

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 arguments>, env ODCIEnv) return NUMBER

Purpose

Calculates the cost of a domain index scan--either a scan of the entire index or a scan of one or more index partitions if a local domain index has been built.

For each table in the query, the optimizer uses partition pruning to determine the range of partitions that may be accessed. These partitions are called interesting partitions. The set of interesting partitions for a table is also the set of interesting partitions for all domain indexes on that table. The cost of a domain index can depend on the set of interesting partitions, so the optimizer passes a list of interesting index partitions to ODCIStatsIndexCost in the args argument (the type of this argument, ODCIArgDescList, is a list of ODCIArgDesc argument descriptor types) for those arguments that are columns. For non-partitioned domain indexes or for cases where no partition pruning is possible, no partition list is passed to ODCIStatsIndexCost, and you should assume that the entire index will be accessed.

The domain index key can contain multiple column arguments (for example, the indexed column and column arguments from other tables appearing earlier in a join order). For each column appearing in the index key, the args argument contains the list of interesting partitions for the table. For example, for an index key

op(T1.c1, T2.c2) = 1

the optimizer passes a list of interesting partitions for tables T1 and T2 if they are partitioned and there is partition pruning for them.

Table 18-7 ODCIStatsIndexCost Parameters  

ia

sel

cost

qi

pred

args

start

stop

<list of function 
arguments>

env

Returns

ODCIConst.Success, ODCIConst.Error, or ODCIConst.Warning

Usage Notes

• This function should be implemented as a static type method.
• Only a single call is made to the ODCIIndexCost function for queries on partitioned or non-partitioned tables. For queries on partitioned tables, additional information is passed in the ODCIIndexCost function. Note that some partitions in the list passed to ODCIStatsIndexCost may not actually be accessed by the query. The list of interesting partitions chiefly serves to exclude partitions that definitely will not be accessed.
• When the ODCIIndexCost function is invoked, users can fill in a string in the IndexCostInfo field of the cost attribute to supply any additional information that might be helpful. The string (255 characters maximum) is displayed in the OPTIONS column in the EXPLAIN PLAN output when an execution plan chooses a domain index scan.
• Users implementing this function must return 'SYS.ODCISTATS2' in the ODCIGetInterfaces call.

ODCIStatsSelectivity

Syntax

FUNCTION ODCIStatsSelectivity(pred ODCIPredInfo, sel OUT NUMBER, args ODCIArgDescList, start <function_return_type>, stop <function_return_type>, <list of function arguments>, env ODCIEnv) return NUMBER

Purpose

This function specifies the selectivity of a predicate. The selectivity of a predicate involving columns from a single table is the fraction of rows of that table that satisfy the predicate. For predicates involving columns from multiple tables (for example, join predicates), the selectivity should be computed as a fraction of rows in the Cartesian product of those tables.

As in ODCIStatsIndexCost, the args argument contains a list of interesting partitions for the tables whose columns are referenced in the predicate for which the selectivity has to be computed. These interesting partitions are partitions that cannot be eliminated by partition pruning as possible candidates to be accessed. The set of interesting partitions is passed to the function only if partition pruning has occurred (in other words, the interesting partitions are a strict subset of all the partitions).

For example, when ODCIStatsSelectivity is called to compute the selectivity of the predicate:

f(T1.c1, T2.c2) > 4

the optimizer passes the list of interesting partitions for the table T1 (in the argument descriptor for column T1.c1) if partition pruning is possible; similarly for the table T2.

If a predicate contains columns from more than one table, this information is indicated by the flag bit PredMultiTable (new in Oracle9i) set in the Flags attribute of the pred argument.

Table 18-8 ODCIStatsSelectivity Parameters  

Returns

ODCIConst.Success, ODCIConst.Error, or ODCIConst.Warning

Usage Notes

• This function should be implemented as a static type method.
• Users implementing this interface must return 'SYS.ODCISTATS2' in the ODCIGetInterfaces call.
• The selectivity of a predicate involving columns from a single table is the fraction of rows of that table that satisfy the predicate. For predicates involving columns from multiple tables (for example, join predicates), the selectivity should be computed as a fraction of rows in the Cartesian product of those tables. For tables with partition pruning, the selectivity should be expressed relative to the cardinalities of the interesting partitions of the tables involved.

  The selectivity of predicates involving columns on partitioned tables is computed relative to the rows in the interesting partitions. Thus, the selectivity of the predicate

  g(T1.c1) < 5
  

  is the percentage of rows in the set of interesting partitions (or all partitions if no partition pruning is possible) that satisfies this predicate. For predicates with columns from multiple tables, the selectivity must be relative to the number of rows in the cartesian product of the tables.

  For example, consider the predicate:

  f(T1.c1, T2.c2) > 4
  
  

  Suppose that the number of rows in the interesting partitions is 1000 for T1 and 5000 for T2. The selectivity of this predicate must be expressed as the percentage of the 5,000,000 rows in the Cartesian product of T1 and T2 that satisfy the predicate.

• If a predicate contains columns from more than one table, this information is indicated by the flag bit PredMultiTable (new in Oracle9i) set in the Flags attribute of the pred argument.
• A selectivity expressed relative to the base cardinalities of the tables involved may be only an approximation of the true selectivity if cardinalities (and other statistics) of the tables have been reduced based on single-table predicates or other joins earlier in the join order. However, this approximation to the true selectivity should be acceptable to most applications.
• Only one call is made to the ODCIStatsSelectivity function for queries on partitioned or non-partitioned tables. In the case of queries on partitioned tables, additional information is passed while calling the ODCIStatsSelectivity function.