Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: index scan is costly

Re: index scan is costly

From: joel garry <joel-garry_at_home.com>
Date: 6 Mar 2007 16:15:41 -0800
Message-ID: <1173226541.775064.113570@8g2000cwh.googlegroups.com>


On Mar 5, 6:56 pm, "Steve Robin" <ocma..._at_gmail.com> wrote:
> In this case index scan is costly.
> Even there is no repeat value in index column.
>
> 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
>
> SQL> select count(1) from MCC_CATALOG.TCPART_GLOBAL;
>
> COUNT(1)
> ----------
> 25147
> SQL> select * from dba_indexes where index_name='KEY_TCPART_GLOBAL';
>
> OWNER INDEX_NAME
> INDEX_TYPE TABLE_OWNER
> TABLE_NAME TABLE_TYPE UNIQUENES COMPRESS
> PREFIX_LENGTH TABLESPACE_NAME INI_TRANS MAX_TRANS
> INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE
> PCT_THRESHOLD INCLUDE_COLUMN FREELISTS FREELIST_GROUPS PCT_FREE
> LOG BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY
> AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR STATUS NUM_ROWS
> SAMPLE_SIZE LAST_ANAL DEGREE
> INSTANCES PAR T G S BUFFER_ USE DURATION
> PCT_DIRECT_ACCESS ITYP_OWNER
> ------------------------------ ------------------------------
> --------------------------- ------------------------------
> ------------------------------ ----------- --------- --------
> ------------- ------------------------------ ---------- ----------
> -------------- ----------- ----------- ----------- ------------
> ------------- -------------- ---------- --------------- ---------- ---
> ---------- ----------- ------------- -----------------------
> ----------------------- ----------------- -------- ----------
> ----------- --------- ----------------------------------------
> ---------------------------------------- --- - - - ------- ---
> --------------- ----------------- ---
> PARAMETERS
> ---------------------------------------------------------------------------­---------------------------------------------------------------------------­---------------------------------------------------------------------------­---------------------------------------------------------------------------­---------------------------------------------------------------------------­---------------------------------------------------------------------------­---------------------------------------------------------------------------­---------------------------------------------------------------------------­-----------------------------------------------------------------------
> GLO DOMIDX_STATU DOMIDX FUNCIDX_ JOI
> --- ------------ ------ -------- ---
> MCC_CATALOG KEY_TCPART_GLOBAL
> NORMAL MCC_CATALOG
> TCPART_GLOBAL TABLE UNIQUE
> DISABLED MCC_CATALOG_INDEX
> 2 255 4194304 1048576 1 2147483645
> 0 1 1 10
> YES
> VALID
> 1
> 1 NO N N N DEFAULT NO
>
> NO NO
>
> SQL> select count(distinct ProductId) FROM MCC_CATALOG.TCPART_GLOBAL;
>
> COUNT(DISTINCTPRODUCTID)
> ------------------------
> 25147
>
> What could be reason Index scaning is costly then table scan.

Consistent gets for a table scan increments 1 per block read. Consistent gets for index only access increments 1 per block read. Consistent gets for table access through an index increments by the index height plus 2 times the number of keys in the range. So I'd say you are having to read a lot of blocks by using the index, but not a lot if you are just scanning through the table. If I'm not too crosseyed,  I'd guess using the index makes Oracle go get an index block or 16, then check each row in the block for consistency, then go get the data block, and so on. Then it has to sort 'em out. Using the table scan just whips through the table, then sorts 'em out. Now why the optimizer couldn't figure that out... we need to see your plan costing doing it both ways.

Two things you might look at: see if these results repeat consistently (to see if making the index blocks hot by repetition, or perhaps cleaning out dirty blocks, for example); and check v$bh to see if there's anything interesting there.

You probably want to use dbms_stats rather than analyze, depending on your version.

Hey, check this out: http://skdba.blogspot.com/2006/08/10053-to-rescue-again.html

jg

--
@home.com is bogus.
http://www.signonsandiego.com/uniontrib/20070304/news_1n4legends.html
Received on Tue Mar 06 2007 - 18:15:41 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US