ITPUB论坛-中国最专业的IT技术社区

 
 注册
热搜:
查看: 16415|回复: 22

[精华] 巧用函数索引优化一例

[复制链接]
论坛徽章:
12
授权会员
日期:2007-07-11 20:34:402010新春纪念徽章
日期:2010-03-01 11:19:07生肖徽章2007版:猪
日期:2009-12-09 20:13:20生肖徽章2007版:兔
日期:2009-11-24 12:05:57鲜花蛋
日期:2011-11-24 21:31:10ITPUB8周年纪念徽章
日期:2009-09-27 10:21:20生肖徽章2007版:鼠
日期:2009-09-10 15:41:532009新春纪念徽章
日期:2009-01-04 14:52:28生肖徽章2007版:鸡
日期:2008-12-01 16:52:42奥运会纪念徽章:花样游泳
日期:2008-10-24 13:28:54
跳转到指定楼层
1#
发表于 2008-9-12 10:15 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式
根据statspack报告发现某语句执行频繁而且全表扫描,消耗资源比较严重,跟踪定位来源于生产库某定时任务是每5分钟执行一次。
提取语句,看执行计划。



SQL> set autotrace traceonly
SQL> select /*+no_index(a,ind_servicetab_endtime) */id, phone, productid, a.producttype, region, bossreqid
  2    from t_servicetab a, t_product r
  3   where triggerflag = '1'
  4     and endtime > Sysdate
  5     and starttime < Sysdate
  6     and a.productid = r.productsign
  7     and r.bigtype = 1
  8     and rownum <= 500;
