ASSOCIATE STATISTICS

Purpose

Use the ASSOCIATE STATISTICS 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, refer to the USER_ASSOCIATIONS table. If you analyze the object with which you are associating statistics, you can also view the associations in the USER_USTATS table.

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.

See Also: CREATE TYPE for information on defining types

Syntax

column_association::=

function_association::=

using_clause::=

default_cost_clause::=

default_selectivity_clause::=

Keywords and Parameters

column_association

Specify one or more table columns. If you do not specify schema, Oracle assumes the table is in your own schema.

function_association

Specify one or more standalone functions, packages, user-defined datatypes, domain indexes, or indextypes. If you do not specify schema, Oracle assumes the object is in your own schema.

• FUNCTIONS refers only to standalone functions, not to method types or to built-in functions.

• TYPES refers only to user-defined types, not to internal SQL datatypes.

Restriction: You cannot specify an object for which you have already defined an association. You must first disassociate the statistics from this object.

See Also: DISASSOCIATE STATISTICS

using_clause

Specify the statistics type being associated with columns, functions, packages, types, domain indexes, or indextypes. The statistics_type must already have been created.

default_cost_clause

Specify default costs for standalone functions, packages, types, domain indexes, or indextypes. If you specify this clause, 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 must be a whole number between 0 and 100. Values outside this range are ignored.

Restriction: You cannot specify DEFAULT SELECTIVITY for domain indexes or indextypes.

Examples

Standalone Function Example

This statement creates an association for a standalone function FN and causes the optimizer to call the appropriate cost function (if present) in the statistics type stat_fn.

ASSOCIATE STATISTICS WITH FUNCTIONS fn USING stat_fn;

Default Cost Example

This statement specifies that using the domain index t_a to implement a given predicate always has a CPU cost of 100, I/O of 5, and network cost of 0.

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

The optimizer will simply use these default costs instead of calling a cost function.