B*****g
发帖数: 34098 | 1 seems (1=3) > 2 > 4, why? how can 1=3? 谁给说说?
Thanks
col0 is unique
DELETE FROM table_name A
WHERE A.col0> ANY (SELECT B.col0 FROM table_name B
WHERE A.col1 = B.col1 AND A.col2 = B.col2)
DELETE FROM table_name A
WHERE A.col0 > (SELECT MIN(B.col0) FROM table_name B
WHERE A.col1 = B.col1 AND A.col2 = B.col2)
DELETE FROM table_name A
WHERE EXSITS (SELECT 1 FROM table_name B
WHERE A.col1 = B.col1 AND A.col2 = B.col2 AND A.col0 > B.col0
)
DELETE FROM |
j*****n
发帖数: 1781 | 2 did not catch your point...
btw, I hate aggregated sql statements. |
B*****g
发帖数: 34098 | 3 tuning。
【在 j*****n 的大作中提到】
: did not catch your point...
: btw, I hate aggregated sql statements.
|
M*****r
发帖数: 1536 | 4 你是说执行的时间长短？
看看execution plan?
【在 B*****g 的大作中提到】
: tuning。
|
c*****d
发帖数: 6045 | 5 4肯定是最差
3应该比1快，如果col2, col3上有index估计就差不多了
在toad里执行一下，贴个执行计划看看
别直接贴在bbs上，上传附件比较清晰一些
【在 B*****g 的大作中提到】
: seems (1=3) > 2 > 4, why? how can 1=3? 谁给说说?
: Thanks
: col0 is unique
: DELETE FROM table_name A
: WHERE A.col0> ANY (SELECT B.col0 FROM table_name B
: WHERE A.col1 = B.col1 AND A.col2 = B.col2)
: DELETE FROM table_name A
: WHERE A.col0 > (SELECT MIN(B.col0) FROM table_name B
: WHERE A.col1 = B.col1 AND A.col2 = B.col2)
: DELETE FROM table_name A
|
B*****g
发帖数: 34098 | 6 我说的（1=3）> 2 就是说的execeution plan。
我古狗了一下，说1，2，4的都很；没一个建议用exists的，所以我才这么问。可惜俺在
公司不能trace，下周把execution plan贴出来。
【在 c*****d 的大作中提到】
: 4肯定是最差
: 3应该比1快，如果col2, col3上有index估计就差不多了
: 在toad里执行一下，贴个执行计划看看
: 别直接贴在bbs上，上传附件比较清晰一些
|
s******e
发帖数: 493 | 7 just my guess. aggregation call min is more expensive.
According Tom Kyte, you should avoid built-in function call as much as you
can, since usually they mean more overhead, such as latches on cahe block,
etc. |
s******e
发帖数: 493 | 8 my new found.
Just happened to read something that I believe it is a good explanation.
exists, any all, in are all operators rather than built-in functions.
Besides, the subquery using max, min which refer a column in parent query is
called "correlated query". For each row of parent query, it will be excuted
once. That should explain why using min is slower (overhead) |
