sql 初级问题。 呼唤达人。

黑山老妖

Update和index都有很多办法optimize的。

澳贼

原帖由 黑山老妖 于 2010-10-21 11:19 发表
Update和index都有很多办法optimize的。

请妖精举例说明

北风

join和in基本是一样的，exists有点区别，但就像月亮说的
不过一般来说exists不会比in快的

当然你在in后面用了distinct，这会有点降速，不过显然这个distinct不是必要的

原帖由 澳贼 于 21/10/2010 09:20 发表
select A.account,
case
when account in (select distinct account from B) then 'yes'
else 'No' end as C
from A

SELECT account,
CASE WHEN (EXISTS (SELECT * FROM B WHERE account=B.account)) THEN 'yes'
E ...

乱码

sql server 中：

1.exits performance最好，比in/join好很多，它不evaluate select中的东西，只看条件，只要有一条符合，就return true，不对整个table scan.exists有的时候跟in差不多，但肯定要比join好。
2.在其中一个ｔａｂｌｅ中加ｆｌａｇ的ｍｏｄｅｌｉｎｇ不是ｇｏｏｄ　ｐｒａｃｔｉｃｅ，它会有潜在的integration issue，如果query要traverse很多中间table,这种作法可以理解，但如果是直接reference的关系，这么做就不推荐了。
3.比较几个query的performance,可以把它们做成一个ｂａｔｃｈ，然后点ｅｘｅｃｕｔｉｏｎ　ｐｌａｎ的ｉｃｏｎ，它给你每个ｓｔａｔｅｍｅｎｔ运行时间的比例（out of 100%)，最优的是最小的那个。

[ 本帖最后由 乱码 于 2010-10-21 11:52 编辑 ]

北风

第一点不对

原帖由 乱码 于 21/10/2010 10:49 发表
sql server 中：

1.exits performance最好，比in/join好很多，它不evaluate select中的东西，只看条件，只要有一条符合，就return true，不对整个table scan.
2.在其中一个ｔａｂｌｅ中加ｆｌａｇ的ｍｏｄｅｌｉｎｇ不是ｇｏｏｄ　ｐｒａｃｔｉｃｅ，它会有潜在的integration issue，如果query要t ...

黑山老妖

原帖由 澳贼 于 21/10/2010 11:34 发表
请妖精举例说明

Oracle下面的optimization通常都是DBA的事情，和你没有关系啦。
简单来说create table和index的时候就要考虑好怎么样做比较合适。
如果update/insert很多就多留点pctfree，多点maxtrans。index可以partition，可以用bitmap或是其他合适的index等等。
取消logging也会有很大帮助，但是万一database crash就惨了。

乱码

原帖由 北风 于 2010-10-21 11:52 发表
第一点不对

test it or google it

stevenbian

select a.account,a.accountName,nvl(status,'no')
  from a a,(select account,'yes' status from b) b
where a.account = b.account(+)

外联结和nvl好像sqlserver也能用，不过有些名字不同

北风

很多时候exists只是nested loops(left semi join)是会scan full table的

原帖由 乱码 于 21/10/2010 10:49 发表
sql server 中：

1.exits performance最好，比in/join好很多，它不evaluate select中的东西，只看条件，只要有一条符合，就return true，不对整个table scan.exists有的时候跟in差不多，但肯定要比join好。
2.在其中一个ｔａｂｌｅ中加ｆｌａｇ的ｍｏｄｅｌｉｎｇ不是ｇｏｏｄ ...

北风

我测试过的
不光是测试，实际使用也碰到对比过的
很多人只是想当然的认为exists只是找到一个就返回。。。。。
这只是误解罢了

原帖由 乱码 于 21/10/2010 10:54 发表


test it or google it

[ 本帖最后由 北风 于 2010-10-21 11:00 编辑 ]

rumcoke

left join两个巨大的表确实不怎么样 他的搜索机制是拿出一个对比所有
可能我经常用的情况是一个大的join一个小的 有些想当然的推荐给你了

乱码

原帖由 北风 于 2010-10-21 11:56 发表
很多时候exists只是nested loops(left semi join)是会scan full table的

你说的是not exists吧？

这是其中的一篇文章，你可以手动做一下实验，看看它的ｅｘｅｃｕｔｉｏｎ　ｐｌａｎ．

“For exists and in, SQL Server can return TRUE as soon as a single row matches”

http://www.lcard.ru/~nail/sybase/perf/18295.htm

北风

