SQL之美 - 分页查询的排序问题

SQL> CREATE TABLE TEST AS SELECT ROWNUM ID, A.*FROM DBA_OBJECTS A;

表已创建。

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'TEST')

PL/SQL 过程已成功完成。

SQL>SET AUTOT ON EXP
SQL> SELECT ID, OBJECT_NAME, OWNER
  2  FROM 
  3   (
  4    SELECT ROWNUM RN, ID, OBJECT_NAME, OWNER
  5    FROM 
  6     (
  7      SELECT OWNER, OBJECT_NAME, ID
  8      FROM TEST ORDER BY OWNER
  9     )
 10    WHERE ROWNUM <= 10
 11   )
 12  WHERE RN >= 1;

   ID     OBJECT_NAME            OWNER
-------  -----------------------  ----------------
6231     AL                                  CCC
6232     AL_I_2                            CCC
6233     AL_I_FNAME_STATUS   CCC
6236     BCB                               CCC
6235     AL_U1                            CCC
6234     AL_P                              CCC
6240     BCF_U1                         CCC
6239     BCF_P                           CCC
6238     BCF                               CCC
6237    BCB_U1                         CCC

已选择10行。

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE(Cost=72 Card=10 Bytes=1090)
   1    0   VIEW (Cost=72 Card=10Bytes=1090)
   2    1     COUNT (STOPKEY)
   3    2       VIEW(Cost=72 Card=6363 Bytes=610848)
   4   3         SORT (ORDER BY STOPKEY)(Cost=72 Card=6363 Bytes=165438)
   5   4           TABLE ACCESS(FULL) OF 'TEST' (Cost=20 Card=6363 Bytes=165438)

 

上面例子给出的就是分页查询的标准写法，对于查询前N条数据具有最高的效率。

但是这种分页排序语句存在一个问题，我们来看下面对例子：

SQL>SELECT ID, OBJECT_NAME, OWNER
  2  FROM 
  3   (
  4    SELECT ROWNUM RN, ID, OBJECT_NAME, OWNER
  5    FROM 
  6     (
  7      SELECT OWNER, OBJECT_NAME, ID
  8      FROM TEST ORDER BY OWNER
  9     )
 10    WHERE ROWNUM <= 20
 11   )
 12  WHERE RN >= 11;

       ID     OBJECT_NAME                         OWNER
---------- ------------------------------ ---------------------
   6249     BP_P                                              CCC
   6248     BP_I_DEVICE_HANDLE_STATUS  CCC
   6247     BP                                                  CCC
   6245     BDF_P                                            CCC
   6243     BDF_I_BS_KEY                              CCC
   6241     BCF_U2                                          CCC
   6239     BCF_P                                            CCC
   6237     BCB_U1                                          CCC
   6236     BCB                                                CCC
   6235     AL_U1                                            CCC

已选择10行。

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE(Cost=72 Card=20 Bytes=2180)
   1    0   VIEW (Cost=72 Card=20Bytes=2180)
   2    1     COUNT (STOPKEY)
   3    2       VIEW(Cost=72 Card=6363 Bytes=610848)
   4   3         SORT (ORDER BY STOPKEY)(Cost=72 Card=6363 Bytes=165438)
   5   4           TABLE ACCESS(FULL) OF 'TEST' (Cost=20 Card=6363 Bytes=165438)

 

对比这次的结果和第一次的结果，就会发现ID为6235的数据出现了两次。第一次在前10条返回记录中，6235出现了，而第二次在11到第20条记录中，6235又出现了。一条数据重复出现两次，就必然意味着有数据在两次查询中都不会出现。

其实造成这个问题的原因很简单，是由于排序列不唯一造成的。Oracle这里使用的排序算法不具有稳定性，也就是说，对于键值相等的数据，这种算法完成排序后，不保证这些键值相等的数据保持排序前的顺序。

在这个例子中，OWNER列包含了大量键值为CCC的数据，而且Oracle的排序算法不具有稳定性，因此前10行记录和前20行记录中键值的顺序不能保证一致。因此，就造成某些数据会重复出现，而有些数据不会出现的现象。

解决这个问题其实也很简单。有两种方法可以考虑。

一，在使用不唯一的字段排序时，后面跟一个唯一的字段。

SQL>SELECT ID, OBJECT_NAME, OWNER
  2  FROM 
  3   (
  4    SELECT ROWNUM RN, ID, OBJECT_NAME, OWNER
  5    FROM 
  6     (
  7      SELECT OWNER, OBJECT_NAME, ID
  8      FROM TEST ORDER BY OWNER, ID
  9     )
 10    WHERE ROWNUM <= 10
 11   )
 12  WHERE RN >= 1;

       ID  OBJECT_NAME             OWNER
---------- ------------------------- --------------
      6231  AL                              CCC
      6232  AL_I_2                        CCC
      6233  AL_I_FNAME_STATUS  CCC
      6234  AL_P                          CCC
      6235  AL_U1                        CCC
      6236  BCB                           CCC
      6237  BCB_U1                     CCC
      6238  BCF                           CCC
      6239  BCF_P                       CCC
      6240  BCF_U1                     CCC

