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

On Mar 7, 11:08 pm, "Steve Robin" <ocma..._at_gmail.com> wrote:
> On Mar 8, 9:00 am, "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
>
> I like to share one more think. In oracle 10g it is using index when
> it is require. Why ?
> Please give me any solution IN ORACLE 9i.
>
> SQL> EXPLAIN PLAN FOR SELECT TCPART.PRODUCTID, TCPART.COUNTRYCODE
> FROM SCOTT.TCPART WHERE TCPART.PRODUCTID IS NOT NULL AND
> TCPART.ISNODE = 1 ORDER BY TCPART.PRODUCTID;
>
> Explained.
>
> Elapsed: 00:00:00.01
> SQL> @?\RDBMS\ADMIN\UTLXPLP.SQL
>
> PLAN_TABLE_OUTPUT
> ---------------------------------------------------------------------------­---------------------------------------------------------------------------­--------------------------------------------------
> Plan hash value: 3598284462
>
> ---------------------------------------------------------------------------­--------------------------------------------
> | Id | Operation | Name | Rows | Bytes |
> Cost (%CPU)| Time |
> ---------------------------------------------------------------------------­---------------------------------------------
> | 0 | SELECT STATEMENT | | 338 | 9464 |
> 33 (4)| 00:00:01 |
> | 1 | SORT ORDER BY | | 338 | 9464 |
> 33 (4)| 00:00:01 |
> |* 2 | TABLE ACCESS BY INDEX ROWID| TCPART | 338 | 9464 |
> 32 (0)| 00:00:01 |
> |* 3 | INDEX RANGE SCAN | IND_ISNODE | 456 |
> | 3 (0)| 00:00:01 |
> ---------------------------------------------------------------------------­---------------------------------------------
>
> PLAN_TABLE_OUTPUT
> ---------------------------------------------------------------------------­---------------------------------------------------------------------------­--------------------------------------------------
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 2 - filter("TCPART"."PRODUCTID" IS NOT NULL)
> 3 - access("TCPART"."ISNODE"=1)
>
> Note
> -----
> - dynamic sampling used for this statement
>
> 20 rows selected.
>
> Elapsed: 00:00:00.12
> SQL> EXPLAIN PLAN FOR SELECT TCPART.PRODUCTID, TCPART.COUNTRYCODE
> FROM SCOTT.TCPART WHERE TCPART.PRODUCTID IS NOT NULL AND
> TCPART.ISNODE =0 ORDER BY TCPART.PRODUCTID;
>
> Explained.
>
> Elapsed: 00:00:00.03
> SQL> @?\RDBMS\ADMIN\UTLXPLP.SQL
>
> PLAN_TABLE_OUTPUT
> ---------------------------------------------------------------------------­---------------------------------------------------------------------------­--------------------------------------------------
> Plan hash value: 3792352330
>
> ---------------------------------------------------------------------------­----------
> | Id | Operation | Name | Rows | Bytes |TempSpc| Cost
> (%CPU)| Time |
> ---------------------------------------------------------------------------­----------
> | 0 | SELECT STATEMENT | | 676K| 18M| | 16358
> (2)| 00:03:17 |
> | 1 | SORT ORDER BY | | 676K| 18M| 51M| 16358
> (2)| 00:03:17 |
> |* 2 | TABLE ACCESS FULL| TCPART | 676K| 18M| | 9211
> (2)| 00:01:51 |
> ---------------------------------------------------------------------------­----------
>
> Predicate Information (identified by operation id):
>
> PLAN_TABLE_OUTPUT
> ---------------------------------------------------------------------------­---------------------------------------------------------------------------­--------------------------------------------------
> ---------------------------------------------------
>
> 2 - filter("TCPART"."PRODUCTID" IS NOT NULL AND
> "TCPART"."ISNODE"=0)
>
> Note
> -----
> - dynamic sampling used for this statement
>
> 18 rows selected.
>
> Elapsed: 00:00:00.25
>
> In oracle 9i
> SQL> show parameter optimizer
>
> NAME TYPE
> VALUE
> ------------------------------------ --------------------------------
> ------------------------------
> optimizer_dynamic_sampling integer
> 1
> optimizer_features_enable string
> 9.2.0
> optimizer_index_caching integer
> 0
> optimizer_index_cost_adj integer
> 100
> optimizer_max_permutations integer
> 2000
> optimizer_mode string
> CHOOSE
>
> In Oracle 10g
>
> SQL> show parameter optimizer
>
> NAME TYPE
> VALUE
> ------------------------------------ --------------------------------
> ------------------------------
> optimizer_dynamic_sampling integer
> 2
> optimizer_features_enable string
> 10.1.0
> optimizer_index_caching integer
> 0
> optimizer_index_cost_adj integer
> 100
> optimizer_mode string
> ALL_ROWS
I believe in 10g the optimizer has improved and you may have historgrams computed. Computing histograms for this table would make your 9i performance equivalent to the 10g performance you see. Using dbms_stats.gather_table_stats() you can provide a method_opt to compute histograms by setting that parameter to the following value:

 'for all indexed columns size auto'

Oracle should then compute how many 'buckets' you need and create them. Once histograms are in place you should see an improvement.

Jonathan Lewis has written much on this subject; I would buy a copy of his 'Cost Based Oracle' text (volume one is out now, and volume two is in the 'works'). Or go out to his website at www.jlcomp.demon.co.uk and search there. You will find a wealth of information to help you.

David Fitzjarrell Received on Thu Mar 08 2007 - 09:32:29 CST