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

 找回密码
 注册
查看: 1250|回复: 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万以下,以提高效率。

在不添加新索引的情况下,是否还有更好的改进办法呢?
论坛徽章:
394
阿斯顿马丁
日期:2014-01-03 13:53:522014年世界杯参赛球队:喀麦隆
日期:2014-07-11 12:10:53马上有对象
日期:2014-04-09 16:19:542014年世界杯参赛球队: 洪都拉斯
日期:2014-06-25 08:25:55itpub13周年纪念徽章
日期:2014-09-28 10:55:55itpub13周年纪念徽章
日期:2014-10-01 15:27:22itpub13周年纪念徽章
日期:2014-10-09 12:04:18马上有钱
日期:2014-10-14 21:37:37马上有钱
日期:2015-01-22 00:39:13喜羊羊
日期:2015-02-20 22:26:07
发表于 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兄,还有其他建议否?

使用道具 举报

回复
论坛徽章:
480
榜眼
日期:2015-09-09 10:34:21秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12状元
日期:2015-11-23 10:04:09举人
日期:2015-11-23 10:04:09
发表于 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引起的索引维护,又可以避免全表扫描。

使用道具 举报

回复
论坛徽章:
480
榜眼
日期:2015-09-09 10:34:21秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12状元
日期:2015-11-23 10:04:09举人
日期:2015-11-23 10:04:09
发表于 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文档 | IT博客
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛 | SAP ERP系统
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
京ICP备16024965号 北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表