Oracle | Associate Statistics and Disassociate Statistics

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 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 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.

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