查看: 2048|回复: 12

[PL/SQL] 不加新索引,还有进一步优化的可能吗?

[复制链接]
论坛徽章:
30
红宝石
日期:2012-10-10 14:50:58技术图书徽章
日期:2017-08-17 11:00:25奥运会纪念徽章:举重
日期:2016-09-18 11:29:42马上有钱
日期:2014-04-17 16:12:27马上加薪
日期:2014-03-31 14:47:51技术图书徽章
日期:2014-03-31 10:52:30优秀写手
日期:2013-12-18 09:29:162013年新春福章
日期:2013-02-25 14:51:24蓝锆石
日期:2012-10-10 14:50:58萤石
日期:2012-10-10 14:50:58
发表于 2017-11-17 10:03 | 显示全部楼层 |阅读模式
本帖最后由 快乐的大个子 于 2017-11-20 09:37 编辑

背景:
ALEX_TEST1表中大约210万条数据,只有一个索引,建在B列上,B列为日期类型。ALEX_TEST2表上只有几百条数据,不是keypoint,就不介绍索引情况了,原SQL如下:
SELECT A,B,C
FROM ALEX_TEST1
WHERE G IN(SELECT D from ALEX_TEST2 where E='M')
AND F='IPM' AND B=(SELECT MAX(B) FROM ALEX_TEST1 where F='IPM')
;

由于F列上没有索引,原SQL执行时须对210万全表扫描。执行时间为58s。

修改SQL如下:
SELECT A,B,C
FROM ALEX_TEST1
WHERE G IN (SELECT D FROM ALEX_TEST2 WHEREE='M')
AND F ='IPM'
AND B = (SELECT MAX(B) FROM (SELECT B, F
                             FROM ALEX_TEST1
                             WHERE B > sysdate - 365)
                             WHERE F='IPM')
;

实际就是有点分页查询的意思,按照业务逻辑,可以时间定位于1年之内,将全表扫描从210万降到3万以下,以提高效率。

在不添加新索引的情况下,是否还有更好的改进办法呢?
论坛徽章:
401
紫蛋头
日期:2012-05-21 10:19:41迷宫蛋
日期:2012-06-06 16:02:49奥运会纪念徽章:足球
日期:2012-06-29 15:30:06奥运会纪念徽章:排球
日期:2012-07-10 21:24:24鲜花蛋
日期:2012-07-16 15:24:59奥运会纪念徽章:拳击
日期:2012-08-07 10:54:50奥运会纪念徽章:羽毛球
日期:2012-08-21 15:55:33奥运会纪念徽章:蹦床
日期:2012-08-21 21:09:51奥运会纪念徽章:篮球
日期:2012-08-24 10:29:11奥运会纪念徽章:体操
日期:2012-09-07 16:40:00
发表于 2017-11-17 10:35 | 显示全部楼层
365改更小

使用道具 举报

回复
论坛徽章:
30
红宝石
日期:2012-10-10 14:50:58技术图书徽章
日期:2017-08-17 11:00:25奥运会纪念徽章:举重
日期:2016-09-18 11:29:42马上有钱
日期:2014-04-17 16:12:27马上加薪
日期:2014-03-31 14:47:51技术图书徽章
日期:2014-03-31 10:52:30优秀写手
日期:2013-12-18 09:29:162013年新春福章
日期:2013-02-25 14:51:24蓝锆石
日期:2012-10-10 14:50:58萤石
日期:2012-10-10 14:50:58
 楼主| 发表于 2017-11-17 10:43 | 显示全部楼层

晕。。。O O兄,还有其他建议否?

使用道具 举报

回复
论坛徽章:
526
奥运会纪念徽章:垒球
日期:2008-09-15 01:28:12生肖徽章2007版:鸡
日期:2008-11-17 23:40:58生肖徽章2007版:马
日期:2008-11-18 05:09:48数据库板块每日发贴之星
日期:2008-11-29 01:01:02数据库板块每日发贴之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:532009日食纪念
日期:2009-07-22 09:30:00
发表于 2017-11-18 00:09 | 显示全部楼层
给出不能加索引的理由。这个表也不是那么大。
给出旧计划,看看对ALEX_TEST1扫描几次。
有可能用分析函数改写会有帮助,但是也不一定。

使用道具 举报

回复
认证徽章
论坛徽章:
0
发表于 2017-11-18 14:49 | 显示全部楼层
原始SQL应该会对ALEX_TEST1全表扫描两次,试下这条SQL语句的速度吧
with
sql1 as (select A, B, C from ALEX_TEST1 where F='IPM' and G IN (SELECT D from ALEX_TEST2 where E='M'))
select A, B, C
from sql1
where B=(SELECT MAX(B) FROM sql1);

