oracle sql分页翻页时发生数据重复的问题

我用with as 写了一个复杂的sql给开发，后来告诉我分页的时候排序数据有重复，问我是不是with as 不支持分页，这怎么可能，with as跟分页怎么会不兼容呢，它的本质也就是一个select语句啊。
查看查询结果，发现排序字段有很多重复值，导致排序随机，造成分页数据重复问题。跟with as没关系，为with as 平反，哈哈。
下面做一个小demo来重现此问题：
drop table test purge;
Create table test as select * from dba_tables;
–查询第一页：

SCOTT@PROD> select *
  from (select temp.*,rownum rn
          from (select  table_name, owner
                  from test
                 order by owner) temp
         where rownum <= 1* 10) temp1 
  where rn>(1-1)*10;

复制

TABLE_NAME		       OWNER			    RN
------------------------------ -------------------- ----------
WLM_METRICS_STREAM	       APPQOSSYS		     1
WLM_FEATURE_USAGE	       APPQOSSYS		     2
WLM_VIOLATION_STREAM	   APPQOSSYS		     3
WLM_MPA_STREAM		   APPQOSSYS		     4
WLM_CLASSIFIER_PLAN	       APPQOSSYS		     5
AUD$UNIFIED		           AUDSYS			 6
DR$CLASS		               CTXSYS			     7
DR$OBJECT_ATTRIBUTE_LOV    CTXSYS			     8
DR$OBJECT_ATTRIBUTE	       CTXSYS			 9
DR$OBJECT		           CTXSYS			 10

10 rows selected.

复制

–查询第二页

SCOTT@PROD> select *
  from (select temp.*,rownum rn
          from (select  table_name, owner
                  from test
                 order by owner) temp
         where rownum <= 2* 10) temp1 
  where rn>(2-1)*10;  

复制

TABLE_NAME		       OWNER			    RN
------------------------------ -------------------- ----------
SYS_IOT_OVER_61247	       CTXSYS			    11
DR$POLICY_TAB		       CTXSYS			    12
DR$INDEX_VALUE		       CTXSYS			    13
DR$INDEX_PARTITION	       CTXSYS			    14
DR$INDEX		               CTXSYS			        15
DR$PREFERENCE_VALUE	       CTXSYS			    16
DR$PREFERENCE		       CTXSYS			    17
DR$OBJECT_ATTRIBUTE_LOV    CTXSYS			        18
DR$OBJECT_ATTRIBUTE	       CTXSYS			    19
DR$OBJECT		           CTXSYS			    20

10 rows selected.

复制

–查询第三页

SCOTT@PROD> select *
  from (select temp.*,rownum rn
          from (select  table_name, owner
                  from test
                 order by owner) temp
         where rownum <= 3* 10) temp1 
  where rn>(3-1)*10; 

复制

TABLE_NAME		       OWNER			    RN
------------------------------ -------------------- ----------
SYS_IOT_OVER_61247	       CTXSYS			    21
DR$POLICY_TAB		       CTXSYS			    22
DR$INDEX_VALUE		       CTXSYS			    23
DR$INDEX_PARTITION	       CTXSYS			    24
DR$INDEX		               CTXSYS			        25
DR$PREFERENCE_VALUE	       CTXSYS			    26
DR$PREFERENCE		       CTXSYS			    27
DR$OBJECT_ATTRIBUTE_LOV    CTXSYS			        28
DR$OBJECT_ATTRIBUTE	       CTXSYS			    29
DR$OBJECT		           CTXSYS			    30

10 rows selected.

复制

我们发现第二页与第三页数据相同，接下来再查第四页、第五页数据也相同，直到查第六页时发生变化
–查询第六页

SCOTT@PROD> select *
  from (select temp.*,rownum rn
          from (select  table_name, owner
                  from test
                 order by owner) temp
         where rownum <= 6* 10) temp1 
  where rn>(6-1)*10;  2    3    4    5    6    7  

