| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: some value index is fast, and for some value it is slow
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
| 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
2 - filter("TCPART"."PRODUCTID" IS NOT NULL) 3 - access("TCPART"."ISNODE"=1)
Note
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
| 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
18 rows selected.
Elapsed: 00:00:00.25
In oracle 9i
SQL> show parameter optimizer
NAME TYPEVALUE
------------------------------------ -------------------------------- ------------------------------ 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 stringCHOOSE In Oracle 10g
SQL> show parameter optimizer
NAME TYPEVALUE
------------------------------------ -------------------------------- ------------------------------ 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 stringALL_ROWS Received on Wed Mar 07 2007 - 23:08:01 CST
 |
 |