??? ??在索引列上使用函數(shù)使得索引失效的是常見(jiàn)的索引失效原因之一，因此盡可能的避免在索引列上使用函數(shù)。盡管可以使用基于函數(shù)的索引來(lái)
解決索引失效的問(wèn)題，但如此一來(lái)帶來(lái)的比如磁盤(pán)空間的占用以及列上過(guò)多的索引導(dǎo)致DML性能的下降。本文描述的是一個(gè)索引列上使用函數(shù)使
其失效的案例。

一、數(shù)據(jù)版本與原始語(yǔ)句及相關(guān)信息
??1.版本信息????

SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Release 10.2.0.3.0 - 64bit Production PL/SQL Release 10.2.0.3.0 - Production CORE 10.2.0.3.0 Production TNS for Linux: Version 10.2.0.3.0 - Production NLSRTL Version 10.2.0.3.0 - Production

??2.原始語(yǔ)句與其執(zhí)行計(jì)劃???

SQL> set autotrace traceonly exp; 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 SUBSTR(business_date, 1, 6) = SUBSTR('20110728', 1, 6) AND business_date <= '20110728'; Execution Plan ---------------------------------------------------------- Plan hash value: 3114115399 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 336K| 12M| 96399 (1)| 00:19:17 | | 1 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | |* 2 | TABLE ACCESS FULL| ACC_POS_INT_TBL | 336K| 12M| 96399 (1)| 00:19:17 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(SUBSTR("BUSINESS_DATE",1,6)='201107' AND "BUSINESS_DATE"<='20110728')

????從執(zhí)行計(jì)劃可以看出，SQL語(yǔ)句使用了全表掃描，而where 子句中只有唯一的一列business_date

??3.表上的索引信息?????

SQL> set autotrace off; SQL> set linesize 190 SQL> @Idx_Info Enter value for owner: goex_admin old 10: AND owner = upper('&owner') new 10: AND owner = upper('goex_admin') Enter value for table_name: ACC_POS_INT_TBL old 11: AND a.table_name = upper('&table_name') new 11: AND a.table_name = upper('ACC_POS_INT_TBL') TABLE_NAME INDEX_NAME COL_NAM CL_POS STATUS IDX_TYP DSCD ------------------ ------------------------ -------------------- ------ -------- --------------- ---- ACC_POS_INT_TBL ACC_POS_INT_10DIG_IDX SYS_NC00032$ 1 VALID FUNCTION-BASED ASC NORMAL ACC_POS_INT_TBL ACC_POS_INT_10DIG_IDX BUSINESS_DATE 2 VALID FUNCTION-BASED ASC NORMAL ACC_POS_INT_TBL ACC_POS_INT_10DIG_IDX CURR_CD 3 VALID FUNCTION-BASED ASC NORMAL ACC_POS_INT_TBL PK_ACC_POS_INT_TBL ACC_NUM 1 VALID NORMAL ASC ACC_POS_INT_TBL PK_ACC_POS_INT_TBL BUSINESS_DATE 2 VALID NORMAL ASC

????從索引的情況上來(lái)看有一個(gè)基于主鍵的索引包含了BUSINESS_DATE列，而查詢(xún)語(yǔ)句并沒(méi)有走索引而是選擇的全表掃描，而且預(yù)估所返回
????的行Rows與bytes也是大的驚人，cost的值96399，接近10W。

二、分析與改造SQL語(yǔ)句
??1.原始的SQL語(yǔ)句分析
?????? SQL語(yǔ)句中where子句的business_date列實(shí)現(xiàn)對(duì)記錄過(guò)濾
?????? business_date <= '20110728'條件不會(huì)限制索引的使用
?????? SUBSTR(business_date, 1, 6) = SUBSTR('20110728', 1, 6)使用了SUBSTR函數(shù)，限制了優(yōu)化器選擇索引
?????? 基于business_date列來(lái)建立索引函數(shù)，從已存在的索引來(lái)看，必要性不大
???
??2.改造SQL語(yǔ)句
????SUBSTR(business_date, 1, 6) = SUBSTR('20110728', 1, 6)的實(shí)質(zhì)是等于當(dāng)月，即限制返回的行為從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語(yǔ)句???

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.改造后的執(zhí)行計(jì)劃??

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語(yǔ)句的執(zhí)行計(jì)劃已經(jīng)由原來(lái)的全表掃描改為執(zhí)行INDEX SKIP SCAN，但其cost也并沒(méi)有降低多少

三、進(jìn)一步分析
??1.表的相關(guān)信息??

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.索引的相關(guān)信息?

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列上創(chuàng)建索引???

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

??建立索引后聚簇因子較小，差不多接近表上塊的數(shù)量
??
??4.使用新創(chuàng)建索引后的執(zhí)行計(jì)劃???

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')

??從上面的執(zhí)行計(jì)劃看出，SQL語(yǔ)句已經(jīng)選擇了新建的索引
??盡管返回的rows,bytes沒(méi)有明顯的變化，但cost已經(jīng)少了近7倍。

?

函數(shù)使得索引列失效