结果集按rownum排序的分析

最新推荐文章于 2024-06-16 16:52:35 发布

转载最新推荐文章于 2024-06-16 16:52:35 发布 · 73 阅读

本文探讨了Oracle数据库中查询结果的排序方式，特别是未指定排序条件时，默认按ROWID进行排序的现象，并通过创建表及查询ROWID的方式验证了这一结论。

一次无意中发现查询的结果集按rownum从下到大排序，不知道什么原因，开始想跟object_id，user_id有关，可是查了下发现没有关系。

SQL> select username,rownum,user_id from dba_users where username like 'D%';

USERNAME            ROWNUM    USER_ID
--------------- ---------- ----------
DBSNMP                   1         24
DEPLOYOP                 2         71
DCFSDATA                 3         66
DCFSCDE                  4         67
DCFSOPR                  5         68
DBMGR                    6         56
DMLBAK                   7         73
DBCONN                   8        115
DIP                      9         19
DMSYS                   10         35

10 rows selected

SQL>

SQL> select username,rownum,user_id from dba_users where username like 'D%' order by 1;

USERNAME            ROWNUM    USER_ID
--------------- ---------- ----------
DBCONN                   8        115
DBMGR                    6         56
DBSNMP                   1         24
DCFSCDE                  4         67
DCFSDATA                 3         66
DCFSOPR                  5         68
DEPLOYOP                 2         71
DIP                      9         19
DMLBAK                   7         73
DMSYS                   10         35

10 rows selected

那就怀疑是不是跟rowid有关的，做了个实验试试了，如下：

create tablespace dbmgrdata
logging
datafile '/paic/stg/merge2/oradata/ls06bj/dbmgrdata01.dbf' size 500m
autoextend off
extent management local
segment space management auto ;

SQL>

Tablespace created

SQL> @tbs dbmgrdata

BYTES/1024/1024 FILE_ID FILE_NAME AUTOEXTENSIBLE MAXBYTES/1024/1024/1024
--------------- ---------- -------------------------------------------------------------------------------- -------------- -----------------------
500 168 /paic/stg/merge2/oradata/ls06bj/dbmgrdata01.dbf NO 0

SQL> drop table users;

Table dropped

SQL> create table users as select * from dba_users;

Table created

SQL> select * from dba_objects where object_name='USERS';

OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS TEMPORARY GENERATED SECONDARY
------- --------------------------------------- -------------- ----------------------- ------------- ------------------- ------- --------- --------- ---------
DBMGR USERS 177305 177305 TABLE 10/22/2010 10/22/2010 12 2010-10-22:12:51:37 VALID N N N

SQL>
SQL>
SQL>
SQL> select rowid,rownum,USERNAME
2    from USERS
3   where rowid between dbms_rowid.rowid_create(1, 177305, 168, 0, 0) and
4         dbms_rowid.rowid_create(1, 177305, 168, 9999999, 0)
5         and rownum<21;

ROWID                  ROWNUM USERNAME
------------------ ---------- ------------------------------
AAArSZACoAAAAAMAAA          1 SYS
AAArSZACoAAAAAMAAB          2 SYSTEM
AAArSZACoAAAAAMAAC          3 OUTLN
AAArSZACoAAAAAMAAD          4 DIP
AAArSZACoAAAAAMAAE          5 TSMSYS
AAArSZACoAAAAAMAAF          6 DBSNMP
AAArSZACoAAAAAMAAG          7 WMSYS
AAArSZACoAAAAAMAAH          8 EXFSYS
AAArSZACoAAAAAMAAI          9 LBACSYS
AAArSZACoAAAAAMAAJ         10 TRACESVR
AAArSZACoAAAAAMAAK         11 ACCTLNK
AAArSZACoAAAAAMAAL         12 ACCTMAN
AAArSZACoAAAAAMAAM         13 ACCTOPA
AAArSZACoAAAAAMAAN         14 ACTRESV
AAArSZACoAAAAAMAAO         15 ARMAN
AAArSZACoAAAAAMAAP         16 CCSQRY
AAArSZACoAAAAAMAAQ         17 CHNL_TRANS
AAArSZACoAAAAAMAAR         18 DATAVIEW
AAArSZACoAAAAAMAAS         19 CRMQRY
AAArSZACoAAAAAMAAT         20 DBVIEWER