性能:rank()与子查询.子查询有较低的成本吗? [英] Performance: rank() vs sub-query. Sub query have lower cost?
问题描述
受这个问题的启发，我决定测试
Inspired by this question I decided to test the rank() function, trying to see if sub query's are less efficient than rank. So I created a table:
create table teste_rank ( codigo number(7), data_mov date, valor number(14,2) );
alter table teste_rank add constraint tst_rnk_pk primary key ( codigo, data_mov );
并插入了一些记录...
and inserted some records...
declare
vdata date;
begin
dbms_random.initialize(120401);
vdata := to_date('04011997','DDMMYYYY');
for reg in 1 .. 465 loop
vdata := to_date('04011997','DDMMYYYY');
while vdata <= trunc(sysdate) loop
insert into teste_rank
(codigo, data_mov, valor)
values
(reg, vdata, dbms_random.value(1,150000));
vdata := vdata + 2;
end loop;
commit;
end loop;
end;
/
然后测试了两个查询:
select *
from teste_rank r
where r.data_mov = ( select max(data_mov)
from teste_rank
where data_mov <= trunc(sysdate)
and codigo = 1 )
and r.codigo = 1;
select *
from ( select rank() over ( partition by codigo order by data_mov desc ) rn, t.*
from teste_rank t
where codigo = 1
and data_mov <= trunc(sysdate) ) r
where r.rn = 1;
如您所见，子查询的成本低于rank().这是正确的吗?我在那儿想念东西吗?
As you can see, the cost of sub query is lower than rank(). Is this right? Am I missing something there?
PS:还对表中的完整查询和低成本子查询进行了测试.
PS: Tested also with a full query in the table and still sub query with the low cost.
编辑
我生成了两个查询的tkprof(跟踪一个，关闭数据库，启动并跟踪第二个).
I generated a tkprof of the two query's (traced one, shutdown the database, startup and traced the second).
对于子查询
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.02 3 5 0 0
Execute 1 0.00 0.00 0 3 0 0
Fetch 2 0.00 0.00 1 4 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.02 4 12 0 1
对于rank()
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.02 3 3 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 9 19 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.03 12 22 0 1
我能得出结论，子查询的效率不会总是低于排名吗?何时显示等级而不是子查询?
推荐答案
我不太确定您的问题是什么.是的，根据这两个执行计划，在这种情况下，子查询方法的预期成本较低.似乎并不太令人惊讶，因为它可以使用索引非常快速地找到您感兴趣的确切行.在这种情况下，子查询仅需要非常快速地扫描PK索引.如果子查询所涉及的列不属于索引，则情况可能会有所不同.
I'm not really sure what your question is. Yes, according to these two execution plans, in this case, the subquery method has a lower expected cost. Doesn't seem too surprising, since it can use the index to very quickly locate the exact row you're interested in. Specifically in this case, the subquery only has to do a very quick scan of the PK index. The situation might be different if the subquery involved columns that weren't part of the index.
使用rank()的查询必须获取所有匹配的行并对其进行排名.我不认为优化程序具有任何短路逻辑来识别这是一个top-n查询，因此即使您真正关心的只是排名最高的行，也避免了完整的排序.
The query using rank() has to get all the matching rows and rank them. I don't believe that the optimizer has any short-circuit logic to recognize that this is a top-n query and therefore avoid a full sort, even though all you really care about is the top-ranked row.
您也可以尝试使用此表单，优化程序应将此表单识别为top-n查询.我希望在您的情况下，只需要对索引进行一次范围扫描，然后再进行表访问即可.
You might also try this form, which the optimizer should recognize as a top-n query. I would expect in your case that it would require only a single range scan on the index followed by a table access.
select *
from (select *
from teste_rank r
where data_mov <= trunc(sysdate)
and codigo = 1
order by data_mov desc)
where rownum=1;
这篇关于性能:rank()与子查询.子查询有较低的成本吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
