oracle rownum最后的数据,oracle rownum问题

本帖最后由 dingjun123 于 2011-11-16 20:06 编辑

你根本不了解oracle rownum的用法，详细参考sql reference

发个很久以前写的东西

8.rownum—top-N分析

8.1 rownum基础

rownum是oracle中的一个伪列(pseudo column)，其目的就是给查询出来的结果集标注行号。可以实现查询前n行(top-n)，中间几行(middle-n)，最后几行(bottom-n)的功能。但是rownum又是一个很特殊的列，使用过程中，首先要弄清楚oracle rownum的原理，然后加以分析。

注意点：

1.rownum不可以直接在前面加上表名或别名等。其他伪列如level,rowid等一样。

如select t.rownum from table t;是错误的。

2.rownum和where在同一层查询中，where条件之后使用rownum比较，只能使用<=,,不能使用>,>=(>=1,>=0,>0和不加效果一样),=(使用=，只能是where rownum=1才可以,rownum>1不可以)。否则不返回任何数据。如果使用!=或<>,那么只是返回前n-1行，其他按照rownum工作原理推算。

如select rownum,name from emp where rownum>=5;没有结果

Select rownum,name from emp where rownum=1;返回第1行

Select rownum,name from emp where rownum !=10;//返回1-9行

Select rownum,name from emp where rownum >=1;//返回全部行,>0,>=0一样3.当rownum和order by在一个语句级别中(同一层)使用的时候.看这个查询的数据是否从索引中获取(或者根据索引先得到rowid然后定位行)的，并且获取的顺序和order by一致(注意索引可以自己desc获取的)，如果不是，那么就是先查询出来,每行标上rownum，然后order by将结果重新排序，那么rownum的顺序是乱的，并且不是按照排序后获取TOP-N结果的，而是先查询的TOP-N，再排序。比如：

SELECT id,name,ROWNUM FROM product WHERE rownum<10 ORDER BY NAME DESC;

相当于：

SELECT ID,NAME,rn

FROM (

SELECT id,name,ROWNUM rn FROM product WHERE rownum<10  --先找前9行，没有排序找

) ORDER BY NAME DESC;--最后排序

而不等价于下面：

SELECT ID,name,ROWNUM

FROM (

SELECT id,NAME FROM product   ORDER BY NAME DESC

) WHERE ROWNUM<10;

这才是我们一般需要的TOP-N查询，TOP-N查询需要有顺序。

SELECT id,name,ROWNUM  FROM product ORDER BY NAME DESC;rownum都是一样的，这里也是先从堆表里查询出给予行号，然后排序，这样出来的结果行号也是乱的

SELECT ID,NAME,ROWNUM FROM product ORDER BY ROWNUM;也是先产生rownum再排序，相当于order by 3;

有ROWNUM的不管ROWNUM在何处，都在计划里有STOP KEY，因为ROWNUM的特殊机制，导致有ROWNUM的查询无法view merge，subquery unnest，predicate push等，因为一旦查询转换发生，ROWNUM就不正确了。

如果排序的数据是从索引中查询的，这样结果有序。这取决于执行计划，执行计划又和oracle优化器相关。

如：

create table emp(id number,name varchar2(10));

select rownum,id from emp where id<200 order by id desc;

查询结果是：

我们可以看到，这里的rownum是无序的，下面看下执行计划：

执行计划显示，查询的数据不是从索引中获取的。也就是先查询数据，然后给数据加上rownum，之后排序(其实是因为索引消除了排序，这个在优化上经常会用到)。

下面建立索引：create index i_emp_id on emp(id);

再看执行计划：

执行计划显示，在第2步的时候走了索引，那么是先排序好了，再分配rownum，所以执行：select rownum,id from emp where id<200 order by id desc;

结果是有序的。

具体情况分析执行计划和oracle优化器对执行计划的影响。

GROUP BY与HAVING和ORDER BY类似

2007-11-21

Is rownum=1 the first row returned? well, it depends :select rownum, enamefrom emporder by ename;ROWNUM ENAME---------- ----------11 ADAMS2 ALLEN6 BLAKE7 CLARK13 FORD12 JAMES4 JONES9 KING5 MARTIN14 MILLER8 SCOTT1 SMITH10 TURNER3 WARD

The ROWNUM is evaluated before the order byselect rownumfrom empgroup by rownumhaving rownum between 3 and 6;ROWNUM----------6534

the rownum is selected before the GROUP BY and before the HAVING.selectrownum,sal,median(sal) over ()from emp;ROWNUM        SAL MEDIAN(SAL)OVER()---------- ---------- -----------------1        800              155012        950              155011       1100              15503       1250              15505       1250              155014       1300              155010       1500              15502       1600              15507       2450              15506       2850              15504       2975              15508       3000              155013       3000              15509       5000              1550

The optimiser may chose to resort the result, here as a WINDOW SORT operation for the MEDIAN analytic function

总结：看oracle官方介绍：

If an ORDER BY clause follows ROWNUM in the same query, then the rows will be reordered by the ORDER BY clause.The results can vary depending on the way the rows are accessed. For example,if the ORDER BY clause causes Oracle to use an index to access the data, then Oracle may retrieve the rows in a different order than without the index.Therefore, the following statement will not have the same effect as the preceding example:

SELECT * FROM employees WHERE ROWNUM < 11 ORDER BY last_name;

建议如果需要对查询的数据排序，并且对排序数据给予rownum，使用子查询，可以保证rownum的顺序。如上面的查询应该是：

select rownum,id from (

select id from emp where id<200 order by id desc

);

分析函数row_number可以和order by在同一层，并且oracle知道先执行order by然后在给予行分配rownum，这是与rownum伪列不同的。在分析函数章节给予详细讲解，这里不做进一步阐述。

另外注意,order by键值有相等的，rownum不保证排列顺序，rownum总是递增，可以使用order by键值,rowid,如果相同的键值同排名，用分析函数中的dense_rank或rank。

Rownum原理：

1 Oracle executes your query.

1.执行查询操作

2 Oracle fetches the first row and calls it row number 1.

2.将第一行的row num置为1

3 Have we gotten past row number meets the criteria? If no, then Oracle discards the row, If yes, then Oracle return the row.

3.将得到的行的row num与条件相比较，如果不匹配，则抛弃行，如果匹配，则返回行

4 Oracle fetches the next row and advances the row number (to 2, and then to 3, and then to 4, and so forth).

4.oracle获取下一行，然后将rownum增1

5 Go to step 3.

5.返回第3步

从这个原理可以知道，select rownum,name from emp where rownum>5;不返回行，因为首先执行查询select name from emp，将第1行的rownum标为1，然后看where条件，为false，则抛弃行，执行第2行，还是rownum标为1，看where条件还为false，所以永远是false，rownum不改变，所有的行都被抛弃，所以没有结果。

4.rownum具有固定结果集的作用，这是rownum非常重要的一个特性。比如有的内联视图中没有使用rownum,那么优化器可以使用谓词推进推入内联视图中(控制条件的执行顺序)，但是如果使用rownum,内联视图的结果集就固定了(可以把rownum放在select、where等结构中都可以)，外层谓词无法推入到内联视图中(使用no_merge,no_push_pred对这种情况经常无效)，下面看一个例子：