Using User-defined Statistics, Selectivity, and Cost

Statistics types act as interfaces for user-defined functions that influence the choice of an execution plan by the optimizer. However, for the optimizer to be able to use a statistics type requires a mechanism to bind the statistics type to a database object (column, stand-alone function, object type, index, indextype or package). This is the function of the new ASSOCIATE STATISTICS command. The following sections describe this command in more detail.

User-defined Statistics

User-defined statistics functions are relevant for columns (both standard SQL datatypes and object types) and domain indexes. Statistics types used to collect user-defined statistics need not have the ODCIStatsSelectivity, ODCIStatsFunctionCost, and ODCIStatsIndexCost functions (they are ignored). The sections below describe how column and index user-defined statistics are collected.

User-collected statistics can either be stored in some predefined dictionary tables or users could create their own tables. The latter approach requires that privileges on these tables be administered properly, backup and restoration of these tables be done along with other dictionary tables, and point-in-time recovery considerations be resolved.

To ease the administration overhead, a predefined table, USTATS$, is created where you can store statistics. These statistics are not interpreted by the system; they are used by user-defined selectivity and cost functions. In addition to using these predefined tables, nothing prevents you from creating and administering your own tables to store the statistics. Details on the USTATS$ table are given in the Extensible Optimizer reference.

Column Statistics

Consider a table Test_tab defined as follows:

CREATE TABLE Test_tab (
   col_a    NUMBER,
   col_b    typ1,
   col_c    VARCHAR2(2000)
)

where typ1 is an object type. Suppose that stat is a statistics type with ODCIStatsCollect and ODCIStatsDelete functions. User-defined statistics are collected by the ANALYZE command for the column col_b if we bind a statistics type with the column as follows:

ASSOCIATE STATISTICS WITH COLUMNS Test_tab.col_b USING stat

A list of columns can be associated with the statistics type stat. Note that Oracle supports only associations with top-level columns, not attributes of object types; if you wish, the ODCIStatsCollect function can collect individual attribute statistics by traversing the column.

Another way to collect user-defined statistics is to declare an association with a datatype as follows:

ASSOCIATE STATISTICS WITH TYPES typ1 USING stat_typ1

which declares stat_typ1 as the statistics type for the type typ1. When the table Test_tab is analyzed with this association, user-defined statistics are collected for the column col_b using the ODCIStatsCollect function of statistics type stat_typ1.

Individual column associations always have precedence over associations with types. Thus, in the above example, if both ASSOCIATE STATISTICS commands are issued, ANALYZE would use the statistics type stat (and not stat_typ1) to collect user-defined statistics for column col_b. It is also important to note that standard statistics, if possible, are collected along with user-defined statistics.

User-defined statistics are deleted using the ODCIStatsDelete function from the same statistics type that was used to collect the statistics.

Associations defined by the ASSOCIATE STATISTICS command are stored in a new dictionary table called ASSOCIATION$ (details are given in the Extensible Optimizer reference).

Only user-defined datatypes can have statistics types associated with them; you cannot declare associations for standard SQL datatypes.

Domain Index Statistics

A domain index has an indextype. A statistics type for a domain index is defined by associating it either with the index or its indextype. Consider the following example using the table Test_tab we defined earlier:

CREATE INDEX Test_indx ON Test_tab(col_a)
INDEXTYPE IS indtype PARAMETERS('example');

CREATE OPERATOR userOp BINDING (NUMBER) RETURN NUMBER
USING userOp_func;

CREATE INDEXTYPE indtype
FOR userOp(NUMBER)
USING imptype;

Here, indtype is the indextype, userOp is a user-defined operator supported by indtype, userOp_func is the functional implementation of userOp, and imptype is the implementation type of the indextype indtype.

A statistics type stat_Test_indx can be associated with the index Test_indx as follows:

ASSOCIATE STATISTICS WITH INDEXES Test_indx USING stat_Test_indx 

When the domain index Test_indx is analyzed, user-defined statistics for the index are collected by calling the ODCIStatsCollect function of stat_Test_indx.

If a statistics type association is not defined for a specific index, Oracle looks for a statistics type association for the indextype of the index. In the above example, a statistics type stat_indtype can be associated with the indextype indtype as follows:

ASSOCIATE STATISTICS WITH INDEXTYPES indtype USING stat_indtype

When the domain index Test_indx is analyzed and no statistics type association has been defined for the index Test_indx, then user-defined statistics for the index are collected by calling the ODCIStatsCollect function of stat_indtype.

Thus, individual domain index associations always have precedence over associations with the corresponding indextypes.

Domain index statistics are dropped using the ODCIStatsDelete function from the same statistics type that was used to collect the statistics.

User-defined Selectivity

Selectivity functions are used by the optimizer to compute the selectivity of predicates in a query. The predicates must have one of the appropriate forms and can contain user-defined operators, stand-alone functions, package functions, or type methods. Selectivity computation for each is described below.

User-defined Operators

Consider the example laid out earlier, and suppose that the following association is declared:

ASSOCIATE STATISTICS WITH FUNCTIONS userOp_func USING stat_userOp_func

Now, if the following predicate

userOp(Test_tab.col_a) = 1

is encountered, the optimizer calls the ODCIStatsSelectivity function (if present) in the statistics type stat_userOp_func that is associated with the functional implementation of the userOp_func of the userOp operator.

Stand-Alone Functions

If the association

ASSOCIATE STATISTICS WITH FUNCTIONS myFunction USING stat_MyFunction

is declared for a stand-alone function myFunction, then the optimizer calls the ODCIStatsSelectivity function (if present) in the statistics type stat_myFunction for the following predicate (for instance):

myFunction(Test_tab.col_a, 'TEST') = 1.

