Why index Scan in this case ?

From: Js <jagjeet_malhi_at_hotmail.com>
Date: 2 Nov 2005 05:02:22 -0800
Message-ID: <1130936542.760810.214480@g47g2000cwa.googlegroups.com>

Using 9.2.0.4

---

SQL> create table t as select * from dba_objects;

Table created.

SQL> update t set object_type = 'TABLE';

5792 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>
SQL> set autot traceonly
SQL>
SQL> select * from t;

5792 rows selected.

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=52 Card=5792 Bytes=492320)

   1 0 TABLE ACCESS (FULL) OF 'T' (Cost=52 Card=5792 Bytes=492320)

Statistics

---

        262  recursive calls
          0  db block gets
        694  consistent gets
          0  physical reads
          0  redo size
     360855  bytes sent via SQL*Net to client
       4745  bytes received via SQL*Net from client
        388  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       5792  rows processed

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

5792 rows selected.

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=51 Card=58 Bytes=4930)

   1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=51 Card=58 Bytes=4930)

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

Statistics

---

          0  recursive calls
          0  db block gets
       1121  consistent gets     <======
          0  physical reads
          0  redo size
     360855  bytes sent via SQL*Net to client
       4745  bytes received via

Received on Wed Nov 02 2005 - 07:02:22 CST