Statistics for tables and indexes can be generated by using the DBMS_STATS package. In general, the more accurate the statistics, the better the execution plan generated by the optimizer.

The optimizer uses statistics to calculate the selectivity of predicates. The selectivity is the fraction of rows in a table or partition that is chosen by the predicate. It is a number between 0 and 1. The selectivity of a predicate is used to estimate the cost of a particular access method; it is also used to determine the optimal join order. A poor choice of join order by the optimizer could result in a very expensive execution plan.

Currently, the optimizer uses a standard algorithm to estimate the selectivity of selection and join predicates. However, the algorithm does not always work well in cases in which predicates contain functions or type methods. In addition, predicates can contain user-defined operators about which the optimizer does not have any information. In that case the optimizer cannot compute an accurate selectivity.

The optimizer estimates the cost of various access paths to choose an optimal plan. For example, it computes the CPU and I/O cost of using an index and a full table scan to choose between the two. However the optimizer does not know the internal storage structure of domain indexes, and so it cannot compute a good estimate of the cost of a domain index.

There are two user-defined statistics collection functions, one for collecting statistics and the other for deleting them.

The first, ODCIStatsCollect(), is used to collect user-defined statistics; its interface depends on whether a column or domain index is being analyzed. It is called when analyzing a column of a table or a domain index and takes two parameters:

• col for the column being analyzed, or ia for the domain index being analyzed;

• options for options specified in the DBMS_STATS package.

As mentioned, the database does not interpret statistics collected by ODCIStatsCollect(). For system-managed domain index statistics, you don't return the statistics collected by ODCIStatsCollect(). You should store these statistics in a user-managed format, as described in Generating Statistics for System-Managed Domain Indexes, and illustrated in Figure 10-1, Figure 10-2, and Figure 10-3.

User-collected statistics are deleted by calling the ODCIStatsDelete() function whose interface depends on whether the statistics for a column or domain index are being dropped. It takes a single parameter: col, for the column whose user-defined statistics must be deleted, or ia, for the domain index whose statistics are to be deleted.

If a user-defined ODCIStatsCollect() function is present in a statistics type, the corresponding ODCIStatsDelete() function must also be present.

The return values of the ODCIStatsCollect() and ODCIStatsDelete() functions must be Success, Error, or Warning; these return values are defined in a system package ODCIConst.

User-defined selectivity functions are used only for predicate forms listed in Example 10-1.

A user-defined selectivity function ODCIStatsSelectivity() takes five sets of input parameters that describe the predicate:

• The pred parameter describes the function operator and the relational operator relational_operator.

• The args parameter describes the start and stop values (that is, <constant>) of the function and the actual arguments to the function (operator()).

• The start parameter, whose data type is identical to that of the function's return value, describes the start value of the function.

• The stop parameter, whose data type is identical to that of the function's return value, describes the stop value of the function.

• A list of function arguments whose number, position, and type must match the arguments of the function operator.

The computed selectivity is returned in the output parameter sel as a number between 0 and 100 (inclusive) that represents a percentage. The optimizer ignores numbers less than 0 or greater than 100 as invalid values. If the computed selectivity is less than 0.5%, a value of 0 may be returned in the output parameter sel. A selectivity of 0 does not mean that the predicate will be removed.

The return value of the ODCIStatsSelectivity() function must be one of Success, Error, or Warning.

As an example, consider a function myFunction, as defined in Example 10-3.

A user-defined selectivity function ODCIStatsSelectivity() is detailed in Extensible Optimizer Interface.

If myFunction() is called using literal arguments, such as myFunction(2, 'TEST') > 5, then the selectivity function is called as out lined in Example 10-4.

If, on the other hand, myFunction() is called with some non-literals arguments, such as myFunction(Test_tab.col_a, 'TEST')> 5, where col_a is a column in table Test_tab, then the selectivity function is called as outlined in Example 10-5.

In summary, the start, stop, and function argument values are passed to the selectivity function only if they are literals; otherwise they are NULL. ODCIArgDescList describes all the arguments that follow it.

myFunction (a NUMBER, b VARCHAR2(10)) return NUMBER

ODCIStatsSelectivity(ODCIPredInfo_constructor, sel,
   ODCIArgDescList_constructor, 5, NULL, 2, 'TEST', ODCIEnv_flag)