使用道具 举报

回复
论坛徽章:
30
红宝石
日期:2012-10-10 14:50:58技术图书徽章
日期:2017-08-17 11:00:25奥运会纪念徽章:举重
日期:2016-09-18 11:29:42马上有钱
日期:2014-04-17 16:12:27马上加薪
日期:2014-03-31 14:47:51技术图书徽章
日期:2014-03-31 10:52:30优秀写手
日期:2013-12-18 09:29:162013年新春福章
日期:2013-02-25 14:51:24蓝锆石
日期:2012-10-10 14:50:58萤石
日期:2012-10-10 14:50:58
 楼主| 发表于 2017-11-20 09:42 | 显示全部楼层
newkid 发表于 2017-11-18 00:09
给出不能加索引的理由。这个表也不是那么大。
给出旧计划,看看对ALEX_TEST1扫描几次。
有可能用分析函数 ...

不加索引的理由是因为这张表会有Bulk insert,插入的效率,且不想因为索引引起其他批次的状况。

其实还有一张表,逻辑和这张表相仿,数据量为2000万,也有Bulk insert,影响更广一些。先用小表找出解决方法,然后再在大表上应用。

使用道具 举报

回复
论坛徽章:
30
红宝石
日期:2012-10-10 14:50:58技术图书徽章
日期:2017-08-17 11:00:25奥运会纪念徽章:举重
日期:2016-09-18 11:29:42马上有钱
日期:2014-04-17 16:12:27马上加薪
日期:2014-03-31 14:47:51技术图书徽章
日期:2014-03-31 10:52:30优秀写手
日期:2013-12-18 09:29:162013年新春福章
日期:2013-02-25 14:51:24蓝锆石
日期:2012-10-10 14:50:58萤石
日期:2012-10-10 14:50:58
 楼主| 发表于 2017-11-20 11:28 | 显示全部楼层
Johnelse 发表于 2017-11-18 14:49
原始SQL应该会对ALEX_TEST1全表扫描两次,试下这条SQL语句的速度吧
with
sql1 as (select A, B, C from  ...

我试了下,效果和原SQL 没有什么区别,原SQL执行计划中有一次全表扫描,用兄台的SQL也是同样的效果。

使用道具 举报

回复
认证徽章
论坛徽章:
0
发表于 2017-11-20 13:08 | 显示全部楼层
先给ALEX_TEST1表按B列分区, 然后在B列上建局部索引。这样可以减少Insert引起的索引维护,又可以避免全表扫描。

使用道具 举报

回复
论坛徽章:
526
奥运会纪念徽章:垒球
日期:2008-09-15 01:28:12生肖徽章2007版:鸡
日期:2008-11-17 23:40:58生肖徽章2007版:马
日期:2008-11-18 05:09:48数据库板块每日发贴之星
日期:2008-11-29 01:01:02数据库板块每日发贴之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:532009日食纪念
日期:2009-07-22 09:30:00
发表于 2017-11-21 00:02 | 显示全部楼层
快乐的大个子 发表于 2017-11-20 09:42
不加索引的理由是因为这张表会有Bulk insert,插入的效率,且不想因为索引引起其他批次的状况。

其实 ...

2000万的你们也打算全表扫描?
新索引是会增加插入的成本,但是要看你们每次插入多少行,然后具体测试其影响,才能决定取舍。
就这个查询来说增加索引应该是最好的解决办法,否则至少全扫描一遍。

使用道具 举报

回复
论坛徽章:
30
红宝石
日期:2012-10-10 14:50:58技术图书徽章
日期:2017-08-17 11:00:25奥运会纪念徽章:举重
日期:2016-09-18 11:29:42马上有钱
日期:2014-04-17 16:12:27马上加薪
日期:2014-03-31 14:47:51技术图书徽章
日期:2014-03-31 10:52:30优秀写手
日期:2013-12-18 09:29:162013年新春福章
日期:2013-02-25 14:51:24蓝锆石
日期:2012-10-10 14:50:58萤石
日期:2012-10-10 14:50:58
 楼主| 发表于 2017-11-21 09:45 | 显示全部楼层
newkid 发表于 2017-11-21 00:02
2000万的你们也打算全表扫描?
新索引是会增加插入的成本,但是要看你们每次插入多少行,然后具体测试其 ...

我其实也知道索引针对这个sql是最好的选择,而且Bitmap最好,因为source列只有10种可能性,完全符合bitmap的应用场景。

但俺是新来的,对总的应用流程并不熟,不想轻易加索引导致引火烧身而已。况且,如果按我之前的改法,并不会full scan,而是range scan,执行计划中的cost从38000+降到7000左右。

使用道具 举报

回复

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

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备16024965号 北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表