Re: How do indexes get read?

I guess this just illustrates why full table scans beat indexed access when you retrieve a substantial portion of the table.

With 40 distinct keys and 4 of the values in the where clause, I expect about 10 % of the rows to be retrieved.

You have 80,735 rows, so about 8100 rows are expected.

You have 765 blocks, so you must have 80,735 / 765 rows per block (about 106).

There is NO reason to expect that your selected 10% of the rows are packed into 10 % of the blocks. Therefore, you must expect the indexed retrieval to request each of the 765 blocks about 11 times (8100 gets).

Your actual stat, 9324 gets, is in the ballbark. The physical reads is less due to buffer hits.

The full table scan, on the other hand, visits each block just once. The physical reads is less due to the multiblock read. It looks like your multiblock read parameter might be 16 K? If so, the physical reads could be cut by a factor of 4 if your multiblock read factor was 64 K.

HTH "Thomas A. La Porte" wrote:

> [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 - 21:45:26 CDT