Re: some value index is fast, and for some value it is slow

On Mar 7, 10:00 pm, "Steve Robin" <ocma..._at_gmail.com> wrote:
> When ISNODE is 0 then full table scan is fast. But when it is 1 index
> is fast.
> Now what can I do here to full index when it is useful, who to
> maintain it according to query.
> It is using CBO in 9.2.0.8 on Windows XP.
>
> SQL> SELECT TC.PRODUCTID, TC.COUNTRYCODE FROM TC WHERE
> TC.PRODUCTID IS NOT NULL AND TC.ISNODE = 0 ORDER BY TC.PRODUCTID;
>
> 679449 rows selected.
>
> Elapsed: 00:00:43.54
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5268 Card=340335 Byt
> es=6126030)
>
> 1 0 SORT (ORDER BY) (Cost=5268 Card=340335 Bytes=6126030)
> 2 1 TABLE ACCESS (FULL) OF 'TC' (Cost=3944 Card=340335 B
> ytes=6126030)
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 6 db block gets
> 41008 consistent gets
> 26442 physical reads
> 0 redo size
> 11424430 bytes sent via SQL*Net to client
> 498763 bytes received via SQL*Net from client
> 45298 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 1 sorts (disk)
> 679449 rows processed
>
> SQL> SELECT /*+ INDEX(TC IND_ISNODE) */ TC.PRODUCTID, TC.COUNTRYCODE
> FROM TC WHERE TC.PRODUCTID IS NOT NULL AND TC.ISNODE = 0 ORDER BY
> TC
>
> 679449 rows selected.
>
> Elapsed: 00:01:02.25
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=22619 Card=340335 By
> tes=6126030)
>
> 1 0 SORT (ORDER BY) (Cost=22619 Card=340335 Bytes=6126030)
> 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'TC' (Cost=21295 Ca
> rd=340335 Bytes=6126030)
>
> 3 2 INDEX (RANGE SCAN) OF 'IND_ISNODE' (NON-UNIQUE) (Cost=
> 619 Card=340358)
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 6 db block gets
> 42212 consistent gets
> 39530 physical reads
> 0 redo size
> 11424430 bytes sent via SQL*Net to client
> 498763 bytes received via SQL*Net from client
> 45298 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 1 sorts (disk)
> 679449 rows processed
>
> SQL> SELECT TC.PRODUCTID, TC.COUNTRYCODE FROM TC WHERE
> TC.PRODUCTID IS NOT NULL AND TC.ISNODE = 1 ORDER BY TC.PRODUCTID;
>
> 1221 rows selected.
>
> Elapsed: 00:00:08.64
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5268 Card=340335 Byt
> es=6126030)
>
> 1 0 SORT (ORDER BY) (Cost=5268 Card=340335 Bytes=6126030)
> 2 1 TABLE ACCESS (FULL) OF 'TC' (Cost=3944 Card=340335 B
> ytes=6126030)
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 0 db block gets
> 41008 consistent gets
> 24347 physical reads
> 0 redo size
> 20483 bytes sent via SQL*Net to client
> 1398 bytes received via SQL*Net from client
> 83 SQL*Net roundtrips to/from client
> 1 sorts (memory)
> 0 sorts (disk)
> 1221 rows processed
>
> SQL> SELECT /*+ INDEX(TC IND_ISNODE) */ TC.PRODUCTID, TC.COUNTRYCODE
> FROM TC WHERE TC.PRODUCTID IS NOT NULL AND TC.ISNODE = 1 ORDER BY
> TCPAR
>
> 1221 rows selected.
>
> Elapsed: 00:00:00.28
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=22619 Card=340335 By
> tes=6126030)
>
> 1 0 SORT (ORDER BY) (Cost=22619 Card=340335 Bytes=6126030)
> 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'TC' (Cost=21295 Ca
> rd=340335 Bytes=6126030)
>
> 3 2 INDEX (RANGE SCAN) OF 'IND_ISNODE' (NON-UNIQUE) (Cost=
> 619 Card=340358)
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 0 db block gets
> 378 consistent gets
> 50 physical reads
> 0 redo size
> 20483 bytes sent via SQL*Net to client
> 1398 bytes received via SQL*Net from client
> 83 SQL*Net roundtrips to/from client
> 1 sorts (memory)
> 0 sorts (disk)
> 1221 rows processed
>
> SQL> select * from dba_tables where owner='MCC' AND TABLE_NAME='TC';
>
> OWNER TABLE_NAME
> TABLESPACE_NAME CLUSTER_NAME
> IOT_NAME PCT_FREE PCT_USED INI_TRANS
> MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS
> PCT_INCREASE FREELISTS FREELIST_GROUPS LOG B
> ------------------------------ ------------------------------
> ------------------------------ ------------------------------
> ------------------------------ ---------- ---------- ----------
> ---------- -------------- ----------- ----------- -----------
> ------------ ---------- --------------- --- -
> NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN
> AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS DEGREE INSTANCES
> CACHE TABLE_LO SAMPLE_SIZE LAST_ANAL PAR IOT_TYPE T S NES BUFFER_
> ROW_MOVE GLO USE DURATION SKIP_COR MON
> CLUSTER_OWNER DEPENDEN
> ---------- ---------- ------------ ---------- ---------- -----------
> ------------------------- ------------------- ---------- ----------
> ----- -------- ----------- --------- --- ------------ - - --- -------
> -------- --- --- --------------- -------- ---
> ------------------------------ --------
> COMPRESS
> --------
> MCC TC
> MCC_DATA
> 10 40 1 255
> 65536 1 2147483645
> 1 1 YES N
> 680716 40994 0 0 0
> 423 0 0 1 1 N
> ENABLED 680716 08-MAR-07 NO N N NO DEFAULT
> DISABLED YES NO DISABLED
> NO DISABLED
> DISABLED
>
> Elapsed: 00:00:00.03
> SQL> select * from dba_INDEXES where owner='MCC' AND
> INDEX_NAME='IND_ISNODE';
>
> 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
> ITYP_NAME
> ---------------------------------------- --- - - - ------- ---
> --------------- ----------------- ------------------------------
> ------------------------------
> PARAMETERS
> ---------------------------------------------------------------------------­---------------------------------------------------------------------------­---------------------------------------------------------------------------­---------------------------------------------------------------------------
> GLO DOMIDX_STATU DOMIDX FUNCIDX_ JOI
> --- ------------ ------ -------- ---
> MCC IND_ISNODE
> NORMAL MCC
> TC TABLE NONUNIQUE
> DISABLED MCC_INDEX 2
> 255 27770880 1 2147483645
> 1
> 1 10 YES 2 1234 2
> 617 20675 41351 VALID 680716
> 680716 08-MAR-07 1
> 1 NO N N N DEFAULT NO
>
> 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
> ITYP_NAME
> ---------------------------------------- --- - - - ------- ---
> --------------- ----------------- ------------------------------
> ------------------------------
> PARAMETERS
> ---------------------------------------------------------------------------­---------------------------------------------------------------------------­---------------------------------------------------------------------------­---------------------------------------------------------------------------
> GLO DOMIDX_STATU DOMIDX FUNCIDX_ JOI
> --- ------------ ------ -------- ---
>
> YES NO
An index scan will usually result in two I/O calls (one to read the index and one to access the table) so the 20 seconds difference in your hinted query and unhinted query may be just that --- time for the additional I/O, especially with over 675000 records to process. Why you'd want to use an index when returning 99+% of your data is a mystery to me as a table scan would be much faster (as you've already reported). I would want to be using the index for the 1221 records where ISNODE = 1 as that total comprises less than 0.2% of your data (and Oracle *is* using that index as evidenced by your explain plan output). Your unhinted queries appear to be working correctly which indicates, to me, anyway, that you have generated the proper statistics and histograms.

Again, I'm lost as to why you think you need to use the index to return essentially your entire table.

David Fitzjarrell Received on Wed Mar 07 2007 - 22:38:12 CST