Re: index not used after compute stats ?

The 4 "db block gets" are the four current mode accesses to the segment header block that take place every time you do a full table scan or an index fast full scan in version 8 of Oracle. (This is down to 2 on v9).

The 11 "consistent gets" are the data acquisition block accesses. A "get" counts a single logical block hit - not a read request - so your table can be at most 11 blocks long (or the statistics are being reported incorrectly).

I do not know why the stats are showing a sort - this is an oddity I have noted in other cases, and I suspect it may be due to a recursive action on dictionary information.

If you want a full breakdown of how Oracle is arriving at the path, then for the run with statistics:

alter system flush shared pool;
alter session set events '10053 trace name context forever, level 2';

and run the query again

The trace file will dump the full explanation of costs.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Now running 3-day intensive seminars
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases


Ted Chyn wrote in message <44a19320.0202082120.f87bf8_at_posting.google.com>...


>jonathan,

>

>1. this table has 65 blocks 65/16=4 db block gets(shown).

>2. compute also show memory sort, 11 consistent gets why it is less

>   expensive than index lookup.

>

>thnx again

>ted

>




>> >

>> >Statistics

>> >----------------------------------------------------------

>> >          0  recursive calls

>> >          4  db block gets

>> >         11  consistent gets

>> >          0  physical reads

>> >          0  redo size

>> >        588  bytes sent via SQL*Net to client

>> >        306  bytes received via SQL*Net from client

>> >          5  SQL*Net roundtrips to/from client

>> >          1  sorts (memory)

>> >          0  sorts (disk)

>> >          1  rows processed