SQL之美 - 分页查询的排序问题
编辑手记：前面我们分享过分页查询的基础知识，其目的就是控制输出结果集大小，将结果尽快的返回。主要有两种方式，一种是嵌套的查询方式，一种是通过范围控制分页的最大值和最小值。详情请阅读：让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信息无法推到查询内部，导致这种写法的执行效率很低。虽然这种方式也可以避免重复数据问题，但是不推荐使用这种方式。
关于分页查询的更多知识，请继续关注后期的分享。