• 售前

  • 售后

热门帖子
入门百科

Oracle函数使索引列失效的办理办法

[复制链接]
掘金入眠刈 显示全部楼层 发表于 2022-1-7 22:25:07 |阅读模式 打印 上一主题 下一主题
在索引列上利用函数使得索引失效的是常见的索引失效原因之一,因此尽大概的克制在索引列上利用函数。尽管可以利用基于函数的索引来办理索引失效的问题,但如此一来带来的好比磁盘空间的占用以及列上过多的索引导致DML性能的下降。本文描述的是一个索引列上利用函数使其失效的案例。
一、数据版本与原始语句及干系信息

1.版本信息
  1. SQL> select * from v$version;                                       
  2.                                                                     
  3. BANNER                                                              
  4. ----------------------------------------------------------------   
  5. Oracle Database 10g Release 10.2.0.3.0 - 64bit Production           
  6. PL/SQL Release 10.2.0.3.0 - Production                              
  7. CORE    10.2.0.3.0      Production                                 
  8. TNS for Linux: Version 10.2.0.3.0 - Production                     
  9. NLSRTL Version 10.2.0.3.0 - Production
复制代码
 2.原始语句与实在验计划
  1. SQL> set autotrace traceonly exp;                                                                 
  2.                                                                                                   
  3. SELECT acc_num,                                                                                   
  4.            curr_cd,                                                                                    
  5.            DECODE('20110728',                                                                           
  6.                           (SELECT TO_CHAR(LAST_DAY(TO_DATE('20110728', 'YYYYMMDD')),                                
  7.                                                           'YYYYMMDD')                                                                       
  8.                            FROM   DUAL),                                                                           
  9.                           0,                                                                                       
  10.                           adj_credit_int_lv1_amt + adj_credit_int_lv2_amt -                                         
  11.                           adj_debit_int_lv1_amt - adj_debit_int_lv2_amt) AS interest                                
  12. FROM   acc_pos_int_tbl ACC_POS_INT_TBL1                                                           
  13. WHERE  SUBSTR(business_date, 1, 6) = SUBSTR('20110728', 1, 6)                                    
  14.            AND business_date <= '20110728';                                                            
  15.                                                                                                   
  16. Execution Plan                                                                                    
  17. ----------------------------------------------------------                                       
  18. Plan hash value: 3114115399                                                                       
  19.                                                                                                   
  20. -------------------------------------------------------------------------------------            
  21. | Id  | Operation         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |            
  22. -------------------------------------------------------------------------------------            
  23. |   0 | SELECT STATEMENT  |                 |   336K|    12M| 96399   (1)| 00:19:17 |            
  24. |   1 |  FAST DUAL        |                 |     1 |       |     2   (0)| 00:00:01 |            
  25. |*  2 |  TABLE ACCESS FULL| ACC_POS_INT_TBL |   336K|    12M| 96399   (1)| 00:19:17 |            
  26. -------------------------------------------------------------------------------------            
  27.                                                                                                   
  28. Predicate Information (identified by operation id):                                               
  29. ---------------------------------------------------                                               
  30.                                                                                                   
  31.    2 - filter(SUBSTR("BUSINESS_DATE",1,6)='201107' AND                                            
  32.               "BUSINESS_DATE"<='20110728')