已选择10行。

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE(Cost=72 Card=10 Bytes=1090)
   1    0   VIEW (Cost=72 Card=10Bytes=1090)
   2    1     COUNT (STOPKEY)
   3    2       VIEW(Cost=72 Card=6363 Bytes=610848)
   4   3         SORT (ORDER BY STOPKEY)(Cost=72 Card=6363 Bytes=165438)
   5   4           TABLE ACCESS(FULL) OF 'TEST' (Cost=20 Card=6363 Bytes=165438)

 

SQL>SELECT ID, OBJECT_NAME, OWNER
  2  FROM 
  3   (
  4    SELECT ROWNUM RN, ID, OBJECT_NAME, OWNER
  5    FROM 
  6     (
  7      SELECT OWNER, OBJECT_NAME, ID
  8      FROM TEST ORDER BY OWNER, ID
  9     )
 10    WHERE ROWNUM <= 20
 11   )
 12  WHERE RN >= 11;

       ID     OBJECT_NAME     OWNER
---------- ---------------------  ----------------------------
      6241  BCF_U2                  CCC
      6242  BDF                        CCC
      6243  BDF_I_BS_KEY      CCC
      6244  BDF_I_DF_KEY       CCC
      6245  BDF_P                    CCC
      6246  BDF_U1                  CCC
      6247  BP                           CCC
      6248  BP_I_DEVICE_HANDLE_STATUS      CCC
      6249  BP_P                      CCC
      6250  BP_U1                    CCC

已选择10行。

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE(Cost=72 Card=20 Bytes=2180)
   1    0   VIEW (Cost=72 Card=20Bytes=2180)
   2    1     COUNT (STOPKEY)
   3    2       VIEW(Cost=72 Card=6363 Bytes=610848)
   4   3         SORT (ORDER BY STOPKEY)(Cost=72 Card=6363 Bytes=165438)
   5   4           TABLE ACCESS(FULL) OF 'TEST' (Cost=20 Card=6363 Bytes=165438)

 

一般在排序字段后面跟一个主键就可以了，如果表不存在主键，跟ROWID也可以。

这种方法最简单，且对性能的影响最小。

二、使用前面给出过多次的BETWEEN AND的方法。

SQL>SELECT ID, OBJECT_NAME, OWNER
  2  FROM 
  3   (
  4    SELECT ROWNUM RN, ID, OBJECT_NAME, OWNER
  5    FROM 
  6     (
  7      SELECT OWNER, OBJECT_NAME, ID
  8      FROM TEST ORDER BY OWNER
  9     )
 10   )
 11  WHERE RN BETWEEN 1 AND 10;

       ID    OBJECT_NAME      OWNER
---------- --------------------- ------------
      6231  AL                            CCC
      6232  AL_I_2                      CCC
      6233  AL_I_FNAME_STATUS    CCC
      6234  AL_P                        CCC
      6238  BCF                         CCC
      6240  BCF_U1                   CCC
      6242  BDF                         CCC
      6244  BDF_I_DF_KEY        CCC
      6246  BDF_U1                   CCC
      6255  BRL_U1                   CCC

已选择10行。

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE(Cost=72 Card=6363 Bytes=693567)
   1    0   VIEW (Cost=72 Card=6363Bytes=693567)
   2    1     COUNT
   3    2       VIEW(Cost=72 Card=6363 Bytes=610848)
   4   3         SORT (ORDER BY) (Cost=72Card=6363 Bytes=165438)
   5    4          TABLE ACCESS (FULL) OF 'TEST' (Cost=20 Card=6363 Bytes=165438)

 

SQL>SELECT ID, OBJECT_NAME, OWNER
  2  FROM 
  3   (
  4    SELECT ROWNUM RN, ID, OBJECT_NAME, OWNER
  5    FROM 
  6     (
  7      SELECT OWNER, OBJECT_NAME, ID
  8      FROM TEST ORDER BY OWNER
  9     )
 10   )
 11  WHERE RN BETWEEN 11 AND 20;

       ID    OBJECT_NAME         OWNER
---------- ---------------------- -----------------
      6254   BRL_P                          CCC
      6253   BRL_I_DTS                   CCC
      6252   BRL_I_BS_KEY             CCC
      6251   BRL                               CCC
      6250   BP_U1                          CCC
      6249   BP_P                             CCC
      6248   BP_I_DEVICE_HANDLE_STATUS      CCC
      6247   BP                                 CCC
      6264   CCF                              CCC
      6263   CCB_U1                        CCC

已选择10行。

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE(Cost=72 Card=6363 Bytes=693567)
   1    0   VIEW (Cost=72 Card=6363Bytes=693567)
   2    1     COUNT
   3    2       VIEW(Cost=72 Card=6363 Bytes=610848)
   4   3         SORT (ORDER BY) (Cost=72Card=6363 Bytes=165438)
   5   4           TABLE ACCESS(FULL) OF 'TEST' (Cost=20 Card=6363 Bytes=165438)

 

这种方式由于采用表数据的全排序，每次只取全排序中的某一部分数据，因此不会出现上面提到的重复数据问题。

但是正是由于使用了全排序，而且ROWNUM信息无法推到查询内部，导致这种写法的执行效率很低。虽然这种方式也可以避免重复数据问题，但是不推荐使用这种方式。