Finding with ROWID as long as full table scan ?

From: Vincent GRENET <vincent.grenet_at_free.fr>
Date: Thu, 19 Aug 1999 21:39:07 GMT
Message-ID: <%P_u3.7$yX2.244761@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 - 16:39:07 CDT