已选择82行。
已用时间:  00: 00: 25.01
Execution Plan
----------------------------------------------------------                     
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=6493 Card=5 Bytes=12         
   1    0   COUNT (STOPKEY)                                                     
   2    1     HASH JOIN (Cost=6493 Card=5 Bytes=1215)                           
   3    2       TABLE ACCESS (FULL) OF 'T_PRODUCT' (Cost=2 Card=4           
   4    2       TABLE ACCESS (FULL) OF 'T_SERVICETAB' (Cost=6490 Card         


Statistics
----------------------------------------------------------                     
          0  recursive calls                                                   
          0  db block gets                                                      
      68387  consistent gets                                                   
      65199  physical reads
                                                     
          0  redo size                                                         
       4758  bytes sent via SQL*Net to client                                   
        311  bytes received via SQL*Net from client                             
          7  SQL*Net roundtrips to/from client                                 
          0  sorts (memory)                                                     
          0  sorts (disk)                                                      
         82  rows processed                                                     
SQL> Select sum(bytes)/1024/1024 "MB" From User_Extents ue
  2  Where ue.segment_name='T_SERVICETAB'
  3  /
        MB
----------
       542
根据查询特点和数据分布特征,定时任务是当starttime由小于当前时间变为大于当前时间时,把triggerflag由1置为0,T_SERVICETAB(400W数据)表其中大部分数据starttime是小于当前时间的,且triggerflag为0.但只有小于5%的数据是starttime小于当前时间而且triggerflag为1的, 根据业务特点建立函数索引。
--普通索引
SQL> Create Index ind_ TRIGGERFLAG On t_servicetab(TRIGGERFLAG);
索引已创建。
已用时间:  00: 00: 54.05
SQL> set autotrace off
SQL> Select Sum(bytes)/1024/1024 "MB"  From User_Extents us --542 496
  2  Where us.segment_name = upper('ind_ TRIGGERFLAG');
        MB                                                                     
----------                                                                     
        72   
--函数索引
SQL> Create Index ind_func On t_servicetab(decode(TRIGGERFLAG,'1','1'))
  2  /
索引已创建。
已用时间:  00: 00: 15.08
SQL> Select Sum(bytes)/1024/1024 "MB"  From User_Extents us
  2  Where us.segment_name = upper('ind_func');
        MB                                                                     
----------                                                                     
         3   
SQL> select /*+no_index(a,ind_servicetab_endtime) */id, phone, productid, a.producttype, region, bossreqid
  2    from t_servicetab a, t_product r
  3   where decode(triggerflag,'1','1') = '1'
  4     and endtime > Sysdate
  5     and starttime < Sysdate
  6     and a.productid = r.productsign
  7     and r.bigtype = 1
  8     and rownum <= 500;
已选择82行。
已用时间:  00: 00: 02.03
Execution Plan
----------------------------------------------------------                     
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=2 Bytes=486)           
   1    0   COUNT (STOPKEY)                                                     
   2    1     NESTED LOOPS (Cost=4 Card=2 Bytes=486)                           
   3    2       TABLE ACCESS (FULL) OF 'T_PRODUCT' (Cost=2 Card=1           
   4    2       TABLE ACCESS (BY INDEX ROWID) OF 'T_SERVICETAB' (Cost         
   5    4         INDEX (RANGE SCAN) OF 'IND_FUNC' (NON-UNIQUE) (Cost=         


Statistics
----------------------------------------------------------                     
          0  recursive calls                                                   
          0  db block gets                                                      
    227334  consistent gets                                                   
          0  physical reads                                                     
          0  redo size                                                         
       5494  bytes sent via SQL*Net to client                                   
        558  bytes received via SQL*Net from client                             
         14  SQL*Net roundtrips to/from client                                 
          0  sorts (memory)                                                     
          0  sorts (disk)                                                      
         82  rows processed      
由于TRIGGERFLAG为1的数据还是有很多,这里面的数据只有少部分满足starttime<sysdate, 继续根据索引与null的特点,重新创建triggerflag与starttime关联的函数。
SQL> Create Index ind_func2 On t_servicetab(decode(triggerflag,'1',starttime) )
  2  /
索引已创建。
已用时间:  00: 00: 16.01
SQL> select /*+no_index(a,ind_servicetab_endtime) */id, phone, productid, a.producttype, region, bossreqid
  2    from t_servicetab a, t_product r
  3   Where triggerflag = '1'
  4     and endtime > Sysdate
  5     and decode(triggerflag,'1',starttime) < Sysdate
  6     and a.productid = r.productsign
  7     and r.bigtype = 1
  8     and rownum <= 500;
已选择82行。
已用时间:  00: 00: 00.00
Execution Plan
----------------------------------------------------------                     
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=2 Bytes=486)         
   1    0   COUNT (STOPKEY)                                                     
   2    1     NESTED LOOPS (Cost=11 Card=2 Bytes=486)                           
   3    2       TABLE ACCESS (FULL) OF 'T_PRODUCT' (Cost=2 Card=1           
   4    2       TABLE ACCESS (BY INDEX ROWID) OF 'T_SERVICETAB' (Cost         
   5    4         INDEX (RANGE SCAN) OF 'IND_FUNC2' (NON-UNIQUE) (Cost         


Statistics
----------------------------------------------------------                     
         62  recursive calls                                                   
          0  db block gets                                                      
        470  consistent gets                                                   
          2  physical reads                                                     
          0  redo size                                                         
       5494  bytes sent via SQL*Net to client                                   
        558  bytes received via SQL*Net from client                             
         14  SQL*Net roundtrips to/from client                                 
          0  sorts (memory)                                                     
          0  sorts (disk)                                                      
         82  rows processed                                                     
到此,数据库的物理读和逻辑读大大降低,优化完毕。
小结:
根据业务规则及数据存储分布,结合null值在索引中不存储,巧用函数索引来优化。


[ 本帖最后由 bolun761 于 2008-9-12 12:06 编辑 ]
论坛徽章:
136
ITPUB年度最佳技术回答奖
日期:2010-06-12 13:17:14现代
日期:2013-10-02 14:53:59路虎
日期:2013-11-22 12:26:182014年新春福章
日期:2014-02-18 16:42:02马上有房
日期:2014-02-18 16:42:02马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:14马上加薪
日期:2014-02-19 11:55:14
2#
发表于 2008-9-12 10:59 | 只看该作者
根据查询特点和数据分布特征,定时任务是当starttime由小于当前时间变为大于当前时间时,把triggerflag由1置为0,T_SERVICETAB(400W数据)表其中大部分数据starttime是小于当前时间的,而且triggerflag为0,只有小于5%左右的数据是starttime大于当前时间而且triggerflag为1的, 根据业务特点建立函数索引。

说的有点矛盾啊??

使用道具 举报

回复
论坛徽章:
12
授权会员
日期:2007-07-11 20:34:402010新春纪念徽章
日期:2010-03-01 11:19:07生肖徽章2007版:猪
日期:2009-12-09 20:13:20生肖徽章2007版:兔
日期:2009-11-24 12:05:57鲜花蛋
日期:2011-11-24 21:31:10ITPUB8周年纪念徽章
日期:2009-09-27 10:21:20生肖徽章2007版:鼠
日期:2009-09-10 15:41:532009新春纪念徽章
日期:2009-01-04 14:52:28生肖徽章2007版:鸡
日期:2008-12-01 16:52:42奥运会纪念徽章:花样游泳
日期:2008-10-24 13:28:54
3#
 楼主| 发表于 2008-9-12 11:13 | 只看该作者
谢谢指出,已修正!

使用道具 举报

回复
认证徽章
论坛徽章:
131
2006年度最佳技术回答
日期:2007-01-24 12:58:48福特
日期:2013-10-24 13:57:422014年新春福章
日期:2014-02-18 16:41:11马上有车
日期:2014-02-18 16:41:11马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:14马上加薪
日期:2014-02-19 11:55:142013年新春福章
日期:2013-02-25 14:51:24
4#
发表于 2008-9-12 11:26 | 只看该作者
很好的思路~~

使用道具 举报

回复
认证徽章
论坛徽章:
131
2006年度最佳技术回答
日期:2007-01-24 12:58:48福特
日期:2013-10-24 13:57:422014年新春福章
日期:2014-02-18 16:41:11马上有车
日期:2014-02-18 16:41:11马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:14马上加薪
日期:2014-02-19 11:55:142013年新春福章
日期:2013-02-25 14:51:24
5#
发表于 2008-9-12 11:27 | 只看该作者
可以加个精华~~

使用道具 举报

回复
招聘 : 产品经理/专员
论坛徽章:
67
管理团队2006纪念徽章
日期:2006-04-16 22:44:45ITPUB十周年纪念徽章
日期:2011-11-01 16:20:282012新春纪念徽章
日期:2012-02-13 15:12:252012新春纪念徽章
日期:2012-02-13 15:12:252012新春纪念徽章
日期:2012-02-13 15:12:252012新春纪念徽章
日期:2012-02-13 15:12:252012新春纪念徽章
日期:2012-02-13 15:12:25版主1段
日期:2012-05-15 15:24:112013年新春福章
日期:2013-02-25 14:51:24夏利
日期:2013-10-10 13:12:33
6#
发表于 2008-9-12 12:02 | 只看该作者
好想法

使用道具 举报

回复
论坛徽章:
12
授权会员
日期:2007-07-11 20:34:402010新春纪念徽章
日期:2010-03-01 11:19:07生肖徽章2007版:猪
日期:2009-12-09 20:13:20生肖徽章2007版:兔
日期:2009-11-24 12:05:57鲜花蛋
日期:2011-11-24 21:31:10ITPUB8周年纪念徽章
日期:2009-09-27 10:21:20生肖徽章2007版:鼠
日期:2009-09-10 15:41:532009新春纪念徽章
日期:2009-01-04 14:52:28生肖徽章2007版:鸡
日期:2008-12-01 16:52:42奥运会纪念徽章:花样游泳
日期:2008-10-24 13:28:54
7#
 楼主| 发表于 2008-9-12 12:15 | 只看该作者
呵呵~~,多谢2位斑竹鼓励,  
多说一句: 以上问题是根据生产库的statspack定位出来的,以上脚本是在测试环境模拟测试脚本。
生产库,切记不可以随意操作!

同时申请授精!^_^

使用道具 举报

回复
认证徽章
论坛徽章:
186
状元
日期:2016-04-28 14:18:17榜眼
日期:2016-04-28 14:18:17探花
日期:2016-04-28 14:18:17进士
日期:2016-04-28 14:18:17举人
日期:2016-04-28 14:18:17红宝石
日期:2012-04-13 17:18:06蓝锆石
日期:2012-02-20 12:20:11紫水晶
日期:2012-04-19 12:49:17祖母绿
日期:2012-02-27 22:10:14海蓝宝石
日期:2012-02-27 16:58:24
8#
发表于 2008-9-12 21:02 | 只看该作者
支持原创,不错的文章。

使用道具 举报

回复
论坛徽章:
47
蒙奇·D·路飞
日期:2017-03-27 08:04:23马上有车
日期:2014-02-18 16:41:112014年新春福章
日期:2014-02-18 16:41:11一汽
日期:2013-09-01 20:46:27复活蛋
日期:2013-03-13 07:55:232013年新春福章
日期:2013-02-25 14:51:24ITPUB 11周年纪念徽章
日期:2012-10-09 18:03:322012新春纪念徽章
日期:2012-02-13 15:13:202012新春纪念徽章
日期:2012-02-13 15:13:202012新春纪念徽章
日期:2012-02-13 15:13:20
9#
发表于 2008-9-13 06:46 | 只看该作者
bolun761, excellent work! I would think of creating a bitmap index on trigger_flag because it's either 0 or 1, and the index would be very small. But there may be problems during regular DML.

Your function based index is great. With that, I think you can remove the triggerflag='1' condition, because decode(triggerflag,'1',starttime) implies that if it's not '1', it's null, which never matches anything except null. That may save a little bit CPU. Could be negligible saving, though.

Yong Huang

使用道具 举报

回复
论坛徽章:
59
狮子座
日期:2016-03-26 13:35:402013年新春福章
日期:2013-02-25 14:51:24双黄蛋
日期:2013-02-25 11:06:15ITPUB 11周年纪念徽章
日期:2012-10-09 18:06:20灰彻蛋
日期:2012-04-25 13:19:33最佳人气徽章
日期:2012-03-13 17:39:18玉石琵琶
日期:2012-02-21 15:04:38紫蛋头
日期:2012-03-14 11:16:09ITPUB十周年纪念徽章
日期:2011-11-01 16:21:15鲜花蛋
日期:2011-11-30 14:13:01
10#
发表于 2008-9-13 07:25 | 只看该作者
又是一个修改sql保证逻辑相同的例子

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则

DTCC2018购票6.8折优惠进行时

中国数据库技术大会是国内数据库及大数据领域规模最大、最受欢迎的技术交流盛会。 2018年5月10-12日,第九届中国数据库技术大会将如约而至。本届大会以“数领先机•智赢未来”为主题,设定2大主会场及20个技术专场,邀请来自国内外互联网、金融、教育等行业百余位技术专家,共同探讨Oracle、MySQL、NoSQL、大数据等领域的前瞻性热点话题与技术。
----------------------------------------
优惠时间:2018年2月13日前

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档 |
  | | |
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
 北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表
北京赛车pk10 申博官网 北京赛车pk10 六台宝典现场开奖 北京赛车pk10历史记录 北京赛车开奖 北京赛车预测 949494开奖结果今晚 威尼斯人线上娱乐 手机投注平台 幸运28投注技巧 pk10助赢软件 北京pk10百度鼎盛彩票网 北京赛车聚彩 北京pk10如何稳杀3码 pk10稳赢方法 pk10定位计划 pk10包赢计划群