Oracle | Associate Statistics and Disassociate Statistics

Oracle Associate / Disassociate Statistics Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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