Oracle 8i Data Cartridge Developer's Guide
Release 2 (8.1.6)
Part Number A76937-01

Library
Product
Contents
Index

Go to previous page Go to next page

17
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:

For more complete details on Java functionality, refer to the Oracle8i Supplied Java Packages Reference.

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 17-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 17-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 17-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 17-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 17-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.

Go to previous page Go to next page
Oracle
Copyright © 1996-2000, Oracle Corporation.
All Rights Reserved.

Library
Product
Contents
Index