如果我们指定rownum子句，Oracle将如何优化记录集 [英] How will Oracle optimise a record set if we specify a rownum clause

问题描述

如果我说:

select * from table order by col1 where rownum < 100

如果表中有1000万条记录，Oracle会否带走全部1000万条记录，对它进行排序，然后向我显示前10条记录?还是有一种方法可以对其进行优化?

If the table has 10 million records, will Oracle bring all 10 million, sort it and then show me the first 10? Or is there a way it will optimise it?

推荐答案

如果您这样做

select * from table order by col1 where rownum < 100

然后，由于WHERE子句出现在ORDER BY之前，Oracle将引发错误.

then Oracle will throw an error as the WHERE clause comes before the ORDER BY.

如果您这样做

select * from table where rownum < 100 order by col1 

然后，当WHERE子句出现在ORDER BY之前，Oracle将随机返回99条记录.

then Oracle will return a random 99 records as the WHERE clause comes before the ORDER BY.

如果要返回按列排序的前100条记录，则必须将排序依据放在子选择中.

If you want to return a the first 100 records, ordered by a column, you must put the order by in a sub-select.

select * 
  from ( select * from table order by col1 )
 where rownum <= 100

Oracle将进行排序，否则它将如何知道您想要的记录?但是，由于ROWNUM，它将带有停止键. Oracle不会实际上对整个结果集进行排序，因为一些优化是在后台进行的，但这是您可以假设发生的事情.

Oracle will do the sort, how else will it know the records you want? However, it will be a sort with a stopkey because of the ROWNUM. Oracle doesn't actually sort the entire result set, as some optimisation goes on under the hood, but this is what you can assume takes place.

请参阅汤姆·凯特(Tom Kyte)的这篇文章.

这篇关于如果我们指定rownum子句，Oracle将如何优化记录集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！