(转) ROWNUM与ORDER BY联用时对结果集的影响

开贴讲一下自己的理解

先建一个表
     CREATE TABLE TEST(ID NUMBER PRIMARY KEY NOT NULL, NAME VARCHAR2(15));
        /
        INSERT INTO TEST VALUES(1, 'John');
        INSERT INTO TEST VALUES(4, 'Tim');
        INSERT INTO TEST VALUES(2, 'Julie');
        INSERT INTO TEST VALUES(3, 'Stacy');
        INSERT INTO TEST VALUES(6, 'Rahul');
        INSERT INTO TEST VALUES(8, 'Leena');
        INSERT INTO TEST VALUES(7, 'Amy');
        INSERT INTO TEST VALUES(11, 'Bill');
        INSERT INTO TEST VALUES(9, 'Teri');
        INSERT INTO TEST VALUES(5, 'Julie');
        /

注意ID为2和ID为5的NAME是一样的, 有意为之

1, 当ROWNUM单独使用时, 它是按照数据的物理存储位置来赋予NUM号的,
    根据ROWID, 我们可以加以验证, 如下SQL:
        SQL> SELECT ID, NAME, ROWID FROM   TEST WHERE  ROWNUM < 6 ;

                ID NAME            ROWID
        ---------- --------------- ------------------
                 1 John              AAADm2AABAAAGJiAAA
                 4 Tim                AAADm2AABAAAGJiAAB
                 2 Julie               AAADm2AABAAAGJiAAC
                 3 Stacy             AAADm2AABAAAGJiAAD
                 6 Rahul             AAADm2AABAAAGJiAAE

    注意ROWID的顺序

2, 当ROWNUM与ORDER BY联合使用时, 分两种情况:
    a,) 当ROWNUM与ORDER BY处于同一查询语句时, Oracle 是先给记录一个ROWNUM再进行ORDER BY,
         如下情况:
         SQL> SELECT ID, NAME, ROWID FROM   TEST WHERE  ROWNUM < 6  ORDER BY NAME;

                 ID NAME            ROWID
         ---------- --------------- ------------------
                  1 John              AAADm2AABAAAGJiAAA
                  2 Julie              AAADm2AABAAAGJiAAC
                  6 Rahul            AAADm2AABAAAGJiAAE
                  3 Stacy            AAADm2AABAAAGJiAAD
                  4 Tim               AAADm2AABAAAGJiAAB

       注意ROWID, 先取前面5笔记录 AAADm2AABAAAGJiAAA 至 AAADm2AABAAAGJiAAE
       再ORDER BY


    b,) 当ROWNUM与ORDER BY不处于同一查询语句,ORDER BY在子查询而ROWNUM在外层时,
         Oracle是先对记录一个ROWNUM再进行ORDER BY再给记录赋予一个ROWNUM.
         如下情况:   
         SQL> SELECT *
           2  FROM (SELECT ID, NAME, ROWID FROM   TEST ORDER BY NAME)
           3  WHERE  ROWNUM < 6;
         
                 ID NAME            ROWID
         ---------- --------------- ------------------
                  7 Amy               AAADm2AABAAAGJiAAG
                 11 Bill                AAADm2AABAAAGJiAAH
                  1 John              AAADm2AABAAAGJiAAA
                  2 Julie              AAADm2AABAAAGJiAAC
                  5 Julie              AAADm2AABAAAGJiAAJ
   
           这个,...呃, 咋说呢, 一目了然

3, 当ROWNUM与ORDER BY处于同一查询语句时, 根据ORDER BY的字段是否为PRKMARY KEY, 又分两种情况.

    先删除TEST中的记录, 再INSERT如下记录
     INSERT INTO TEST VALUES(99, 'John');
        INSERT INTO TEST VALUES(1, 'John');
        INSERT INTO TEST VALUES(4, 'Tim');
        INSERT INTO TEST VALUES(2, 'Julie');
        INSERT INTO TEST VALUES(3, 'Stacy');
        INSERT INTO TEST VALUES(6, 'Rahul');
        INSERT INTO TEST VALUES(8, 'Leena');
        INSERT INTO TEST VALUES(7, 'Amy');
        INSERT INTO TEST VALUES(11, 'Bill');
        INSERT INTO TEST VALUES(9, 'Teri');
        INSERT INTO TEST VALUES(5, 'Julie');

        留意上面蓝色部分
    a,) 当ORDER BY字段为PRIMARY KEY时, 此时Oracle是先ORDER BY再对记录ROWNUM
           如下SQL:
           SQL> SELECT ID, NAME, ROWID FROM   TEST WHERE  ROWNUM < 6  ORDER BY ID;

                   ID NAME            ROWID
           ---------- --------------- ------------------
                    1 John              AAADm5AABAAAGJiAAB
                    2 Julie              AAADm5AABAAAGJiAAD
                    3 Stacy            AAADm5AABAAAGJiAAE
                    4 Tim               AAADm5AABAAAGJiAAC
                    5 Julie             AAADm5AABAAAGJiAAK

            再看看ID为99的记录的ROWID
            SQL> SELECT ID, NAME, ROWID FROM   TEST;

                    ID NAME            ROWID
            ---------- --------------- ------------------
                    99 John             AAADm5AABAAAGJiAAA
                     1 John              AAADm5AABAAAGJiAAB
                     4 Tim                AAADm5AABAAAGJiAAC
                     2 Julie               AAADm5AABAAAGJiAAD
                     3 Stacy             AAADm5AABAAAGJiAAE
                     6 Rahul             AAADm5AABAAAGJiAAF
                     8 Leena            AAADm5AABAAAGJiAAG
                     7 Amy               AAADm5AABAAAGJiAAH
                    11 Bill                AAADm5AABAAAGJiAAI
                     9 Teri                AAADm5AABAAAGJiAAJ
                     5 Julie               AAADm5AABAAAGJiAAK

          注意粉红色部分, 应该可以看出ID为99的记录最先INSERT, 其ROWID最小,
          但由于其ID最大, 且ID为PRIMARY KEY, 因此在
       SELECT ID, NAME, ROWID FROM   TEST WHERE  ROWNUM < 6  ORDER BY ID
           先对ID做了ORDER BY操作, 再对操作后的结果集赋ROWNUM,
           然后取出符合条件(ROWNUM < 6)的值

    b,) 当ORDER BY字段不为PRIMARY KEY时, 参照我写的第 1 种情况

 

转自http://www.itpub.net/thread-961972-1-1.html