rownum—top-N分析

【技术开发 技术文章】 Rownum是oracle中的一个伪列(pseudo column)，其目的就是给查询的行标注行号。可以实现查询前n行，中间几行，最后几行的功能(根据业务功能定义行的排序)。但是rownum又是一个很特殊的列，使用过程中，首先要弄清楚oracle的原理，然后加以分析。 注意点： 1.rownum不可以直接在前面加上表名或别名等。 如 select t.rownum from table t;是错误的。 2.rownum 和where 在同一层查询中，where 条件之后使用rownum 比较，只能使用<=,,不能使用>,>=(>=1是可以的,和不加效果一样),=(使用=，只能是where 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;//返回全部行 3.当rownum 和order by 在一个语句级别中(同一层)使用的时候.看这个查询的数据是否从索引中获取(或者根据索引先得到rowid然后定位行)的，如果不是，那么就是先查询出来,每行标上rownum，然后order by 将结果重新排序，那么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优化器对执行计划的影响。 总结：看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 without the index. Therefore, the following statement will not have the same effect as the preceding example: 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 的顺序。 分析函数row_number可以和order by在同一层，并且oracle 知道先执行order by然后在给予行分配rownum，这是与rownum伪列不同的。在分析函数章节给予详细讲解，这里不做进一步阐述。 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不改变，所有的行都被抛弃，所以没有结果。 基于以上的问题，那么使用rownum的常用查询结构有： 1.简单的加上行号 选择员工，显示姓名,结果集的行号 select rownum ,last_name from s_emp; select rownum ,last_name from s_emp where rownum <= 10; select rownum ,last_name from s_emp where rownum = 1; select rownum ,last_name from s_emp where rownum >= 1; select rownum ,last_name from s_emp where rownum != 10; (只选择到1-9,后面的都不满足，抛弃行) 下面的不正确 select rownum ,last_name from s_emp where rownum = 10; select rownum ,last_name from s_emp where rownum >= 10; 2.获取前n行 选择员工，显示姓名,行号，按照姓名排序 select rownum,last_name from s_emp order by last_name;--不正确 如果要排序，那么需要在内层查询中排序好，然后外层查询引入rownum（两重嵌套查询） select rownum,last_name from (select last_name from s_emp order by last_name); 选择前10行，可以直接在两重嵌套的外层查询中引入rownum，并且where rownum<= select rownum,last_name from (select last_name from s_emp order by last_name) where rownum<=10; 3.获取中间行数据 选择员工姓名，显示行号，按照姓名排序,显示第5 条记录到第10条记录 select rn,last_name from --最外层用where条件判断 (select rownum rn,last_name --中间层查询加上行号 from ( select last_name from s_emp order by last_name --底层查询排序 ) ) where rn between 5 and 10; 上面是使用三重嵌套，如果不需要数据排序，那么可以使用两重嵌套。 select rn,last_name from ( select last_name,rownum rn from s_emp order by rn ) where rn between 5 and 10; 也可以在中间层查询中加入最大的行号判断，<=,最外层用>=，上面的查询可以用下面的替换： select rn,last_name from (select rownum rn,last_name from ( select last_name from s_emp order by last_name ) where rownum<=10 --中间层查询把要查询的最大行号最过滤 ) where rn>=5; 4.取最大值的行 查找出薪资差距最大的部门，显示名称 select name from s_dept where id = (select dept_id from (select dept_id, (max(salary) - min(salary)) salary --内层查询将薪水差计算作为别名，排序 from s_emp group by dept_id order by salary desc) where rownum = 1); --外层查询取得第一行数据 其他的最小，后几行等都可以通过修改排序获得。
链接:http://doc.chinaunix.net/oracle/200910/534160.shtml

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26532163/viewspace-716868/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26532163/viewspace-716868/