Re: Finding with ROWID as long as full table scan ?

Hi Vincent,

A few remarks:
- Please note when you issue these two statements immediately after each other your results can be misleading because the data is still in the buffer cache.
- As far as I know, the number of reads in a full table scan is accurate, though they will be processed by
  <number of blocks read>/<db_file_multiblock_read_count> read requests. However, using an index the number of read requests will equal the number of blocks read.
- In the past you could 'convince' the optimizer to use indexes by lowering db_file_multiblock_read_count.
- The cardinality is the actual number of rows returned by the step executed - For exact results, you should make sure timed_statistics=true in init.ora, trace run your session and run TKPROF on the trace file.

Hth,

Sybrand Bakker, Oracle DBA

Vincent GRENET <vincent.grenet_at_free.fr> wrote in message news:%P_u3.7$yX2.244761_at_nnrp2.proxad.net...
> Hi all.
>
> Optimizer question.
>
> Here are the execution plans and statistics for the same request (a simple
> select of one row among 80K), generated with SQL*PLUS and AUTOTRACE.
> The first plan uses an index (forced with a hint). The second one does not
> use the index.
>
> Query:
>
> set autotrace on explain
> select /*+ index(sm_queue i_18) */
> sm_queue.id
> from
> sm_queue
> where
> sm_queue.state='W';
> quit
>
>
> It looks like finding the row (by rowid ?) in the table after looking in
the
> index costs more than the full table scan, though there are far less reads
> when using the index.
>
> This is confirmed by running the real world application, from which this
> example is taken.
>
> Do I read this correctly ? If so, why ?
> If this is correct, how can I find how the cost is actually spent ?
>
> Finding the row after finding its rowid using the index should be (almost)
> immediate. Only one block must be read to find the row using the rowid.
> (I understand that looking in the index might cost more than a
> full table scan if the number of rows to return is high compared to the
size
> of the table, which is not the case).
>
> Though I understand how the query is processed, I don't understand the
> figures.
> What is the 'card' figure ?
> What is the 'bytes' figure ? (doc says it's the bytes processed by the
> query).
> Why is the 'cost' figure not closely related to the 'bytes' figure ?
>
> Using index:
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2152 Card=26064 Byte
> s=417024)
>
> 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'SM_QUEUE' (Cost=2152 Car
> d=26064 Bytes=417024)
>
> 2 1 INDEX (RANGE SCAN) OF 'I_18' (NON-UNIQUE) (Cost=209 Card
> =26064)
>
> Statistics
> ----------------------------------------------------------
> 8 recursive calls
> 0 db block gets
> 24 consistent gets
> 0 physical reads
> 0 redo size
> 556 bytes sent via SQL*Net to client
> 716 bytes received via SQL*Net from client
> 4 SQL*Net roundtrips to/from client
> 1 sorts (memory)
> 0 sorts (disk)
> 1 rows processed
>
> Not using index:
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=752 Card=26064 Bytes
> =417024)
>
> 1 0 TABLE ACCESS (FULL) OF 'SM_QUEUE' (Cost=752 Card=26064 Byt
> es=417024)
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 3 db block gets
> 6915 consistent gets
> 6886 physical reads
> 0 redo size
> 556 bytes sent via SQL*Net to client
> 717 bytes received via SQL*Net from client
> 4 SQL*Net roundtrips to/from client
> 1 sorts (memory)
> 0 sorts (disk)
> 1 rows processed
>
> Thank you for any advice
>
> Vincent.
>
>
>
>
>
>
>
Received on Thu Aug 19 1999 - 23:37:30 CDT