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

 找回密码
 注册
查看: 1334|回复: 10

[SQL] 请教下表关联的疑问,执行慢啊~

[复制链接]
认证徽章
论坛徽章:
2
弗兰奇
日期:2017-06-25 12:14:04山治
日期:2018-01-25 10:46:01
发表于 2018-1-23 11:54 | 显示全部楼层 |阅读模式
本帖最后由 wwwyibin518 于 2018-1-23 11:54 编辑

表TB_BIND_ADSL_ITV   ,表别名a, 数据量500W左右(大表)
表TB_DEVICE_INFO       ,表别名b,  数据量1W左右(小表)
大表的bras_ip字段有本地索引

执行下面sql前,大表已做了表分析,也检查了本地索引状态是USABLE

下图1和图2,图2的SQL比图1的SQL 多了判断索引字段非空, AND a.bras_ip IS NOT NULL
【疑问1】:连接为什么从HASH JOIN变成了NESTED LOOPS?
【疑问2】:图2的SQL,为什么将大表作为驱动表?(所以导致该sql执行的超级慢~~~~~

图1

图1

.

图2

图2




图3, SQL的写法改变了下, FROM先跟小表, 再右连大表,加上 hint,/*+ ordered */,想让执行计划将小表当作驱动表,再索引查找大表
【疑问3】:但是为什么执行计划显示还是以大表作驱动表? 慢~~~

图3

图3















论坛徽章:
126
ITPUB元老
日期:2007-07-04 17:27:50会员2007贡献徽章
日期:2007-09-26 18:42:10现任管理团队成员
日期:2011-05-07 01:45:08优秀写手
日期:2015-01-09 06:00:14版主7段
日期:2015-07-16 02:10:00
发表于 2018-1-23 15:04 | 显示全部楼层
目前分区表是一个保留表, 目前这个a.bras_ip is not null应该写在where条件中, 不要写在on的连接条件, 因为, 表别名a是保留表, 你这个地方改一下, 在执行以下, 贴出出执行计划

使用道具 举报

回复
认证徽章
论坛徽章:
16
ITPUB十周年纪念徽章
日期:2011-11-01 16:26:29蒙奇·D·路飞
日期:2018-01-31 16:03:23秀才
日期:2018-01-02 15:35:05技术图书徽章
日期:2018-01-02 15:30:09秀才
日期:2018-01-02 15:22:40技术图书徽章
日期:2017-12-21 14:05:31妮可·罗宾
日期:2017-11-17 10:43:23技术图书徽章
日期:2017-11-17 09:13:02娜美
日期:2017-12-15 13:56:33鲜花蛋
日期:2011-12-20 16:06:45
发表于 2018-1-23 15:12 | 显示全部楼层
我想因為這裡使用outer join:不管你使用ordered 還是 leading 驅動表都不會變,因為left join 就要取左邊表的所有數據再去和其它join。
但是hash join和 nested loop可以使用hint: use_nl(a b) 和use_hash(a b) 去控制

使用道具 举报

回复
认证徽章
论坛徽章:
2
弗兰奇
日期:2017-06-25 12:14:04山治
日期:2018-01-25 10:46:01
发表于 2018-1-23 15:34 | 显示全部楼层
bell6248 发表于 2018-1-23 15:04
目前分区表是一个保留表, 目前这个a.bras_ip is not null应该写在where条件中, 不要写在on的连接条件,  ...

SELECT a.*, b.*
FROM tb_bind_adsl_itv PARTITION(P200) a
LEFT JOIN tb_device_info b ON a.city_id = b.city_id AND a.bras_ip = b.device_ip
WHERE a.bras_ip IS NOT NULL;

Plan Hash Value  : 1512300575
---------------------------------------------------------------------------------------------
| Id  | Operation                | Name             | Rows    | Bytes     | Cost | Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                  | 4663583 | 867426438 | 9938 | 00:02:00 |
| * 1 |   HASH JOIN RIGHT OUTER  |                  | 4663583 | 867426438 | 9938 | 00:02:00 |
|   2 |    TABLE ACCESS FULL     | TB_DEVICE_INFO   |    9799 |   1264071 |   57 | 00:00:01 |
|   3 |    PARTITION LIST SINGLE |                  | 4663583 | 265824231 | 9863 | 00:01:59 |
| * 4 |     TABLE ACCESS FULL    | TB_BIND_ADSL_ITV | 4663583 | 265824231 | 9863 | 00:01:59 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 1 - access("A"."BRAS_IP"="B"."DEVICE_IP"(+) AND "A"."CITY_ID"="B"."CITY_ID"(+))
* 4 - filter("A"."BRAS_IP" IS NOT NULL)


但是,这样最后出来的结果集,就少了 a.bras_ip IS NULL的数据了。
不过1楼贴的SQL,在ON后加a.bras_ip IS NOT NULL好像意义不大。
第一次听保留表这个字眼~.百度很少相关信息..
其实,本意是想要A表全数据,然后关联B表取相关字段信息。

使用道具 举报

回复
认证徽章
论坛徽章:
2
弗兰奇
日期:2017-06-25 12:14:04山治
日期:2018-01-25 10:46:01
发表于 2018-1-23 15:36 | 显示全部楼层
guocun09 发表于 2018-1-23 15:12
我想因為這裡使用outer join:不管你使用ordered 還是 leading 驅動表都不會變,因為left join 就要取左邊 ...

是啊~. 试过 ordered, leading, usd_nl,执行计划都没有变化...
搜的资料都是以inner join当测试事例的。
可能真的是outer join的关系吧... hint都没效.

使用道具 举报

回复
论坛徽章:
126
ITPUB元老
日期:2007-07-04 17:27:50会员2007贡献徽章
日期:2007-09-26 18:42:10现任管理团队成员
日期:2011-05-07 01:45:08优秀写手
日期:2015-01-09 06:00:14版主7段
日期:2015-07-16 02:10:00
发表于 2018-1-23 16:32 | 显示全部楼层
wwwyibin518 发表于 2018-1-23 15:34
SELECT a.*, b.*
FROM tb_bind_adsl_itv PARTITION(P200) a
LEFT JOIN tb_device_info b ON a.city_id  ...

这个条件为何要加, 你的需求是什么?

使用道具 举报

回复
认证徽章
论坛徽章:
2
弗兰奇
日期:2017-06-25 12:14:04山治
日期:2018-01-25 10:46:01
发表于 2018-1-23 18:25 | 显示全部楼层
bell6248 发表于 2018-1-23 16:32
这个条件为何要加, 你的需求是什么?

想要A表分区表全量数据,左连B表取B表一些字段,再作后续处理。

当时是想,a.bras_ip有索引,null值不走索引,所以在ON后加上a.bras_ip IS NOT NULL

现在再想,应该加 b.device_ip IS NOT NULL才对吧.. -.-

SELECT a.*, b.*
FROM tb_bind_adsl_itv PARTITION(P200) a
LEFT JOIN tb_device_info b ON a.city_id = b.city_id AND a.bras_ip = b.device_ip AND b.device_ip IS NOT NULL

Plan Hash Value  : 1512300575

---------------------------------------------------------------------------------------------
| Id  | Operation                | Name             | Rows    | Bytes     | Cost | Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                  | 4701286 | 874439196 | 9935 | 00:02:00 |
| * 1 |   HASH JOIN RIGHT OUTER  |                  | 4701286 | 874439196 | 9935 | 00:02:00 |
| * 2 |    TABLE ACCESS FULL     | TB_DEVICE_INFO   |    9789 |   1262781 |   57 | 00:00:01 |
|   3 |    PARTITION LIST SINGLE |                  | 4701286 | 267973302 | 9860 | 00:01:59 |
|   4 |     TABLE ACCESS FULL    | TB_BIND_ADSL_ITV | 4701286 | 267973302 | 9860 | 00:01:59 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 1 - access("A"."BRAS_IP"="B"."DEVICE_IP"(+) AND "A"."CITY_ID"="B"."CITY_ID"(+))
* 2 - filter("B"."DEVICE_IP"(+) IS NOT NULL)


不过,不知道该SQL在ON后加 a.bras_ip IS NOT NULL 执行计划会将大表作驱动表

使用道具 举报

回复
论坛徽章:
487
秀才
日期:2015-09-09 10:33:01秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12状元
日期:2015-11-23 10:04:09举人
日期:2015-11-23 10:04:09秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21
发表于 2018-1-23 22:57 | 显示全部楼层
这个NOT NULL条件加在WHERE还是ON, 在ON的哪张表,其意义完全不同,你必须搞清楚再来,不要乱加。
看起来第一个计划是最好的,有什么不满意?
至于为什么驱动表如此选择,因为CBO觉得反过来代价更高。如果你这索引选择性不好,一万次索引扫描还不如一次全表扫描。你每次索引扫描还要回表去取另外一个连接列来判断。

使用道具 举报

回复
认证徽章
论坛徽章:
2
弗兰奇
日期:2017-06-25 12:14:04山治
日期:2018-01-25 10:46:01
发表于 2018-1-24 09:42 | 显示全部楼层
newkid 发表于 2018-1-23 22:57
这个NOT NULL条件加在WHERE还是ON, 在ON的哪张表,其意义完全不同,你必须搞清楚再来,不要乱加。
看起来 ...

是的,NOT null的条件加错了。
一心想着索引索引 能提高效率,
还专门在两表关联的字段上,大表的a.bras_ip新建索引

SQL好了,执行计划是HASH JOIN连接,表关联用不着这索引,我就删了大表的a.bras_ip索引了。

使用道具 举报

回复
认证徽章
论坛徽章:
8
2009新春纪念徽章
日期:2009-01-04 14:52:28祖国60周年纪念徽章
日期:2009-10-09 08:28:002010新春纪念徽章
日期:2010-03-01 11:07:24ITPUB9周年纪念徽章
日期:2010-10-08 09:32:25ITPUB十周年纪念徽章
日期:2011-11-01 16:23:262013年新春福章
日期:2013-02-25 14:51:24沸羊羊
日期:2015-03-04 14:51:522015年新春福章
日期:2015-03-06 11:57:31
发表于 2018-1-24 13:42 | 显示全部楼层
bell6248 发表于 2018-1-23 15:04
目前分区表是一个保留表, 目前这个a.bras_ip is not null应该写在where条件中, 不要写在on的连接条件,  ...

1、你加了个not null条件后,优化器认为小表作为内行源会更高效,如果这个小表上有合适的索引就会走索引;
2、之所以将大表作为驱动表,是因为你的左连接,如果交换驱动表,整个语句的语义及结果与原SQL就不等价了;
3、如上述第2点。

使用道具 举报

回复

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

本版积分规则

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