Re: index scan is costly

>
> SQL> SELECT TCPART_GLOBAL.tril_gid FROM MCC_CATALOG.TCPART_GLOBAL
> WHERE ( TCPART_GLOBAL.ProductId ) = 'G62580000000' ORDER BY
> TCPART_GLOBAL.tril_gid;
>
> TRIL_GID
> ----------------------------------
> BYN0FHR1WGGZJSGJ53ZJATYQ0Y20ZZZZ
>
> Elapsed: 00:00:06.06
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE
> 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TCPART_GLOBAL'
> 2 1 INDEX (FULL SCAN) OF 'KEY_TCPART_GLOBAL' (UNIQUE)
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 0 db block gets
> 11066 consistent gets
> 0 physical reads
> 0 redo size
> 227 bytes sent via SQL*Net to client
> 349 bytes received via SQL*Net from client
> 2 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 1 rows processed
>
> SQL> SELECT /*+ FULL(TCPART_GLOBAL) */ TCPART_GLOBAL.tril_gid FROM
> MCC_CATALOG.TCPART_GLOBAL WHERE ( TCPART_GLOBAL.ProductId ) =
> 'G62580000000' ORDER BY TCPART_GLOBAL.tril_gid;
>
> TRIL_GID
> ----------------------------------
> BYN0FHR1WGGZJSGJ53ZJATYQ0Y20ZZZZ
>
> Elapsed: 00:00:05.96
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=180 Card=395 Bytes=1
> 2245)
>
> 1 0 SORT (ORDER BY) (Cost=180 Card=395 Bytes=12245)
> 2 1 TABLE ACCESS (FULL) OF 'TCPART_GLOBAL' (Cost=149 Card=39
> 5 Bytes=12245)
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 0 db block gets
> 972 consistent gets
> 1 physical reads
> 0 redo size
> 242 bytes sent via SQL*Net to client
> 349 bytes received via SQL*Net from client
> 2 SQL*Net roundtrips to/from client
> 1 sorts (memory)
> 0 sorts (disk)
> 1 rows processed
>
>
> What could be reason Index scaning is costly then table scan.

Looks like the cost just different marginally. with Index the elapsed time was 00:00:06.06, with full scan 00:00:05.96.

but with index reading not suppose to have that many 11066 consistent gets just for 1 row.

May I know the database version?
Is the statistics up to date?

Why does you first explain plan doesn't have cost in it? Was the table analyzed before you ran the first query?

Try to get the index stats
sql> analyze index <index name> validate structure; sql> select * from index_stats; Received on Tue Mar 06 2007 - 04:42:28 CST