是exists
我做过实验的，而且用了数据量下差很大的不同的表，不同的index下，很多情况你在excution plan的时候exists的cost会和in差不多，甚至还低一点点，但是一运行速度马上就看出来差距了。
而且你excution plan的时候就会发现exists很多是nested loops，把表scan一遍，怎么可能快呢

你可以做一些实验。
这里也有一篇文章
http://explainextended.com/2009/06/16/in-vs-join-vs-exists/

原帖由 乱码 于 21/10/2010 11:06 发表


你说的是not exists吧？

这是其中的一篇文章，你可以手动做一下实验，看看它的ｅｘｅｃｕｔｉｏｎ　ｐｌａｎ．

“For exists and in, SQL Server can return TRUE as soon as a single row matches”

http://www.lcard.ru/~nail/sybase/perf/18295 ...

乱码

原帖由 北风 于 2010-10-21 11:58 发表
我测试过的
不光是测试，实际使用也碰到对比过的
很多人只是想当然的认为exists只是找到一个就返回。。。。。
这只是误解罢了

很有可能你的table 很小，一次i/o就可以读出所有的数据，所以engine用table scan,你尝试一下用大点的ｔａｂｌｅ，肯定结果不同。

我手头的ｄｂ没有权限作　show plan,不能给你我测试的结果，但exists一般来说不scan table这是ｃｏｍｍｕｎｉｔｙ的共识，如果你有很solid的证据它一定ｓｃａｎ　ｔａｂｌｅ，你应该报告ｍｓ　ｓｑｌ　ｓｅｖｅｒ　ｔｅａｍ，这是一个很大的ｂｕｇ，他们应该ｆｉｘ的。

jands

真是一篇好的學習帖

澳贼

原帖由 黑山老妖 于 2010-10-21 11:52 发表

Oracle下面的optimization通常都是DBA的事情，和你没有关系啦。
简单来说create table和index的时候就要考虑好怎么样做比较合适。
如果update/insert很多就多留点pctfree，多点maxtrans。index可以partition，可以用bitma ...

长知识！

典

Just done a test
It looks like exist is better than left join ( for my test only)
Tables have few million rows
Both table indexed on visit_no

select v.visit_no,v.outlet_id,
case isnull(lv.visit_no,0) when 0 then 'NO' else 'YES' end c
from v left join lv on(lv.visit_no=v.visit_no) where v.last_visit_date>'20090505'

-------CPU time = 1281 ms,  elapsed time = 16083 ms.

select v.visit_no,v.outlet_id, CASE WHEN (EXISTS (SELECT visit_no FROM  lv
WHERE lv.visit_no=v.visit_no)) THEN 'yes' ELSE 'no' End C
from  v where v.last_visit_date>'20090505'
-------CPU time = 1078 ms,  elapsed time = 8870 ms.

-----Scans and reads are exactly same

[ 本帖最后由 典 于 2010-10-21 11:41 编辑 ]

月亮

这个跟table大小以及有没有index很有关系的

北风

共识？真的没有，只是误解罢了
我也和别人讨论过的，他们有的也已经知道的
可能你没有做过比较吧，其实实践中的例子很多的，你要是做过会能留意到的
我用的table有大有小，2G以上的table也有
不是说exists就是scan table
它是做nested loops，这个你懂的，它可大可小的，尤其是两个表都不小的时候，你nest就很慢了
而in就是hash match

原帖由 乱码 于 21/10/2010 11:22 发表


很有可能你的table 很小，一次i/o就可以读出所有的数据，所以engine用table scan,你尝试一下用大点的ｔａｂｌｅ，肯定结果不同。

我手头的ｄｂ没有权限作　show plan,不能给你我测试的结果，但exists一般来说不scan table这是ｃｏｍｍｕｎｉｔｙ的共识，如果 ...

澳贼

原帖由 典 于 2010-10-21 12:39 发表
Just done a test
It looks like exist is better than left join ( for my test only)
Tables have few million rows
Both table indexed on visit_no

select v.visit_no,v.outlet_id,
case isnull(lv.visit_no,0) ...

数据说话，顶！！ 不知道nested exist 会如何？

北风

这个和我们讨论的好像不是一个
我们讨论的是in和exists
left join是很恐怖的，不能乱来

可不可以改成
select * from v where v.visit_no in (select visit_no from lv) and v.last_visit_date > '20090505'
比较
select * from v where exists (select 1 from lv where lv.visit_no = v.visit_no) and v.last_visit_date > '20090505'

