subquery 或 expression 使用 IN 或 NOT IN 与 test_expression 比较后返回的所有空值都将返回 UNKNOWN。将空值与 IN 或 NOT IN 一起使用会产生意外结果。
在 IN 子句中包括数量非常多的值(数以千计)可能会消耗资源并返回错误 8623 或 8632。若要解决这一问题,请将这些项存储于某个表的 IN 列表中。
错误 8623:
查询处理器用尽了内部资源,无法生成查询计划。这种情况很少出现,只有在查询极其复杂或引用了大量表或分区时才会出现。请简化查询。如果您认为该消息的出现纯属错误,请与客户支持服务部门联系,了解详细信息。
错误 8632:
in 是把外表和内表作hash 连接,而exists是对外表作loop循环,每次loop循环再对内表进行查询。一直以来认为exists比in效率高的说法是不准确的。
如果查询的两个表大小相当,那么用in和exists差别不大。
如果两个表中一个较小,一个是大表,则子查询表大的用exists,子查询表小的用in:
例如:表A(小表),表B(大表)1:
select * from A where cc in (select cc from B)
效率低,用到了A表上cc列的索引;
select * from A where exists(select cc from B where cc=A.cc)
效率高,用到了B表上cc列的索引。
相反的2:
select * from B where cc in (select cc from A)
效率高,用到了B表上cc列的索引;
select * from B where exists(select cc from A where cc=B.cc)
效率低,用到了A表上cc列的索引。
not in 和not exists
如果查询语句使用了not in 那么内外表都进行全表扫描,没有用到索引;而not extsts 的子查询依然能用到表上的索引。所以无论那个表大,用not exists都比not in要快。
in和exists性能比较
- 外大内小的情况:
history.tb_stk_cap_chg 记录数 > 100,000,000
history.tb_stk_cap_chg_test 记录数 = 20
外大内小用in效率极低,用exists效率很高
- 外小内大的情况:
history.tb_stk_cap_chg 记录数 > 100,000,000
history.tb_stk_cap_chg_test 记录数 = 1,000,000
外小内大时使用in比exists效率更高
由此得出结论: exists适合内小外大的查询,in适合内大外小的查询
网友评论