复制代码
从实验计划可以看出,SQL语句利用了全表扫描,而where 子句中只有唯一的一列business_date
3.表上的索引信息
  1. SQL> set autotrace off;                                                                                 
  2. SQL> set linesize 190                                                                                    
  3. SQL> @Idx_Info                                                                                          
  4. Enter value for owner: goex_admin                                                                        
  5. old  10:           AND owner = upper('&owner')                                                           
  6. new  10:           AND owner = upper('goex_admin')                                                      
  7. Enter value for table_name: ACC_POS_INT_TBL                                                              
  8. old  11:           AND a.table_name = upper('&table_name')                                               
  9. new  11:           AND a.table_name = upper('ACC_POS_INT_TBL')                                          
  10.                                                                                                          
  11. TABLE_NAME         INDEX_NAME               COL_NAM              CL_POS STATUS   IDX_TYP         DSCD   
  12. ------------------ ------------------------ -------------------- ------ -------- --------------- ----   
  13. ACC_POS_INT_TBL    ACC_POS_INT_10DIG_IDX    SYS_NC00032$              1 VALID    FUNCTION-BASED  ASC     
  14.                                                                                  NORMAL                  
  15.                                                                                                          
  16. ACC_POS_INT_TBL    ACC_POS_INT_10DIG_IDX    BUSINESS_DATE             2 VALID    FUNCTION-BASED  ASC     
  17.                                                                                  NORMAL                  
  18.                                                                                                          
  19. ACC_POS_INT_TBL    ACC_POS_INT_10DIG_IDX    CURR_CD                   3 VALID    FUNCTION-BASED  ASC     
  20.                                                                                  NORMAL                  
  21.                                                                                                          
  22. ACC_POS_INT_TBL    PK_ACC_POS_INT_TBL       ACC_NUM                   1 VALID    NORMAL          ASC     
  23. 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语句
  1. SELECT acc_num,                                                               
  2.            curr_cd,                                                                  
  3.            DECODE('20110728',                                                        
  4.                           (SELECT TO_CHAR(LAST_DAY(TO_DATE('20110728', 'YYYYMMDD')),            
  5.                                                           'YYYYMMDD')                                                   
  6.                            FROM   DUAL),                                                         
  7.                           0,                                                                     
  8.                           adj_credit_int_lv1_amt + adj_credit_int_lv2_amt -                     
  9.                           adj_debit_int_lv1_amt - adj_debit_int_lv2_amt) AS interest            
  10. FROM   acc_pos_int_tbl ACC_POS_INT_TBL1                                       
  11. WHERE  business_date >=                                                        
  12.            to_char(last_day(add_months(to_date('20110728', 'yyyymmdd'), -1)) + 1,   
  13.                            'yyyymmdd')                                                           
  14.            AND business_date <= '20110728';
复制代码
4.改造后的实验计划
  1. Execution Plan                                                                                               
  2. ----------------------------------------------------------                                                   
  3. Plan hash value: 66267922                                                                                    
  4.                                                                                                             
  5. --------------------------------------------------------------------------------------------------           
  6. | Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |           
  7. --------------------------------------------------------------------------------------------------           
  8. |   0 | SELECT STATEMENT            |                    |  1065K|    39M| 75043   (1)| 00:15:01 |           
  9. |   1 |  FAST DUAL                  |                    |     1 |       |     2   (0)| 00:00:01 |           
  10. |   2 |  TABLE ACCESS BY INDEX ROWID| ACC_POS_INT_TBL    |  1065K|    39M| 75043   (1)| 00:15:01 |           
  11. |*  3 |   INDEX SKIP SCAN           | PK_ACC_POS_INT_TBL | 33730 |       | 41180   (1)| 00:08:15 |           
  12. --------------------------------------------------------------------------------------------------           
  13.                                                                                                             
  14. Predicate Information (identified by operation id):                                                         
  15. ---------------------------------------------------                                                         
  16.                                                                                                             
  17.    3 - access("BUSINESS_DATE">='20110701' AND "BUSINESS_DATE"<='20110728')                                   
  18.        filter("BUSINESS_DATE">='20110701' AND "BUSINESS_DATE"<='20110728')                                   
复制代码
改造后可以看到SQL语句的实验计划已经由原来的全表扫描改为实验INDEX SKIP SCAN,但其cost也并没有低落多少
三、进一步分析

1.表的干系信息
  1. SQL> @Tab_Stat                                                                                       
  2. Enter value for input_table_name: ACC_POS_INT_TBL                                                     
  3. old  11: WHERE  table_name = upper('&input_table_name')                                               
  4. new  11: WHERE  table_name = upper('ACC_POS_INT_TBL')                                                
  5. Enter value for input_owner: goex_admin                                                               
  6. old  12:           AND owner = upper('&input_owner')                                                  
  7. new  12:           AND owner = upper('goex_admin')                                                   
  8.                                                                                                       
  9.   NUM_ROWS       BLKS    EM_BLKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN AVG_ROWS_PER_BLOCK LST_ANLY  STA   
  10. ---------- ---------- ---------- ---------- ---------- ----------- ------------------ --------- ---   
  11.   33659947     437206       1322        855          0          99                 77 27-SEP-11 NO
