三、进一步分析
1、表的相关信息
| SQL> @Tab_Stat Enter value for input_table_name: ACC_POS_INT_TBL old 11: WHERE table_name = upper('&input_table_name') new 11: WHERE table_name = upper('ACC_POS_INT_TBL') Enter value for input_owner: goex_admin old 12: AND owner = upper('&input_owner') new 12: AND owner = upper('goex_admin') NUM_ROWS BLKS EM_BLKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_ROWS_PER_BLOCK LST_ANLY STA ---------- ---------- ---------- ---------- ---------- ----------- ------------------ --------- --- 33659947 437206 1322 855 0 99 77 27-SEP-11 NO |
2、索引的相关信息
| SQL> @Idx_Stat Enter value for input_table_name: ACC_POS_INT_TBL old 11: WHERE table_name = upper('&input_table_name') new 11: WHERE table_name = upper('ACC_POS_INT_TBL') Enter value for input_owner: goex_admin old 12: AND owner = upper('&input_owner') new 12: AND owner = upper('goex_admin') BLEV IDX_NAME LF_BLKS DST_KEYS NUM_ROWS LF_PER_KEY DAT_BLK_PER_KEY CLUS_FCT LST_ANLY ---- ------------------------------ ---------- ---------- ---------- ---------- --------------- ---------- --------- 3 PK_ACC_POS_INT_TBL 155658 33777720 33777720 1 1 33777447 27-SEP-11 3 ACC_POS_INT_10DIG_IDX 160247 32850596 32850596 1 1 32763921 27-SEP-11 |
3、尝试在BUSINESS_DATE列上创建索引
| SQL> create index I_ACC_POS_INT_TBL_BS_DT on ACC_POS_INT_TBL(BUSINESS_DATE) tablespace tbs_tmp nologging; Index created. SQL> @Idx_Stat Enter value for input_table_name: ACC_POS_INT_TBL old 11: WHERE table_name = upper('&input_table_name') new 11: WHERE table_name = upper('ACC_POS_INT_TBL') Enter value for input_owner: goex_admin old 12: AND owner = upper('&input_owner') new 12: AND owner = upper('goex_admin') BLEV IDX_NAME LF_BLKS DST_KEYS NUM_ROWS LF_PER_KEY DAT_BLK_PER_KEY CLUS_FCT LST_ANLY ---- ------------------------------ ---------- ---------- ---------- ---------- --------------- ---------- --------- 2 I_ACC_POS_INT_TBL_BS_DT 93761 908 33659855 103 506 460007 30-SEP-11 3 PK_ACC_POS_INT_TBL 155658 33777720 33777720 1 1 33777447 27-SEP-11 3 ACC_POS_INT_10DIG_IDX 160247 32850596 32850596 1 1 32763921 27-SEP-11 |
建立索引后聚簇因子较小，差不多接近表上块的数量
4、使用新创建索引后的执行计划
| Execution Plan ---------------------------------------------------------- Plan hash value: 2183566226 ------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1065K| 39M| 17586 (1)| 00:03:32 | | 1 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | | 2 | TABLE ACCESS BY INDEX ROWID| ACC_POS_INT_TBL | 1065K| 39M| 17586 (1)| 00:03:32 | |* 3 | INDEX RANGE SCAN | I_ACC_POS_INT_TBL_BS_DT | 1065K| | 2984 (1)| 00:00:36 | ------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("BUSINESS_DATE">='20110701' AND "BUSINESS_DATE"<='20110728') |
从上面的执行计划看出，SQL语句已经选择了新建的索引。尽管返回的rows,bytes没有明显的变化，但cost已经少了近7倍。
