Re: index scan is costly

On Mar 6, 3:42 pm, blundermas..._at_gmail.com wrote:
> > 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;- Hide quoted text -
>
> - Show quoted text -

SQL> ANALYZE INDEX KEY_TCPART_GLOBAL VALIDATE STRUCTURE; Index analyzed.

SQL> SELECT * FROM INDEX_STATS;

    HEIGHT     BLOCKS NAME
PARTITION_NAME                    LF_ROWS    LF_BLKS LF_ROWS_LEN

---------- ---------- ------------------------------
------------------------------ ---------- ---------- -----------
---------- ---------- ---------- ----------- ---------- -----------
--------------- ------------- ----------------- ----------- ----------
---------- ------------ -------------------- ---------- ------------
-------------- ----------------
         2        640
KEY_TCPART_GLOBAL
17705        107      761315       8000        106          1
3266       8032           0               0
17705                 1      864032     764581         89
1                    3          0            0
0                0

I gather stats again but still useless. Received on Tue Mar 06 2007 - 22:33:04 CST