Oracle Associate / Disassociate Statistics
Version 21c

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.
Be sure to view the full listing of monographs in Morgan's Library
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));

Table created.

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

Index created.

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

TABLE_NAME      INDEX_TYPE
--------------- ---------------
T               DOMAIN


desc user_associations

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

no rows selected

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

Statistics associated.

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

OBJECT_NAME  OBJECT_TYPE  DEF_CPU_COST DEF_IO_COST DEF_NET_COST
------------ ------------ ------------ ----------- ------------
IX_T_DOMAIN  INDEX                 100           5            1
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;

OBJECT_NAME                    DEF_SELECTIVITY
------------------------------ ---------------
IX_T_DOMAIN


ASSOCIATE STATISTICS WITH FUNCTIONS ftest DEFAULT SELECTIVITY 10;

Statistics associated.

SELECT object_name, def_selectivity
FROM user_associations;

OBJECT_NAME                    DEF_SELECTIVITY
------------------------------ ---------------
IX_T_DOMAIN
FTEST                                       10
 
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;

Statistics disassociated.

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 19c
What's New In 20c-21c

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