原帖由 典 于 21/10/2010 11:39 发表
Just done a test
It looks like exist is better than left join ( for my test only)
Tables have few million rows

select v.visit_no,v.outlet_id,
case isnull(lv.visit_no,0) when 0 then 'NO' else 'YES' end c
from v left join lv on(lv.visit_no=v.visit_no) where v.last_visit_date>'20090505'

-------CPU time = 1281 ms,  elapsed time = 16083 ms.

select v.visit_no,v.outlet_id, CASE WHEN (EXISTS (SELECT visit_no FROM  lv
WHERE lv.visit_no=v.visit_no)) THEN 'yes' ELSE 'no' End C
from  v where v.last_visit_date>'20090505'
-------CPU time = 1078 ms,  elapsed time = 8870 ms.

-----Scans and reads are exactly same

典

Changed the condition to search more data (one year more)
the result is:
use left join: CPU time = 2109 ms,  elapsed time = 34873 ms.
use exist: CPU time = 1875 ms,  elapsed time = 19685 ms.
Use In:      CPU time = 1672 ms, elapsed time = 19812 ms.

scans / reads are exactly same (so don't worry about index)
Now in is very much similar to exist (for my test only)

[ 本帖最后由 典 于 2010-10-21 12:00 编辑 ]

典

原帖由 北风 于 2010-10-21 11:48 发表
这个和我们讨论的好像不是一个
我们讨论的是in和exists
left join是很恐怖的，不能乱来

可不可以改成
select * from v where v.visit_no in (select visit_no from lv) and v.last_visit_date > '20090505'
比较
sele ...

Ok I will have a try

江苏小伙子

Index will only help when you query a small percentage of data out of a table, like 10%. In this case, full scan will be preferred by optimizer.

Which SQL will run faster depends on a couple of factors NOT just by the look itself: database(orace, sql server, teradata), statistics, table access method(full table scan/index scan), join method(NL, Sort-Merge, Hash).  Explain plan will tell you more.

You will need to consider scalability as well as one-off performance. Cache should be cleared when running two SQLs to compare.

乱码

原帖由 北风 于 2010-10-21 12:40 发表
共识？真的没有，只是误解罢了
我也和别人讨论过的，他们有的也已经知道的
可能你没有做过比较吧，其实实践中的例子很多的，你要是做过会能留意到的
我用的table有大有小，2G以上的table也有
不是说exists就是scan table
它是做n ...

谢谢分享，我的确原来没注意到这个问题．　你是说ｅｘｉｓｔｓ如果用merge/hash join就没有ｓｃａｎ　ｔａｂｌｅ这个问题了么？什么样的ｎｅｓｔｅｄ　ｌｏｏｐ？indexed还是没有index的？

典，你能测一下么？把那些ｗｈｅｒｅ的ｃｌｕｍｎ　ｉｎｄｅｘ一下，谢谢！！如果是很关键的数据和ｐｒｏｄ相关，就算了，别给你添麻烦。

[ 本帖最后由 乱码 于 2010-10-21 13:03 编辑 ]

典

For my test,
in is same as exist
left join is bad

visited_no indexed in both tables
I have ensured thire scans / reads are same, so no index issue here
and nobody else is using the database server,

but for hash join....I didn't test

[ 本帖最后由 典 于 2010-10-21 12:05 编辑 ]

LoveAu

EXISTS  scan and stop at the first record that matches the EXISTS criterion. If the recorde at the end of table, the performance will be degraded.

北风

一般来说应该不是nested loop而是就简单的hash match就像in 一样（这也是他们应该差不多的原因）
但是有些时候，写的不好exists就变成nested loop了，那就糟糕了
我手上没有，但是见到过几个

[ 本帖最后由 北风 于 2010-10-21 12:06 编辑 ]

乱码

原帖由 典 于 2010-10-21 13:03 发表
For my test,
in is same as exist
left join is bad

visited_no indexed in both tables
I have ensured thire scans / reads are same, so no index issue here
and nobody else is using the database server,

...

u can't control which join the engine uses. but if u find an hash join in execution plan, it's one of best candidates to index it.

乱码

原帖由 北风 于 2010-10-21 13:04 发表
一般来说应该不是nested loop而是就简单的hash match就像in 一样（这也是他们应该差不多的原因）
但是有些时候，写的不好exists就变成nested loop了，那就糟糕了
我手上没有，但是见到过几个 ...

nested loop join最常见，merge/hash到不怎么多见。