ODCIStatsSelectivity(ODCIPredInfo_constructor, sel,
   ODCIArgDescList_constructor, 5, NULL, NULL, 'TEST', ODCIEnv_flag)

User-defined cost functions are only used for predicate forms listed in Example 10-1.

You can define a function, ODCIStatsFunctionCost(), for computing the cost of standalone functions, package functions, or type methods. This function takes three sets of input parameters describing the predicate:

• The func parameter describes the function operator.

• The args parameter describes the actual arguments to the function operator.

• A list of function arguments whose number, position, and type must match the arguments of the function operator.

The ODCIStatsFunctionCost() function returns its computed cost in the cost parameter. The returned cost can have two components, a CPU cost and an I/O cost, which are combined by the optimizer to compute a composite cost. The costs returned by user-defined cost functions must be positive whole numbers. Invalid values are ignored by the optimizer.

The return value of the ODCIStatsFunctionCost() function must be one of Success, Error, or Warning.

Consider a myFunction(), defined in Example 10-3.

A user-defined cost function ODCIStatsFunctionCost() is detailed in Extensible Optimizer Interface.

If myFunction() is called using literal arguments, such as myFunction(2, 'TEST') > 5, where col_a is a column in table Test_tab, then the cost function is called as out lined in Example 10-6.

If, on the other hand, myFunction() is called with non-literal arguments, such as myFunction(Test_tab.col_a, 'TEST') > 5, where col_a is a column in table Test_tab, then the cost function is called as out lined in Example 10-7.

In summary, function argument values are passed to the cost function only if they are literals; otherwise, they are NULL. ODCIArgDescList describes all the arguments that follow it.

ODCIStatsFunctionCost(ODCIFuncInfo_constructor, cost,
   ODCIArgDescList_constructor, 2, 'TEST', ODCIEnv_flag)

ODCIStatsFunctionCost(ODCIFuncInfo_constructor, cost,
   ODCIArgDescList_constructor, NULL, 'TEST', ODCIEnv_flag)

User-defined cost functions for domain indexes are used for the same type of predicates mentioned previously, except that operator must be a user-defined operator for which a valid domain index access path exists.

The ODCIStatsIndexCost() function takes these sets of parameters:

• ia describing the domain index

• sel representing the user-computed selectivity of the predicate

• cost giving the computed cost

• qi containing additional information about the query

• pred describing the predicate

• args describing the start and stop values (that is, <constant>) of the operator and the actual arguments to the operator operator

• start, whose data type is identical to that of the operator's return value, describing the start value of the operator

• stop whose data type is identical to that of the operator's return value, describing the stop value of the operator

• a list of operator value arguments whose number, position, and type must match the arguments of the operator operator. The value arguments of an operator are the arguments excluding the first argument.

• env, an environment flag set by the server to indicate which call is being made in cases where multiple calls are made to the same routine. The flag is reserved for future use; currently it is always set to 0.

The computed cost of the domain index is returned in the output parameter, cost.

ODCIStatsIndexCost() returns Success, Error or Warning.

Consider an operator defined in Example 10-8, which returns 1 or 0 depending on whether or not the string b_string is contained in the string a_string. Further, assume that the operator is implemented by a domain index.

A user-defined index cost function ODCIStatsIndexCost() is detailed in Extensible Optimizer Interface.

If contains() is called using non-literal arguments, such as Contains(Test_tab.col_c,'TEST') <= 1, then the index cost function is called as out lined in Example 10-9.

Note that the first argument, a_string, of Contains does not appear as a parameter of ODCIStatsIndexCost(). This is because the first argument to an operator must be a column for the domain index to be used, and this column information is passed in through the ODCIIndexInfo parameter. Only the operator arguments after the first (the value arguments) must appear as parameters to the ODCIStatsIndexCost() function.

In summary, the start, stop, and operator argument values are passed to the index cost function only if they are literals; otherwise they are NULL. ODCIArgDescList describes all the arguments that follow it.

Contains(a_string VARCHAR2(2000), b_string VARCHAR2(10))

ODCIStatsIndexCost(ODCIIndexInfo_constructor, sel, cost,
   ODCIQueryInfo_constructor, ODCIPredInfo_constructor, 
   ODCIArgDescList_constructor, NULL, 1, 'TEST', ODCIEnv_flag)

If you choose the system-managed approach to maintain domain indexes and must associate a statistics type with the domain index or the indextype, then the statistics type must also be managed by the system.

