在索引列上使用函数使得索引失效的是常见的索引失效原因之一，因此尽可能的避免在索引列上使用函数。尽管可以使用基于函数的索引来解决索引失效的问题，但如此一来带来的比如磁盘空间的占用以及列上过多的索引导致DML性能的下降。本文描述的是一个索引列上使用函数使其失效的案例。
一、数据版本与原始语句及相关信息
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、原始语句与其执行计划
| 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') |
从执行计划可以看出，SQL语句使用了全表扫描，而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 |
从索引的情况上来看有一个基于主键的索引包含了BUSINESS_DATE列，而查询语句并没有走索引而是选择的全表扫描，而且预估所返回的行Rows与bytes也是大的惊人，cost的值96399，接近10W。
