Oracle Associate / Disassociate Statistics
Version 19.3

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
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 18c
What's New In 19c

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