我在用 Oracle Database 10g Enterprise Edition 10.2.0.4.0 64bit
我想知道,编写以下查询的最佳方法是什么?
1.与rownum
SELECT * FROM
(
SELECT ID_DONNEE_H, DATE_DONNEE
FROM DONNEE_H d
WHERE d.DATE_DONNEE > sysdate -50000
AND d.ID_SC = 38648
ORDER BY DATE_DONNEE DESC
)
WHERE rownum=1;
2.附有WITH
条款
with req as (
select d.ID_DONNEE_H, row_number() over (order by DATE_DONNEE desc) as seqnum
from DONNEE_H d
where d.DATE_DONNEE > sysdate -50000
AND d.ID_SC = 38648 )
select * from req where seqnum = 1;
3.有等级条款
select * from (select d.ID_DONNEE_H, row_number() over (order by DATE_DONNEE desc) as seqnum
from DONNEE_H d
where d.DATE_DONNEE > sysdate -50000
AND d.ID_SC = 38648) test
where seqnum = 1;
我认为2和3相似,但是最快的是1、2或3?
我认为您无法在所有情况下特别概括哪个查询是“最佳”查询。与大多数IT问题一样,答案是:“取决于”!您将需要根据其优点调查每个查询。
顺便说一句,您错过了另一种选择-假设您只关注单列,而不关注感兴趣的最高列的整行:
with sample_data as (select 10 col1, 3 col2 from dual union all
select 20 col1, 3 col2 from dual union all
select 30 col1, 1 col2 from dual union all
select 40 col1, 2 col2 from dual)
select max(col1) keep (dense_rank first order by col2 desc) col1_val_of_max_col2
from sample_data;
COL1_VAL_OF_MAX_COL2
--------------------
20
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句