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];
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;
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'));