Oracle Associate / Disassociate Statistics
Version 18.3.0.1

General Information
Library Note Morgan's Library Page Header
Coming to OpenWorld 2018? Be sure to visit the TidalScale booth in Moscone South and learn how to solve performance problems and lower costs with Software Defined Servers. Before you visit the booth, or if you can't make it this year, check out TidalScale at www.tidalscale.com. Be sure to click on the Solutions link and look through the Oracle resources.
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
Built-in Functions
Built-in Packages
System Events
What's New In 12cR2
What's New In 18cR1

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-2017 Daniel A. Morgan All Rights Reserved