二、分析与改造SQL语句
1、原始的SQL语句分析
SQL语句中where子句的business_date列实现对记录过滤
business_date <= '20110728'条件不会限制索引的使用
SUBSTR(business_date, 1, 6) = SUBSTR('20110728', 1, 6)使用了SUBSTR函数，限制了优化器选择索引
基于business_date列来建立索引函数，从已存在的索引来看，必要性不大
2、改造SQL语句
SUBSTR(business_date, 1, 6) = SUBSTR('20110728', 1, 6)的实质是等于当月，即限制返回的行为从2011.7.1日至2011.7.28
因此其返回的记录大于等于2011.7.1，且小于2011.7.28
做如下改造
business_date >=to_char(last_day(add_months(to_date('20110728','yyyymmdd'),-1)) + 1,'yyyymmdd')
3、改造后的SQL语句
| SELECT acc_num, curr_cd, DECODE('20110728', (SELECT TO_CHAR(LAST_DAY(TO_DATE('20110728', 'YYYYMMDD')), 'YYYYMMDD') FROM DUAL), 0, adj_credit_int_lv1_amt + adj_credit_int_lv2_amt - adj_debit_int_lv1_amt - adj_debit_int_lv2_amt) AS interest FROM acc_pos_int_tbl ACC_POS_INT_TBL1 WHERE business_date >= to_char(last_day(add_months(to_date('20110728', 'yyyymmdd'), -1)) + 1, 'yyyymmdd') AND business_date <= '20110728'; |
4、改造后的执行计划
| Execution Plan ---------------------------------------------------------- Plan hash value: 66267922 -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1065K| 39M| 75043 (1)| 00:15:01 | | 1 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | | 2 | TABLE ACCESS BY INDEX ROWID| ACC_POS_INT_TBL | 1065K| 39M| 75043 (1)| 00:15:01 | |* 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也并没有降低多少
