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 -> some value index is fast, and for some value it is slow

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

From: Steve Robin <ocmaman_at_gmail.com>
Date: 7 Mar 2007 20:00:52 -0800
Message-ID: <1173326452.175494.295630@h3g2000cwc.googlegroups.com> 





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


Received on Wed Mar 07 2007 - 22:00:52 CST












Original text of this message













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