Oracle Associate Statistics and Disassociate Statistics

Oracle Associate / Disassociate Statistics Version 11.2.0.3

General Information

Data Dictionary Objects

ASSOCIATION$	DBA_ASSOCIATIONS	USER_ASSOCIATIONS
ALL_ASSOCIATIONS	DBA_USTATS	USER_USTATS
ALL_USTATS

Associate Statistics

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

Create Column Association

ASSOCIATE STATISTICS WITH COLUMNS <schema.table.column>
USING schema.statistics_type
[WITH <SYSTEM | USER> MANAGED STORAGE TABLES];

TBD

Create Domain Index Association

CREATE TABLE t (comments VARCHAR2(4000));

CREATE INDEX ix_t_domain
ON t(comments)
INDEXTYPE IS ctxsys.context PARAMETERS ('nopopulate');

SELECT table_name, index_type
FROM user_indexes
WHERE table_name = 'T';

desc user_associations

SELECT object_name, object_type, def_cpu_cost, def_io_cost, def_net_cost
FROM user_associations;

ASSOCIATE STATISTICS WITH INDEXES ix_t_domain DEFAULT COST (100,5,1);

SELECT object_name, object_type, def_cpu_cost, def_io_cost, def_net_cost
FROM user_associations;

Create Function Association

CREATE OR REPLACE FUNCTION ftest RETURN VARCHAR2 AUTHID_CURRENT USER IS
BEGIN
RETURN 'FTEST';
END ftest;
/

desc user_associations

SELECT object_name, def_selectivity
FROM user_associations;

ASSOCIATE STATISTICS WITH FUNCTIONS ftest DEFAULT SELECTIVITY 10;

SELECT object_name, def_selectivity
FROM user_associations;

Disassociate Statistics

Use the DISASSOCIATE STATISTICS statement to disassociate default statistics or a statistics type from columns, standalone functions, packages, types, domain indexes, or indextypes.

Statistics Disassociation

DISASSOCIATE STATISTICS FROM FUNCTIONS ftest;

DROP FUNCTION ftest;

Associate Statistics Demo

Based on code from the dbms_application_info page of the library

-- ASSOCIATE STATISTICS WITH FUNCTIONS app_info_wrapper USING ExpressionIndexStats;

INSERT INTO airplanes
SELECT '787', 1, customer_id, order_date, delivered_date
FROM airplanes
WHERE rownum = 1;

exec dbms_application_info.set_client_info('787');

CREATE OR REPLACE FUNCTION app_info_wrapper RETURN VARCHAR2 AUTHID CURRENT_USER IS
x VARCHAR2(64);
BEGIN
dbms_application_info.read_client_info(x);
RETURN x;
END app_info_wrapper;
/

EXPLAIN PLAN SET STATEMENT_ID = 'abc' FOR
SELECT *
FROM airplanes
WHERE program_id = app_info_wrapper;

SELECT * FROM TABLE(dbms_xplan.display('PLAN_TABLE','abc','ALL'));

ASSOCIATE STATISTICS WITH FUNCTIONS app_info_wrapper
DEFAULT SELECTIVITY 100;

EXPLAIN PLAN SET STATEMENT_ID = 'abc' FOR
SELECT *
FROM airplanes
WHERE program_id = app_info_wrapper;

SELECT * FROM TABLE(dbms_xplan.display('PLAN_TABLE','abc','ALL'));

DISASSOCIATE STATISTICS FROM FUNCTIONS app_info_wrapper;

ASSOCIATE STATISTICS WITH FUNCTIONS app_info_wrapper
DEFAULT SELECTIVITY 1;

EXPLAIN PLAN SET STATEMENT_ID = 'abc' FOR
SELECT *
FROM airplanes
WHERE program_id = app_info_wrapper;

SELECT * FROM TABLE(dbms_xplan.display('PLAN_TABLE','abc','ALL'));