Change CBO statistics selectivity for given index and column
                                                 Last update (2008-11-27 11:01:19)
                                                                                                                   Date added (2008-02-15 15:49:32)

Summary
When an indexed column has a low Number of Distinct Values (NDV), the selectivity (1/NDV) for that column becomes poor. If the indexed column with poor selectivity is the leading column of an index, the whole selectivity of the index becomes poor. When the CBO has the option of selecting an index range scan on an index with poor selectivity, it may opt to perform a full table scan (FTS) instead, as it can result on a lower computed cost. When the index with poor selectivity has a non-uniform data distribution, the cost of the FTS may be lower than the cost for the index range scan, still the performance using the index for values with small number of rows may be better.

In situations like this, improving the selectivity of the index/column enables the CBO to choose an index range scan over a FTS. Over time, when the data increases the NDV for the column, improving automatically the selectivity for the indexed column, there is no further need to modify the statistics for index/column, as the standard process to gather stats will update the statistics for index/column with more selective values. In the meantime, this script uses DBMS_STATS to change the following columns on the data dictionary.

Execute from SQL*Plus connected as system or apps:
 
    # sqlplus apps/apps@vis11i 
     SQL> START bde_chg_stats.sql; 

Parameters
 ------------------------- 
 1. p_owner Database User that owns the Table and the Index for which the statistics will be changed. 
 2. p_table_name Table (name). 
 3. p_index_name Index (name) that will have its statistics changed. 
 4. p_column_name Column (name) that will have its statistics changed. 

bde_chg_stats.sql
=============================================================================*/  
define p_owner       
define p_table_name  
define p_index_name  
define p_column_name 
 
variable v_dk   NUMBER; 
variable v_lblk NUMBER; 
variable v_dblk NUMBER; 
variable v_ndv  NUMBER; 
variable v_dens NUMBER; 
 
SET term ON ver OFF pages 100 lines 110 serveroutput ON; 
spool BDE_CHG_STATS_&&p_column_name; 
 
SELECT TO_CHAR(SYSDATE,'DD-MON-YY HH24:MI') TODAY, 
       '&&p_owner'       OWNER, 
       '&&p_table_name'  TABLE_NAME, 
       '&&p_index_name'  INDEX_NAME, 
       '&&p_column_name' COLUMN_NAME 
  FROM DUAL; 
 
SELECT table_name, 
       num_rows 
  FROM all_tables 
 WHERE owner='&&p_owner' 
   AND table_name='&&p_table_name'; 
 
SELECT index_name, 
       num_rows, 
       distinct_keys,           -- Former Value 
       avg_leaf_blocks_per_key, -- Former Value 
       avg_data_blocks_per_key  -- Former Value 
  FROM all_indexes 
 WHERE owner='&&p_owner' 
   AND index_name='&&p_index_name'; 
 
SELECT column_name, 
       num_nulls, 
       num_distinct,            -- Former Value 
       density                  -- Former Value 
  FROM all_tab_columns 
 WHERE owner='&&p_owner' 
   AND table_name='&&p_table_name' 
   AND column_name='&&p_column_name'; 
 
BEGIN 
--  Calculating new selectivity values for Index and Column 
    SELECT TRUNC(num_rows),1,1 
      INTO :v_dk,:v_lblk,:v_dblk 
      FROM all_indexes 
     WHERE owner='&&p_owner' 
       AND index_name='&&p_index_name'; 
 
    SELECT NVL(TRUNC(atb.num_rows-atc.num_nulls),0), 
           DECODE(NVL(TRUNC(atb.num_rows-atc.num_nulls),0),0,0, 
           ROUND(1/NVL(TRUNC(atb.num_rows-atc.num_nulls),1),8)) 
      INTO :v_ndv,:v_dens 
      FROM all_tab_columns atc, 
           all_tables atb 
     WHERE atb.owner='&&p_owner' 
       AND atb.table_name='&&p_table_name' 
       AND atc.owner=atb.owner 
       AND atc.table_name=atb.table_name 
       AND atc.column_name='&&p_column_name'; 
 
--  Printing new values 
    DBMS_OUTPUT.PUT_LINE('*           DISTINCT_KEYS: '||TO_CHAR(:v_dk)); 
    DBMS_OUTPUT.PUT_LINE('* AVG_LEAF_BLOCKS_PER_KEY: '||TO_CHAR(:v_lblk)); 
    DBMS_OUTPUT.PUT_LINE('* AVG_DATA_BLOCKS_PER_KEY: '||TO_CHAR(:v_dblk)); 
    DBMS_OUTPUT.PUT_LINE('*            NUM_DISTINCT: '||TO_CHAR(:v_ndv)); 
    DBMS_OUTPUT.PUT_LINE('*                 DENSITY: '||TO_CHAR(:v_dens)); 
 
--  Changing data dictionary stats for Index and Column (Selectivity) 
    DBMS_STATS.SET_INDEX_STATS (ownname=>'&&p_owner', 
                                indname=>'&&p_index_name', 
                                numdist=>:v_dk, 
                                avglblk=>:v_lblk, 
                                avgdblk=>:v_dblk); 
    DBMS_STATS.SET_COLUMN_STATS(ownname=>'&&p_owner', 
                                tabname=>'&&p_table_name', 
                                colname=>'&&p_column_name', 
                                distcnt=>:v_ndv, 
                                density=>:v_dens); 
END; 
/ 
 
spool OFF; 
undefine p_owner; 
undefine p_table_name; 
undefine p_index_name; 
undefine p_column_name; 
Tip: Put as a leading column in a concatenated index the column with the best selectivity

Other links
Concatenated index columns order
Reviews
Categories
Filters
Search