How do indexes get read?

[Environment: Oracle RDBMS EE 8.0.5.1.0 running on Irix 6.2]

I'm having trouble understanding why the statistics for a given query are so different when performing a full table scan versus an index lookup--in the favor of the FTS.

I've run the following query with and without an index on the 'fldid' column. I've tried both B*Tree and bitmap, given that there are about 40 distinct values and the 'changes' table has approximately 100,000 rows.

What I can't understand is why, when I do the index scan, followed by the table access, do I do 10X the number of consistent gets than when I just do a full table scan?

  select * from changes
   where fldid in (4, 5, 7, 10)
     and time > 931536958
order by caseid, time asc

The physical characterstics of the table are thus:

TABLE_NAME Ini Ext Next NUM_ROWS BLOCKS AVG_SPACE AVG_ROW_LEN ----------- ---------- ------ ---------- ------- --------- ----------- CHANGES 6307840 270336 80735 765 893 66 The query produces the following relevant stats (doing a FTS)

Statistics

          0  recursive calls
          3  db block gets
        765  consistent gets
        320  physical reads

When I build an index on fldid, it looks like this:

INDEX_NAME Ini Ext Next BLOCKS BYTES

--------------------- ---------- ---------- ---------- ----------
CHANGES_FLDID             262144     131072         35     286720

And the execution plan and statistics for the query look like this:

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=70 Card=1 Bytes=66)    1 0 SORT (ORDER BY) (Cost=70 Card=1 Bytes=66)

   2    1     INLIST ITERATOR (CONCATENATED)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'Changes' 
                    (Cost=67 Card=1 Bytes=66)
   4    3         INDEX (RANGE SCAN) OF 'CHANGES_FLDID' (NON-UNIQUE) 
                      (Cost=3 Card=1)

Statistics

          0  recursive calls
          0  db block gets
       9324  consistent gets
       2582  physical reads

Any thoughts are appreciated.

Thomas A. La Porte
DreamWorks Feature Animation
tlaporte_at_anim.dreamworks.com Received on Fri Jul 09 1999 - 17:53:27 CDT