On Mar 8, 10:22 am, "Steve Robin" <ocma..._at_gmail.com> wrote:
> 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
> > > ------------ ------------- -------------- ---------- ---------------
>
> ...
>
> read more »- Hide quoted text -
>
> - Show quoted text -
Well steve when I faced this problem, I get stats with all columns.
Why don't you try it.
Received on Thu Mar 08 2007 - 03:51:44 CST
