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

Card is the number of rows returned by the step The optimizer thinks that it is going to return 26,064 rows from this query, which is probably why it wants to do a table-scan.

You might try creating a histogram on the state column if you want to give the optimizer better information.

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Vincent GRENET wrote in message
>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 Fri Aug 20 1999 - 03:31:16 CDT