Package Functions

If the association

ASSOCIATE STATISTICS WITH PACKAGES Demo_pack USING stat_Demo_pack

is declared for a package Demo_pack, then the optimizer calls the ODCIStatsSelectivity function (if present) in the statistics type stat_Demo_pack for the following predicate (for instance):

Demo_pack.myDemoPackFunction(Test_tab.col_a, 'TEST') = 1

where myDemoPackFunction is a function in Demo_pack.

Type Methods

If the association

ASSOCIATE STATISTICS WITH TYPES Example_typ USING stat_Example_typ

is declared for a type Example_typ, then the optimizer calls the ODCIStatsSelectivity function (if present) in the statistics type stat_Example_typ for the following predicate (for instance):

myExampleTypMethod(Test_tab.col_b) = 1

where myExampleTypMethod is a method in Example_typ.

Default Selectivity

An alternative to selectivity functions is user-defined default selectivity. The default selectivity is a value (between 0% and 100%) that is looked up by the optimizer instead of calling a selectivity function. Default selectivities can be used for predicates with user-defined operators, stand-alone functions, package functions, or type methods.

The following command:

ASSOCIATE STATISTICS WITH FUNCTIONS myFunction DEFAULT SELECTIVITY 20

declares that the following predicate, for instance,

myFunction(Test_tab.col_a) = 1

always has a selectivity of 20 percent (or 0.2) regardless of the parameters of myFunction, or the comparison operator "=", or the constant "1". The optimizer uses this default selectivity instead of calling a selectivity function.

An association can be declared using either a statistics type or a default selectivity, but not both. Thus, the following statement is illegal:

ASSOCIATE STATISTICS WITH FUNCTIONS myFunction USING stat_myFunction
   DEFAULT SELECTIVITY 20

The following are some more examples of default selectivity declarations:

ASSOCIATE STATISTICS WITH PACKAGES Demo_pack DEFAULT SELECTIVITY 20
ASSOCIATE STATISTICS WITH TYPES Example_typ DEFAULT SELECTIVITY 20

User-defined Cost

The optimizer uses user-defined cost functions to compute the cost of predicates in a query. The predicates must have one of the forms listed earlier and can contain user-defined operators, stand-alone functions, package functions, or type methods. In addition, user-defined cost functions are also used to compute the cost of domain indexes. Cost computation for each is described below.

User-defined Operators

Consider the example outlined above, and suppose that the following associations are declared:

ASSOCIATE STATISTICS WITH INDEXES Test_indx USING stat_Test_indx
ASSOCIATE STATISTICS WITH FUNCTIONS userOp USING stat_userOp_func

Consider the following predicate:

userOp(Test_tab.col_a) = 1.

If the domain index Test_indx implementing userOp is being evaluated, the optimizer calls the ODCIStatsIndexCost function (if present) in the statistics type stat_Test_indx. If the domain index is not used, however, the optimizer calls the ODCIStatsFunctionCost function (if present) in the statistics type stat_userOp to compute the cost of the functional implementation of the operator userOp.

Stand-Alone Functions

If the association

ASSOCIATE STATISTICS WITH FUNCTIONS myFunction USING stat_myFunction

is declared for a stand-alone function myFunction, then the optimizer calls the ODCIStatsFunctionCost function (if present) in the statistics type stat_myFunction for the following predicate (for instance):

myFunction(Test_tab.col_a, 'TEST') = 1

User-defined function costs do not influence the choice of access methods; they are only used for ordering predicates (described in the Extensible Optimizer reference).

Package Functions

If the association

ASSOCIATE STATISTICS WITH PACKAGES Demo_pack USING stat_Demo_pack;

is declared for a package Demo_pack, then the optimizer calls the ODCIStatsFunctionCost function (if present) in the statistics type stat_Demo_pack for the following predicate (for instance):

Demo_pack.myDemoPackFunction(Test_tab.col_a) = 1

where myDemoPackFunction is a function in Demo_pack.

Type Methods

If the association

ASSOCIATE STATISTICS WITH TYPES Example_typ USING stat_Example_typ;

is declared for a type Example_typ, then the optimizer calls the ODCIStatsFunctionCost function (if present) in the statistics type stat_Example_typ for the following predicate:

myExampleTypMethod(Test_tab.col_b) = 1

where myExampleTypMethod is a method in Example_typ.

Default Cost

Like default selectivity, default costs can be used for predicates with user-defined operators, stand-alone functions, package functions, or type methods. So, the following command

ASSOCIATE STATISTICS WITH INDEXES Test_indx DEFAULT COST (100, 5, 0)

declares that using the domain index Test_indx to implement the following predicate (to select one example)

userOp(Test_tab.col_a) = 1

always has a CPU cost of 100, I/O of 5, and network of 0 (the network cost is ignored in Oracle8i) regardless of the parameters of userOp, the comparison operator "=", or the constant "1". The optimizer uses this default cost instead of calling an ODCIStatsIndexCost cost function.

You can declare an association using either a statistics type or a default cost, not both. Thus, the following statement is illegal:

ASSOCIATE STATISTICS WITH INDEXES Test_indx USING stat_Test_indx
   DEFAULT COST (100, 5, 0)

The following are some more examples of default cost declarations:

ASSOCIATE STATISTICS WITH FUNCTIONS myFunction DEFAULT COST (100, 5, 0)
ASSOCIATE STATISTICS WITH PACKAGES Demo_pack DEFAULT COST (100, 5, 0)
ASSOCIATE STATISTICS WITH TYPES Example_typ DEFAULT COST (100, 5, 0)
ASSOCIATE STATISTICS WITH INDEXTYPES indtype DEFAULT COST (100, 5, 0)