Re: Why index Scan in this case ?

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> Received on Fri Nov 04 2005 - 07:39:47 CST