复制代码
2.索引的干系信息
  1. SQL> @Idx_Stat                                                                                                      
  2. Enter value for input_table_name: ACC_POS_INT_TBL                                                                    
  3. old  11: WHERE  table_name = upper('&input_table_name')                                                              
  4. new  11: WHERE  table_name = upper('ACC_POS_INT_TBL')                                                               
  5. Enter value for input_owner: goex_admin                                                                              
  6. old  12:           AND owner = upper('&input_owner')                                                                 
  7. new  12:           AND owner = upper('goex_admin')                                                                  
  8.                                                                                                                      
  9. BLEV IDX_NAME                          LF_BLKS   DST_KEYS   NUM_ROWS LF_PER_KEY DAT_BLK_PER_KEY   CLUS_FCT LST_ANLY  
  10. ---- ------------------------------ ---------- ---------- ---------- ---------- --------------- ---------- ---------
  11.    3 PK_ACC_POS_INT_TBL                 155658   33777720   33777720          1               1   33777447 27-SEP-11
  12.    3 ACC_POS_INT_10DIG_IDX              160247   32850596   32850596          1               1   32763921 27-SEP-11
复制代码
3.实验在BUSINESS_DATE列上创建索引
  1. SQL> create index I_ACC_POS_INT_TBL_BS_DT on ACC_POS_INT_TBL(BUSINESS_DATE) tablespace tbs_tmp nologging;            
  2.                                                                                                                      
  3. Index created.                                                                                                        
  4.                                                                                                                      
  5. SQL> @Idx_Stat                                                                                                        
  6. Enter value for input_table_name: ACC_POS_INT_TBL                                                                     
  7. old  11: WHERE  table_name = upper('&input_table_name')                                                               
  8. new  11: WHERE  table_name = upper('ACC_POS_INT_TBL')                                                                 
  9. Enter value for input_owner: goex_admin                                                                              
  10. old  12:           AND owner = upper('&input_owner')                                                                  
  11. new  12:           AND owner = upper('goex_admin')                                                                    
  12.                                                                                                                      
  13. BLEV IDX_NAME                          LF_BLKS   DST_KEYS   NUM_ROWS LF_PER_KEY DAT_BLK_PER_KEY   CLUS_FCT LST_ANLY   
  14. ---- ------------------------------ ---------- ---------- ---------- ---------- --------------- ---------- ---------  
  15.    2 I_ACC_POS_INT_TBL_BS_DT             93761        908   33659855        103             506     460007 30-SEP-11  
  16.    3 PK_ACC_POS_INT_TBL                 155658   33777720   33777720          1               1   33777447 27-SEP-11  
  17.    3 ACC_POS_INT_10DIG_IDX              160247   32850596   32850596          1               1   32763921 27-SEP-11
复制代码
建立索引后聚簇因子较小,差不多靠近表上块的数量
4.利用新创建索引后的实验计划
  1. Execution Plan                                                                                               
  2. ----------------------------------------------------------                                                   
  3. Plan hash value: 2183566226                                                                                 
  4.                                                                                                             
  5. -------------------------------------------------------------------------------------------------------      
  6. | Id  | Operation                   | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |      
  7. -------------------------------------------------------------------------------------------------------      
  8. |   0 | SELECT STATEMENT            |                         |  1065K|    39M| 17586   (1)| 00:03:32 |      
  9. |   1 |  FAST DUAL                  |                         |     1 |       |     2   (0)| 00:00:01 |      
  10. |   2 |  TABLE ACCESS BY INDEX ROWID| ACC_POS_INT_TBL         |  1065K|    39M| 17586   (1)| 00:03:32 |      
  11. |*  3 |   INDEX RANGE SCAN          | I_ACC_POS_INT_TBL_BS_DT |  1065K|       |  2984   (1)| 00:00:36 |      
  12. -------------------------------------------------------------------------------------------------------      
  13.                                                                                                             
  14. Predicate Information (identified by operation id):                                                         
  15. ---------------------------------------------------                                                         
  16.                                                                                                             
  17.    3 - access("BUSINESS_DATE">='20110701' AND "BUSINESS_DATE"<='20110728')                                   
复制代码
从上面的实验计划看出,SQL语句已经选择了新建的索引尽管返回的rows,bytes没有显着的厘革,但cost已经少了近7倍。
以上所述是小编给各人介绍的Oracle函数使索引列失效的办理办法,盼望对各人有所帮助。在此也非常感谢各人对脚本之家网站的支持!

帖子地址: 

回复

使用道具 举报

分享
推广
火星云矿 | 预约S19Pro,享500抵1000!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

草根技术分享(草根吧)是全球知名中文IT技术交流平台,创建于2021年,包含原创博客、精品问答、职业培训、技术社区、资源下载等产品服务,提供原创、优质、完整内容的专业IT技术开发社区。
  • 官方手机版

  • 微信公众号

  • 商务合作