Re: Why index Scan in this case ?

Js wrote:
> Thanks fitzjarr... for this great explaination.
>
> Without computing the histograms, still this information is available
> to CBO
> that there is x number of rows and there is only 1 distinct key.
>
> SQL> create table t as select * from dba_objects;
>
> Table created.
>
> SQL> update t set object_type = 'TABLE';
>
> 5794 rows updated.
>
> SQL> create index ind_t on T ( object_type);
>
> Index created.
>
> -- not computing the histograms ...
>
> SQL> analyze table t compute statistics for table for all indexes;
>
> Table analyzed.
>
> SQL>
> SQL> select table_name,num_rows from user_tables where table_name = 'T'
> ;
>
> TABLE_NAME NUM_ROWS
> ------------------------------ ----------
> T 5794
>
> SQL> select Index_name,num_rows,distinct_keys from user_indexes where
> index_name = 'IND_T';
>
> INDEX_NAME NUM_ROWS DISTINCT_KEYS
> ------------------------------ ---------- -------------
> IND_T 5794 1
>
> SQL>
Yet that information in USER_INDEXES doesn't cause the CBO to create 'correct' execution plans:

SQL> create table t as select * from sys.dba_objects;

Table created.

SQL> update t set objecT_type = 'TABLE';

7834 rows updated.

SQL> create index ind_t on t(objecT_type);

Index created.

SQL> analyze table t compute statistics for table for all indexes;

Table analyzed.

SQL> select *
  2 from user_histograms
  3 where table_name = 'T'
  4 and column_name = 'OBJECT_TYPE';

no rows selected

SQL> SQL> set autotrace traceonly
SQL> select * from t where object_type = 'TABLE';

7834 rows selected.

Execution Plan

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=22 Card=78 Bytes=678
          6)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=22 Card=78 Byte
          s=6786)

   2    1     INDEX (RANGE SCAN) OF 'IND_T' (NON-UNIQUE) (Cost=40 Card
          =31)

Statistics

        267  recursive calls
          0  db block gets
       1299  consistent gets
          0  physical reads
          0  redo size
     508795  bytes sent via SQL*Net to client
       6393  bytes received via SQL*Net from client
        524  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
       7834  rows processed

SQL> Creating histograms corrects the issue:

SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T', method_opt => 'for all indexed columns size auto', cascade=>true);

PL/SQL procedure successfully completed.

SQL> select * from t where object_type = 'TABLE';

7834 rows selected.

Execution Plan

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=7834 Bytes=6
          50222)

   1    0   TABLE ACCESS (FULL) OF 'T' (Cost=26 Card=7834 Bytes=650222
          )

Statistics

          0  recursive calls
          0  db block gets
        710  consistent gets
          0  physical reads
          0  redo size
     508795  bytes sent via SQL*Net to client
       6393  bytes received via SQL*Net from client
        524  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       7834  rows processed

SQL> select *
  2 from user_histograms
  3 where table_name = 'T'
  4* and column_name = 'OBJECT_TYPE';

TABL COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A

---- -------------------- --------------- -------------- ----------
T    OBJECT_TYPE                     7834     4.3748E+35

SQL> Simply having this data available in USER_INDEXES is not sufficient for the CBO to 'know' an index scan is not warranted. Note that until I had computed histograms the query plan involved an index scan of IND_T; only after the histogram was in place did the CBO decide a full table scan was the best course of action. Jonathan Lewis can explain this better than I have, however I know, and have proven, that histograms are necessary in some cases to 'nudge' the CBO toward the 'correct' plan of attack.

Also, I would seriously recommend you cease using analyze table and start using dbms_stats, as analyze table is present only for backward compatability and dbms_stats produces better statistics for the CBO.

David Fitzjarrell Received on Fri Nov 04 2005 - 10:53:35 CST