ASSOCIATE STATISTICS

Associate a type of statistics (or default statistics) with an object.
This allows you to override the CBO on a per object basis (rather than using hints for every SQL statement.)

Syntax:

   ASSOCIATE STATISTICS WITH
      COLUMNS [schema.]table_column,…
         USING [schema.]statistics_type;

   ASSOCIATE STATISTICS WITH
      object [schema.]object_name,…
         cost_usage_clause;

    Where object is any of
   
      FUNCTIONS
      PACKAGES
      TYPES
      INDEXES
      INDEXTYPES

cost_usage_clauses:
   USING [schema.]statistics_type
   USING NULL
   DEFAULT COST (cpu_cost, io_cost, network_cost)
   DEFAULT SELECTIVITY default_selectivity

Key:

  cpu_cost            An Integer: 0 or greater
  io_cost             An Integer: 0 or greater
  network_cost        An Integer: 0 or greater
  default_selectivity A number between 0 and 100
  statistics_type     Must already have been created. 

Examples:
ASSOCIATE STATISTICS WITH PACKAGES spud.spuds_package DEFAULT SELECTIVITY 10; 

ASSOCIATE STATISTICS WITH INDEXES My_Index DEFAULT COST (50,10,0);

ASSOCIATE STATISTICS WITH COLUMNS spud.My_Table.My_col USING spud.My_Stats;

ASSOCIATE STATISTICS WITH COLUMNS spud.My_Table.My_col USING NULL;

"There are lies, damned lies, and statistics" - Disraeli

Related Commands:

DISASSOCIATE STATISTICS
DBMS_STATS
ANALYZE
EXPLAIN PLAN

IMP with rows=y

Related Views:

 DBA_PART_COL_STATISTICS  ALL_PART_COL_STATISTICS USER_PART_COL_STATISTICS 
 DBA_TAB_COL_STATISTICS   ALL_TAB_COL_STATISTICS  USER_TAB_COL_STATISTICS 

 V$BUFFER_POOL_STATISTICS 


© Copyright SS64.com 1999-2013
Some rights reserved