Statistics may be collected when issuing an ODCIStatsCollect() call for a system-managed domain index. For a non-partitioned index, the statistics may be stored with the index storage table, as a separate table, or in a data cartridge metadata table with index name qualified rows.

For local partitioned domain indexes, there are three options for storing statistics. All use the ODCIStatsUpdPartStatistics() method during a partition maintenance operation in the following ways. Please note that in all the following examples, no DDLs are executed inside the ODCIStatsUpdPartStatistics() call, and only DML and query instructions are allowed in the implementation of ODCIStatsUpdPartStatistics().

User-defined statistics functions are relevant for columns that use both standard SQL data types and object types, and for domain indexes. The functions ODCIStatsSelectivity(), ODCIStatsFunctionCost(), and ODCIStatsIndexCost() are not used for user-defined statistics, so statistics types used only to collect user-defined statistics need not implement these functions.

Users could create their own tables. This 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.

The optimizer uses selectivity functions to compute the selectivity of predicates in a query. The predicates must have one of the appropriate forms and can contain user-defined operators, standalone functions, package functions, or type methods.

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, standalone functions, package functions, or type methods. In addition, user-defined cost functions are also used to compute the cost of domain indexes.

An association of a statistics type defined for an indextype or object type is inherited by index instances of that indextype and by columns of that object type. An inherited association can be overridden by explicitly defining a different association for an index instance or column, but there may be occasions when you would prefer an index or column not to have any association at all. For example, for a particular query the benefit of a better plan may not outweigh the additional compilation time incurred by invoking the cost or selectivity functions. For cases like this, you can use the ASSOCIATE command to declare a NULL association for a column or index, as in Example 10-25.

If the NULL association is specified, the schema object does not inherit any statistics type from the column type or the indextype. A NULL association also precludes default values.

ASSOCIATE STATISTICS WITH COLUMNS columns NULL;
ASSOCIATE STATISTICS WITH INDEXES indexes NULL;

Partition-level and schema object-level aggregate statistics are affected by DDL operations in the same way as standard statistics. Table 10-2 summarizes the effects.

ADD PARTITION

DROP PARTITION

SPLIT PARTITION

MERGE PARTITION

TRUNCATE PARTITION

EXCHANGE PARTITION

REBUILD PARTITION

MOVE PARTITION

RENAME PARTITION

If an existing partition is exchanged, or dropped with an ALTER TABLE DROP PARTITION statement, and the _minimal_stats_aggregation parameter is set to FALSE, the statistics for that partition are deleted, and the aggregate statistics of the table or index are recalculated.

Oracle's distributed implementation does not support adding functions to the remote capabilities list. All functions referencing remote tables are executed as filters. The placement of the filters occurs outside the optimizer. The cost model reflects this implementation and does not attempt to optimize placement of these predicates.

Since predicates are not shipped to the remote site, you cannot use domain indexes on remote tables. Therefore, the DESCRIBE protocol is unchanged, and remote domain indexes are not visible from the local site.

If you are creating an indextype WITH SYSTEM MANAGED STORAGE TABLES, you should also create its associated statistics type WITH SYSTEM MANAGED STORAGE TABLES. If you are collecting statistics on the local indexed column using system partitioned tables, then the Oracle server maintains the system-partitioned statistics tables for them during partition maintenance operations. You can only use the WITH SYSTEM MANAGED STORAGE TABLES option when an indextype is associated with the statistics type; otherwise the system raises an error.

When using local indexes, it may be useful to maintain both partition-level and aggregate object-level statistics. During partition maintenance operations, the partition level statistics are deleted, while the aggregate object-level statistics are either adjusted to reflect the operation or left "as is" for later recomputation.

The decision to adjust or recompute the aggregate statistics is made based on _minimal_stats_aggregation parameter in the server. If the parameter is FALSE, the aggregate statistics are recomputed. If the parameter is TRUE, the statistics are not recomputed.

The system-managed domain indexing approach supports system-managed statistics that are associated with indextypes; indextype itself should also be system-managed.

The cost of execution of the queries remains the same with the extensible optimizer if the same plan is chosen. If a different plan is chosen, the execution time should be better assuming that the user-defined cost, selectivity, and statistics collection functions are accurate. In light of this, you are strongly encouraged to provide statistics collection, selectivity, and cost functions for user-defined structures because the optimizer defaults can be inaccurate and lead to an expensive execution plan.