ASSOCIATE STATISTICS

Purpose

Use the ASSOCIATESTATISTICS statement to associate a statistics type (or default statistics) containing functions relevant to statistics collection, selectivity, or cost with one or more columns, standalone functions, packages, types, domain indexes, or indextypes.

For a listing of all current statistics type associations, query the USER_ASSOCIATIONS data dictionary view. If you analyze the object with which you are associating statistics, then you can also query the associations in the USER_USTATS view.

See Also:

ANALYZE for information on the order of precedence with which ANALYZE uses associations

Prerequisites

To issue this statement, you must have the appropriate privileges to alter the base object (table, function, package, type, domain index, or indextype). In addition, unless you are associating only default statistics, you must have execute privilege on the statistics type. The statistics type must already have been defined.

Specify the statistics type (or a synonym for the type) being associated with column, function, package, type, domain index, or indextype. The statistics_type must already have been created.

The NULL keyword is valid only when you are associating statistics with a column or an index. When you associate a statistics type with an object type, columns of that object type inherit the statistics type. Likewise, when you associate a statistics type with an indextype, index instances of the indextype inherit the statistics type.You can override this inheritance by associating a different statistics type for the column or index. Alternatively, if you do not want to associate any statistics type for the column or index, then you can specify NULL in the using_statistics_type clause.

Specify default costs for standalone functions, packages, types, domain indexes, or indextypes. If you specify this clause, then you must include one number each for CPU cost, I/O cost, and network cost, in that order. Each cost is for a single execution of the function or method or for a single domain index access. Accepted values are integers of zero or greater.

default_selectivity_clause

Specify as a percent the default selectivity for predicates with standalone functions, types, packages, or user-defined operators. The default_selectivity_clause must be a number between 0 and 100. Values outside this range are ignored.

Restriction on the default_selectivity_clauseYou cannot specify DEFAULTSELECTIVITY for domain indexes or indextypes.

Specify WITHSYSTEMMANAGEDSTORAGETABLES to indicate that the storage of statistics data is to be managed by the system. The type you specify in statistics_type should be storing the statistics related information in tables that are maintained by the system. Also, the indextype you specify must already have been created or altered to support the WITHSYSTEMMANAGEDSTORAGETABLES clause.

Specify WITHUSERMANAGEDSTORAGETABLES to indicate that the tables that store the user-defined statistics will be managed by the user. This is the default behavior.

Specifying Default Cost: Example This statement specifies that using the domain index salary_index, created in "Using Extensible Indexing", to implement a given predicate always has a CPU cost of 100, I/O cost of 5, and network cost of 0.