TABLE_NAME		       OWNER			    RN
------------------------------ -------------------- ----------
DR$POLICY_TAB		       CTXSYS			    51
DR$INDEX_VALUE		       CTXSYS			    52
DR$INDEX_PARTITION	       CTXSYS			    53
DR$INDEX		                CTXSYS			    54
DR$PREFERENCE_VALUE	       CTXSYS			    55
DR$PREFERENCE		       CTXSYS			    56
DR$OBJECT_ATTRIBUTE_LOV     CTXSYS			    57
DR$OBJECT_ATTRIBUTE	       CTXSYS			    58
DR$OBJECT		           CTXSYS			    59
EXADIRECT_ACL		       DBSFWUSER		    60

复制

第六页为什么会变呢，因为排序字段的值发生了变化。
总结：分页，没有排序字段不行，因为查询每页的结果时，每页的查询sql相对独立，不能保证oracle每次查询结果相同；有了排序字段，但是排序字段重复数据太多，极端情况完全相同，也是近似无序，同样也会出现翻页时有重复数据。
解决办法：排序字段上加上唯一值字段，或者排序组合字段无重复值，就不会出现重复数据了。
例如，我们在上面排序字段上加入table_name,翻页就不会出现重复数据了。
–第一页

SCOTT@PROD> select *
  from (select temp.*,rownum rn
          from (select  table_name, owner
                  from test
                 order by owner,table_name) temp
         where rownum <= 1* 10) temp1 
  where rn>(1-1)*10;  2    3    4    5    6    7  

TABLE_NAME		       OWNER			    RN
------------------------------ -------------------- ----------
WLM_CLASSIFIER_PLAN	       APPQOSSYS		     1
WLM_FEATURE_USAGE	       APPQOSSYS		     2
WLM_METRICS_STREAM	       APPQOSSYS		     3
WLM_MPA_STREAM		   APPQOSSYS		     4
WLM_VIOLATION_STREAM	   APPQOSSYS		     5
AUD$UNIFIED		           AUDSYS			 6
DR$ACTIVELOGS		       CTXSYS			 7
DR$AUTOOPT		           CTXSYS			 8
DR$CLASS		               CTXSYS			     9
DR$DBO			           CTXSYS			 10

10 rows selected.
--第二页
SCOTT@PROD> select *
  from (select temp.*,rownum rn
          from (select  table_name, owner
                  from test
                 order by owner,table_name) temp
         where rownum <= 2* 10) temp1 
  where rn>(2-1)*10;  2    3    4    5    6    7  

TABLE_NAME		       OWNER			    RN
------------------------------ -------------------- ----------
DR$DELETE		       CTXSYS			    11
DR$DICTIONARY		   CTXSYS			    12
DR$FEATURE_USED 	   CTXSYS			    13
DR$FREQTOKS		       CTXSYS			    14
DR$IDX_DICTIONARIES	   CTXSYS			    15
DR$INDEX		           CTXSYS			    16
DR$INDEX_CDI_COLUMN	   CTXSYS			    17
DR$INDEX_ERROR		   CTXSYS			    18
DR$INDEX_OBJECT 	       CTXSYS			    19
DR$INDEX_PARTITION	   CTXSYS			    20

10 rows selected.
--第三页
SCOTT@PROD> select *
  from (select temp.*,rownum rn
          from (select  table_name, owner
                  from test
                 order by owner,table_name) temp
         where rownum <= 3* 10) temp1 
  where rn>(3-1)*10;  2    3    4    5    6    7  

TABLE_NAME		       OWNER			    RN
------------------------------ -------------------- ----------
DR$INDEX_SET		       CTXSYS			    21
DR$INDEX_SET_INDEX	   CTXSYS			    22
DR$INDEX_VALUE		   CTXSYS			    23
DR$NUMBER_SEQUENCE	   CTXSYS			    24
DR$NVTAB		       CTXSYS			    25
DR$OBJECT		       CTXSYS			    26
DR$OBJECT_ATTRIBUTE	   CTXSYS			    27
DR$OBJECT_ATTRIBUTE_LOV  CTXSYS			    28
DR$ONLINE_PENDING	   CTXSYS			    29
DR$PARALLEL		       CTXSYS			    30

10 rows selected.

复制

补充：
其实最好是加上rowid，因为索引本身是存储rowid信息的，而且对于相同索引值的行是按rowid来排序的，也就是说排序列上加上rowid即可以避免重复，又可以不用在索引中加入其他字段而增大索引的体积。