ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Rownum与Order by(一)

Rownum与Order by(一)

原创 Linux操作系统 作者:realkid4 时间:2011-02-13 21:59:11 0 删除 编辑

rownum是数据表的一个虚列,是随着select获取到的数据集合而依次生成的数据项目。借助rownum,很多复杂的SQL需求都得以解决。但是,rownum绝不是一个数据列,使用它的时候一定要注意rownum的生成原理和特性。本篇就从rownum与order by结合,谈使用rownum的一个注意点。

 

 

简单的说,rownum是在使用select查询的时候,自动生成的一串顺序号。在每返回一条数据结果的时候,rownum就自动加一。rownum可以使用在select后的列表中,也可以使用在where条件后。Oracle中没有top n命令,通常都是使用rownum来控制返回数据的条数。

 

 

SQL> select count(*) from emp;

 

  COUNT(*)

----------

        14

 

SQL> select * from emp where rownum<3; //使用rownum来限制条数

 

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

----- ---------- --------- ----- ----------- --------- --------- ------

 7369 SMITH      CLERK      7902 1980-12-17     800.00               20

 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30

 

 

rownum与>符号

 

我们大都知道一个原则,就是rownum在where后面的时候,要慎用>符号。当使用rownum>XXX的时候,返回结果是错误的。

 

 

SQL> select * from emp where rownum>3;

 

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

----- ---------- --------- ----- ----------- --------- --------- ------

 

 

明明数据表中有数据,但是使用rownum>3的条件时,却是一条也无法搜到。刚刚接触的时候,觉得很诡异。让我们一起看看执行计划。

 

 

SQL> explain plan for select * from emp where rownum>3;

 

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

------------------------------------------

Plan hash value: 2063368778

----------------------------------------------------------------------------

| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |      |    14 |   518 |     3   (0)| 00:00:01 |

|   1 |  COUNT              |      |       |       |            |          |

|*  2 |   FILTER            |      |       |       |            |          |

|   3 |    TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 |

----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter(ROWNUM>3)

 

15 rows selected

 

 

注意,Oracle生成的执行计划比较简单。对EMP进行全表扫描,将每条记录进行筛选(filter操作),符合条件的记录才会最后返回给前台。筛选filter的条件就是rownum>3。

 

解释这个现象,还要从rownum的原理入手。rownum并不是一列事先写好的数值,而是随着select的结果集而动态推进的一个数据序列。rownum初始为1,有一条返回的时候,rownum推进数1。解释刚刚的rownum>3,开始有一条数据返回的时候,rownum为1。但是应用filter条件的时候,因为没有满足rownum>1的条件,所以该行数据被筛去,rownum没有被推进1。这时候第二条结果来,同样不会推进rownum,这样就是一个数据结果都没有。

 

根据这个思路,我们可以让rownum>XX起效果。

 

 

SQL> select count(*) from emp where rownum>0;

//出现结果集合

  COUNT(*)

----------

        14

 

rownum为1初始值的时候,满足了filter的条件,所以能够顺利推进rownum取值。

 

SQL> explain plan for select * from emp where rownum>0;

 

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 2063368778

----------------------------------------------------------------------------

| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |      |    14 |   518 |     3   (0)| 00:00:01 |

|   1 |  COUNT              |      |       |       |            |          |

|*  2 |   FILTER            |      |       |       |            |          |

|   3 |    TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 |

----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter(ROWNUM>0)

 

15 rows selected

 

 

 

 

rownum与order by

 

我们回到本篇的正题,关于rownum与order by。我们有时候遇到这样的需求:“列出按XXX从高到低(逆序)的前五行”。排序+控制显示行数,就是order by和rownum结合的场景。

 

首先,我们构建一下实验环境。

 

 

SQL> conn scott/tiger@orcl;

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as scott

 

SQL> create table t as select rownum as row_num, object_name from user_objects;

 

Table created

 

SQL> select * from t;

 

   ROW_NUM OBJECT_NAME

---------- --------------------------------------------------------------------------------

         1 PK_DEPT

         2 DEPT

(篇幅原因,省略部分内容

        20 SOU

        21 TAR

 

21 rows selected

 

//收集统计量

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

 

PL/SQL procedure successfully completed

 

 

首先,我们实验一条SQL,也是笔者当年的一种写法(惭愧ing)。

 

 

SQL> col object_name format a20;

SQL> select * from t where rownum<5 order by row_num desc;

 

   ROW_NUM OBJECT_NAME

---------- --------------------

         4 PK_EMP

         3 EMP

         2 DEPT

         1 PK_DEPT

 

 

结果显然是不正确的!排序是做到了,但是是在找到的几个数据中进行的排序。我们看看执行计划。

 

 

SQL> explain plan for select * from t where rownum<5 order by row_num desc;

 

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 846588679

----------------------------------------------------------------------------

| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |      |     4 |    52 |     3   (0)| 00:00:01 |

|   1 |  SORT ORDER BY      |      |     4 |    52 |     3   (0)| 00:00:01 |

|*  2 |   COUNT STOPKEY     |      |       |       |            |          |

|   3 |    TABLE ACCESS FULL| T    |    21 |   273 |     3   (0)| 00:00:01 |

----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter(ROWNUM<5)

 

15 rows selected

 

 

这里,我们看到了一个操作“COUNT STOPKEY”,应该是由于rownum而产生的。根据Oracle官方文档的解释,如下:

 

Count operation where the number of rows returned is limited by

the ROWNUM expression in the WHERE clause.

 

含义是,这是一个计数操作。当在where子句中使用rownum的时候,会引入这个操作。这个操作的作用就是限制返回的行数,当返回数量达到rownum的限制(filter)的时候,就结束操作,不会去管剩下的数据

 

这样的解释就对了,我们的数据表T里面虽然需要进行全排序才能确定最大的四个row_num行,但是当前SQL的执行计划是“选取四行,在排序”。这样显然不是我们的需要了。

 

那么,如何在不改变SQL结构的情况下,获取到正确的结果呢? 

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

上一篇: 预定义错误类型
请登录后发表评论 登录
全部评论
求道~

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7745000