求解释SQL Union VS OR

典

有段SQL 代码大约是

select xxxx from yyy
where conditionA or conditionB,

运行时间大约10秒
---------------------------------------------
如果把or 改为union

select xxxx from yyy
where conditionA
union
select xxxx from yyy
where conditionB,
运行时间大约1秒
------------------------------------------
为什么会这样？？

红烧鸡翅

or 用的时间是 a*b
union用的时间是 a+b

典

回复atransformer
这是真的，来自我们的产品环境

我在tunning一段别人写的SQL代码, 原来要50秒，凭直觉不应该，
改写后依然要10秒，觉得还是不对，

然后把or condition分开运行发现很快，才想到用union, 结果不到1秒

网上有人在问同样的问题，好像目前没有答案。还有人坚持不相信有这回事

[ 本帖最后由 典 于 2012-3-14 13:58 编辑 ]

wil

上execution plan就好解释了

杨白劳

1) I believe the sample using "OR" might prevent the use of index. It is hard to say without the actual SQL and table design. How about putting both conditions into brackets?

SELECT xxx FROM yyy Where (Cond 1) OR (Cond 2)

2) If the table is large, it is quick to scan the index twice then scanning the entire table once.

3) Usually it is not recommended to use UNION where you can avoid.

典

我在1楼说的是简化后的情况，实际的Query有点点复杂，涉及到很多表和sub-query

看了execution plan, 有个表发现分开运行时(union情况下）index seek,
                                   用or合并时， 变成了index scan,
                                
                                 也许这是原因所在。

这应该是特例，用其它一些query来检验，没有发现union 比 or 快

fyang1024

原帖由 wil 于 2012-3-14 15:17 发表
上execution plan就好解释了

用Oracle 10测了一下, Union比Or慢

bffbffbff

原帖由 典 于 2012-3-14 15:55 发表
我在1楼说的是简化后的情况，实际的Query有点点复杂，涉及到很多表和sub-query

看了execution plan, 有个表发现分开运行时(union情况下）index seek,
                                   用or合并时， 变成了index scan,
...

不算是很特的例， 起码我遇见好几次了， 我觉得 index scan v index seek 的说法有一定道理

marklan001

那个union不用 Select Distinct？

bffbffbff

原帖由 marklan001 于 2012-3-14 18:29 发表
那个union不用 Select Distinct？

有 union 就不用 distinct 了

atransformer

原帖由 典 于 2012-3-14 14:57 发表
回复atransformer
这是真的，来自我们的产品环境

我在tunning一段别人写的SQL代码, 原来要50秒，凭直觉不应该，
改写后依然要10秒，觉得还是不对，

然后把or condition分开运行发现很快，才想到用union, 结果不到1秒

网上有人在问同样的问题，好像目前没有答案。还有人坚持不相信有这回事

你的条件列有没有index?

Fernando

还是拿具体的table，index，statistics，放到具体的数据库版本里面说吧

huaxianz

execution plan说明了一切。

wendy

did you flash out the buffer cache before you run the second statement?

NNX

原帖由 wendy 于 2012-3-14 21:29 发表
did you flash out the buffer cache before you run the second statement?

同感， 可能只是cache的原因

huaxianz

原帖由 wendy 于 2012-3-14 21:29 发表
did you flash out the buffer cache before you run the second statement?

我猜楼主是在production环境下，cache flushing可能不适合。

我脚着index/table scan还是index seek多数取决于selectivity.如果condition A和condition B都分别低于threshold，query optimizer当然选择bookmark lookup，这是个union的情形；但如果condition A + condition B超过了那个threshold，query optimizer则认为table/index scan的cost更低，这个就是or的情形。当然都是理论，实际情况就像前面有人说过的，离不开具体的physical characteristics of table, index, statistics,甚至是server runtime尤其是severity of memory pressure. memory pressure是决定前面谈到的threshold因素之一。

还是那句话，execution plan talks.

典

1) Cache应该没问题, 两个query反复运行，连续运行多次,结果都一样
2) 确实两个query的exec plan 完全不一样, 结构都变了，连比较都不太好比，
同意大家的意见， exec plan说明一切, 明天看能不能贴出来给大家研究
包括io / time statistics
3) 在另外一台SQL 2008服务器上测试，几乎是产品数据库的镜像，则没有这个问题。
4) 准备从逻辑上从新设计这段程序，有把握进一步降低到0.1秒左右，对query本身我准备放弃优化了

