设为首页收藏本站我的广告

运维网

 找回密码
 注册

QQ登录

只需一步,快速开始

扫一扫,访问微社区

搜索
运维网 首页 数据库运维 SQL Server 查看内容

函数使得索引列失效

2012-4-27 13:37| 发布者: yunweiw.com| 查看: 4888| 评论: 0|原作者: 运维网|来自: 网络

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

下一篇:一次SQL分页的优化

上一篇:SQL多表联查优化


鲜花

握手

雷人

路过

鸡蛋
加入阿里云推荐返利15%

最新评论

QQ|申请友链|sitemap|手机版|小黑屋|Archiver|运维网 ( 京ICP备16008201号  

GMT+8, 2016-12-9 21:39 , Processed in 0.054908 second(s), 28 queries , Xcache On.

Powered by Discuz! X3.2 Licensed

© 2001-2013 Comsenz Inc.

返回顶部