Oracle函数使索引列失效的解决办法(索引语句)

在索引列上使用函数使得索引失效的是常见的索引失效原因之一，因此尽可能的避免在索引列上使用函数。尽管可以使用基于函数的索引来解决索引失效的问题，但如此一来带来的比如磁盘空间的占用以及列上过多的索引导致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。

二、分析与改造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也并没有降低多少

三、进一步分析

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倍。

以上所述是小编给大家介绍的Oracle函数使索引列失效的解决办法，希望对大家有所帮助。在此也非常感谢大家对本站网站的支持！

美国快速服务器

mysql学习笔记之表的基本操作

centos编译安装mysql 5.6及安装多个mysql实例详解

mysql 5.7.11 winx64.zip安装配置方法图文教程

mysql 5.7.17 winx64.zip安装配置方法图文教程

CentOS安装mysql5.7 及简单配置教程详解

MySQL 5.7 zip版本(zip版)安装配置步骤详解

MySQL5.6.31 winx64.zip 安装配置教程详解

MySQL注入绕开过滤的技巧总结

一次Mysql死锁排查过程的全纪录