第一行VS下一行VS rownum [英] first row VS Next row VS rownum

问题描述

这3个查询返回相同的结果集，但使用2种不同的技术.使用一个相对于另一个有优势吗?计划和执行时间在相同的成本范围内.

Those 3 queries return the same result set but use 2 different technics. Is there an advantage using one over the other? The plan and the execution time are in the same cost range.

Select * 
from user_tab_columns
order  by   data_length desc,table_name, column_name  
fetch first 5 rows only  

Select * 
from user_tab_columns
order  by  data_length desc,table_name, column_name  
fetch next 5 rows only  

select * 
from (
  Select * 
  from user_tab_columns
  order  by  data_length desc,table_name, column_name  
)  
where  rownum <=5  

推荐答案

在fetch子句中使用的关键字first和next是彼此的完美替代，它们可以互换使用-声明在文档中清楚地说明.因此，您实际上只有两个查询，而不是三个. (前两个是完全相同的.)

The keywords first and next as used in the fetch clause are perfect substitutes for each other, they can be used interchangeably - this is stated clearly in the documentation. So you really only have two queries there, not three. (The first two are really identical.)

第一个查询比最后一个查询更易于编写和维护.另一方面，它仅在Oracle 12.1和更高版本中可用.在Oracle 11.2和更早版本中，唯一的选择是您的最后一个查询.

The first query is easier to write and maintain than the last query. On the other hand, it is only available in Oracle 12.1 and later versions; in Oracle 11.2 and earlier, the only option is your last query.

fetch子句更加灵活，例如，它允许您指定with ties(如果将具有rownum 4、5、6和7的行按照order by准则捆绑在一起，则可以包含5个以上的行，例如例如).

The fetch clause is more flexible, for example it allows you to specify with ties (to include more than 5 rows if rows with rownum 4, 5, 6 and 7 are tied on the order by criteria, for example).

这篇关于第一行VS下一行VS rownum的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！