典

请问你碰到这种情况后如何处理? 用union 代?

waynepublic

这个情况我们也碰到几回，和clean buffer没有关系。在这种情况下，or会导致sql server无法用一个index完成query,于是认为一个table scan或index scan优于两个index seek。sql server在选择index plan时不是每次都是明智的。楼主可以set io statistics on来比较一下各读了多少page。

典

有没有简单的解决办法？
我试了forceseek 来强制使用seek
没有效果，也许测试的table不对

Janet

condition A or B 是有Index过滤吗？

如果没有index 的话，应该不会差这么多吧

bffbffbff

原帖由 典 于 2012-3-14 22:26 发表
请问你碰到这种情况后如何处理? 用union 代?

我也没啥好的方法，也没怎么研究，union可以解决的就用union了，然后就是写query的时候用or的时候一般都比较小心，尤其是大点的query。
目前观察到的（当然样本有限可能结论不对）union 比 or 差距明显（和你说的差不多是10几秒甚至几十秒 v 几秒的区别）的2个主要特征：

1. 和花仙子说的类似，selectivity很有关系，如果or左右的两个condition中的一个以上会返回非常多的数据，一般会有问题 极端的例子： col1 is not null 之类
2. 问题一般出现在or的两个condition都是直接对1个或多个表进行seek/scan。 如果有一个condition是个variable比较（@isEnabled=1)， 只有另一个condition在做查询, 一般不会有问题

典

使用Or的时候

典

使用Union的时候

典

可能真是2楼说的，使用OR的时候， work table的scan次数爆增。

Plan则不太好比，因为结构完全变了。

不过这个query设计得很不好，虽然改了一次提高了几十倍的速度，但是依然不合理，准备有空的时候推倒重来。

huaxianz

我的直觉是第一张plan的精华在图的左半部。。。什么导致index spool?是不是下面分支处在另外一个Nested Loops的outer side?比如上面分支返回100行的话，则下边rowset要被rewound100回。。。index/table spool不需要重新读原表，数据被暂时的(within query scope) dump到tempdb上，所以rewind对tempdb有很大的negative impact. 第二张图的hash match则不存在这个问题。所以问题的焦点从index scan转移到了index spool。

huaxianz

向网警致敬~

porcorosso

个人觉得OR比较可取，UNION是当两个queries的source不一样时才用的

[ 本帖最后由 porcorosso 于 2012-3-15 16:00 编辑 ]

remonyan

我说两点看法：
1。你第三点的现象说明你的index/ststistics己经outdate了，估计没做常规的maintenance
2。从23#和24#公布的I/O stats来看，没有physical reads, 证明你的测试都是在cache里进行的，这样似乎对实际prod issue没啥参考价值。除非你的库很小而RAM极大且系统不忙。
欢迎探讨

典

原帖由 remonyan 于 2012-3-20 20:38 发表
我说两点看法：
1。你第三点的现象说明你的index/ststistics己经outdate了，估计没做常规的maintenance
2。从23#和24#公布的I/O stats来看，没有physical reads, 证明你的测试都是在cache里进行的，这样似乎对实际prod issue没啥参考价值。除非你的库很小而RAM极大且系统不忙。
欢迎探讨

你很仔细..确实数据库不大200G左右， RAM很大20G，系统不会很忙只有几百user,且只用于transaction,所以经常情况下没有physical read...