|* 3 | INDEX SKIP SCAN | PK_ACC_POS_INT_TBL | 33730 | | 41180 (1)| 00:08:15 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("BUSINESS_DATE">='20110701' AND "BUSINESS_DATE"<='20110728')
filter("BUSINESS_DATE">='20110701' AND "BUSINESS_DATE"<='20110728')
改造后可以看到SQL语句的执行计划已经由原来的全表扫描改为执行INDEX SKIP SCAN，但其cost也并没有降低多少
三、进一步分析
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
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
---- ---------------