linux排序和分页,分页查询的排序问题

前两天刚刚看完Tom的EFFECTIVE ORACLE BY DESIGN的第八章，从里面学到不是东西。同时也发现一个问题：Tom给出的关于分页以及ROWNUM的时候，几乎包含了我前几篇关于分页文章中的所有关键点。

于是，打算补充一篇关于分页排序问题的文章，这篇文章中的内容是Tom书中没有的。

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=10 Bytes=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=20 Bytes=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=10 Bytes=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=20 Bytes=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=6363 Bytes=693567)

2 1 COUNT

3 2 VIEW (Cost=72 Card=6363 Bytes=610848)

4 3 SORT (ORDER BY) (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

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=6363 Bytes=693567)

2 1 COUNT

3 2 VIEW (Cost=72 Card=6363 Bytes=610848)

4 3 SORT (ORDER BY) (Cost=72 Card=6363 Bytes=165438)

5 4 TABLE ACCESS (FULL) OF 'TEST' (Cost=20 Card=6363 Bytes=165438)

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

但是正是由于使用了全排序，而且ROWNUM信息无法推到查询内部，导致这种写法的执行效率很低。对比二种效率的例子，前面分页查询的文章中以及有很多了，这里就不在重复描述了。虽然这种方式也可以避免重复数据问题，但是不推荐使用这种方式。