sql优化如何在内部工作? [英] How does sql optimization work internally?
问题描述
我之前的问题:
在上一个问题中，我正在寻找不同的方法来查找ID号最高的记录的日期.以下是一些提供的解决方案，以及通过说明计划计算出的成本".
select date from table where id in (
select max(id) from table)
的费用为8
select date from table where rownum < 2 order by id desc;
的费用为5
select date from (select date from table order by id desc) where rownum < 2;
的成本也为5
with ranked_table as (select rownum as rn, date from table order by id desc)
select date from ranked_table where rn = 1;
的费用为906665
SELECT t1.date
FROM table t1
LEFT OUTER JOIN table t2
ON t1.id < t2.id
WHERE t2.id IS NULL;
花费1438619
很显然，id上的索引正在执行其工作.但是我想知道，在什么情况下后两者在至少更好的情况下会表现得同样好?我想了解这样做的好处.
这是在Oracle中完成的.可以讨论所有品种，但请说出您的答案适用.
如果您希望使用可移植性最强的SQL来支持多种其他RDBMS品牌(即并非所有品牌都支持rownum)，请使用解决方案#1. :
select date from table where id in (select max(id) from table);
如果您想要最有效的Oracle解决方案，请使用解决方案#3:
select date from (select date from table order by id desc) where rownum < 2;
请注意，解决方案2并非总是能给出正确的答案，因为它会返回之前的前"两行，并按id对其进行排序.如果碰巧返回具有最高id值的行，则只是偶然.
select date from table where rownum < 2 order by id desc;
关于代价如此高昂的更复杂的查询#4和#5，我同意我不建议将它们用于诸如提取具有最高id的行之类的简单任务.但是，了解如何使用子查询因子分解和自联接对于解决其他更复杂的查询类型非常有用，在这种情况下，简单的解决方案根本无法胜任.
示例:给定带主题的论坛评论的层次结构，显示回复最直接的最热门"评论.
My previous question:
Date of max id: sql/oracle optimization
In my previous question, I was finding different ways of finding the date of the record with the highest id number. Below are several of the offered solutions, and their 'cost' as calculated by explain plan.
select date from table where id in (
select max(id) from table)
has a cost of 8
select date from table where rownum < 2 order by id desc;
has a cost of 5
select date from (select date from table order by id desc) where rownum < 2;
also has a cost of 5
with ranked_table as (select rownum as rn, date from table order by id desc)
select date from ranked_table where rn = 1;
has a cost of 906665
SELECT t1.date
FROM table t1
LEFT OUTER JOIN table t2
ON t1.id < t2.id
WHERE t2.id IS NULL;
has a cost of 1438619
Obviously the index on id is doing its job. But I was wondering, in what cases would the last two perform at least as well, if not better? I want to understand the benefits of doing it that way.
This was done in Oracle. All varieties can be discussed, but kindly say what your answer applies to.
Use solution #1 if you want the most portable SQL that will work on a wide variety of other brands of RDBMS (i.e. not all brands support rownum):
select date from table where id in (select max(id) from table);
Use solution #3 if you want the most efficient solution for Oracle:
select date from (select date from table order by id desc) where rownum < 2;
Note that solution #2 doesn't always give the right answer, because it returns the "first" two rows before it has sorted them by id. If this happens to return the rows with the highest id values, it's only by coincidence.
select date from table where rownum < 2 order by id desc;
Regarding the more complex queries #4 and #5 that give such a high cost, I agree I wouldn't recommend using them for such a simple task as fetching the row with the highest id. But understanding how to use subquery factoring and self-joins can be useful for solving other more complex types of queries, where the simple solutions simply don't do the job.
Example: given a hierarchy of threaded forum comments, show the "hottest" comments with the most direct replies.
这篇关于sql优化如何在内部工作?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
