Oracle Associate / Disassociate Statistics
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
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 ASSOCIATE STATISTICS WITH <FUNCTIONS|PACKAGES|TYPES|INDEXES|INDEXTYPES>
DEFAULT COST <cpu_cost, io_cost, network_cost>
[WITH <SYSTEM | USER> MANAGED STORAGE TABLES];
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 ASSOCIATE STATISTICS WITH <FUNCTIONS|PACKAGES|TYPES|INDEXES|INDEXTYPES>
DEFAULT SELECTIVITY <default_selectivity>
[WITH <SYSTEM | USER> MANAGED STORAGE TABLES];
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 <columns|functions|packages|types|indexes|
indextypes> <schema.object_name> [FORCE];
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'));

Related Topics
System Events

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved