On Mar 8, 9:38 am, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> 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
Thanks for reply David.
Actually the problem is time and front end query. As you can see if I
use index hint output comes in less then 1 sec, but when I don't use
it I takes more than 8 sec. And I cann't apply hint on the front end.
Because this product has been released and Front end has been taken by
another company. So I can just make back-end changes.
I apologise for late 10g reply. But you can see in 10g , it is working
like I want it to do in oracle 9i.
Received on Wed Mar 07 2007 - 23:22:05 CST
