oracle读顺序,Oracle读取数据的顺序问题

点击标题下「蓝色微信名」可快速关注

预计阅读时间：9分钟

Oracle中，关于数据检索的顺序，可能有个误区，即“如果数据按顺序存储(insert)，则可能读取(select)出来的数据就是按顺序的，即使不带order by“，所谓的“默认排序”，准确地说，无order by的语句，返回的结果顺序，应该是不确定的，而且不是“随机”。

Oracle中，默认创建的表，都是heap表，即堆表。堆表存放数据，就是“随便存“，存的时候就是乱序的。如果创建的是索引组织表，数据就会默认排序了。

一张堆表，Oracle对无order by的语句返回的结果不进行排序，此时的处理方式是按照数据的物理存储顺序来读取数据，就是说查询的结果和数据存储在磁盘上的位置有关系。

这里有个概念，就是rowid，他代表着每行数据的物理地址，关于rowid的含义，可以参考《什么是rowid？》。有时候检索结果，上面说了，无order by语句返回的结果，会按照数据的物理存储顺序，进行读取，因此，看起来会像是使用rowid排序的，理论上，相同的数据库，完全没有修改，前后两次相同的查询返回的顺序应该一样，但实际上，这个顺序是可能被打乱的，例如，在表的数据被删除后，rowid会被新插入的数据占用，所以一个无order by查询结果看起来也可能是个杂乱无章的。而且，有些场景，不会按照rowid读取，因此rowid不是唯一的“排序”读取规则。

下面通过实验，进一步了解下排序的问题，首先，创建测试表空间，大小设为1M，

SQL>create tablespace test_tbs

datafile '/u01/app/oracle/oradata/BISAL/test_tbs01.dbf' size 1m;

Tablespace created.

创建一张测试表，test_1，定义在test_tbs表空间，初始分配空间896k，

SQL>create table test_1 (id number)

tablespace test_tbs storage(initial 896k);

Table created.

之所以设为896k，为了先占据空间，后面会用到，1024k-896k=128k，但是数据文件头，会占用些空间，因此可用空间，小于128k，

SQL> select 1024-896 from dual;

1024-896

----------

128

但是需要注意，由于11g新特性，延迟段创建，因此为了真正占用存储空间，要实际插入一条数据，此时能看出，test_tbs表空间，已被使用，

TBSPNAME  TBSP_SIZE  USED  FREE   PCT_USED

--------  --------- ------ ------ ---------

TEST_TBS      1         0   .9375      0

SQL>insert into test_1 values(1);

1 row created.

SQL>commit;

Commit complete.

TBSPNAME  TBSP_SIZE  USED   FREE   PCT_USED

--------  --------- ------ ------ ---------

TEST_TBS      1       .875  .0625     87.5

创建第二张表，test_2，包含3个char(2000)的字段，由于1个block，默认8k，因此1条记录，会占用1个block，同时设置initial初始分配空间64k，刚才说了，此时表空间，<128k了，因此最多分配1次，

SQL> create table test_2 (id number, a char(2000), b char(2000),

c char(2000)) tablespace test_tbs storage(initial 64k);

Table created.

我们计算一下，1个block是8k，表头占据3个block，1条记录占据1个block，1个extent是64k，因此1个extent只能存储五条记录，再次分配空间，会提示空间已满，

SQL> insert into test_2 values(1, 'A', 'A', 'A');

1 row created.

SQL> insert into test_2 values(2, 'A', 'A', 'A');

1 row created.

SQL> insert into test_2 values(3, 'A', 'A', 'A');

1 row created.

SQL> insert into test_2 values(4, 'A', 'A', 'A');

1 row created.

SQL> insert into test_2 values(5, 'A', 'A', 'A');

1 row created.

SQL> insert into test_2 values(6, 'A', 'A', 'A');

insert into test_2 values(6, 'A', 'A', 'A')

*

ERROR at line 1:

ORA-01653: unable to extend table BISAL.TEST_2 by 8 in tablespace TEST_TBS

返回的结果，并不是按照插入顺序显示，而是按照rowid的顺序，

SQL> select id, rowid from test_2;

ID ROWID

---------- ------------------

2 AAADr1AAHAAAAB7AAA

3 AAADr1AAHAAAAB8AAA

4 AAADr1AAHAAAAB9AAA

5 AAADr1AAHAAAAB+AAA

1 AAADr1AAHAAAAB/AAA5rows selected.

现在删除test_1，腾出空间，向test_2插入第6条记录，

SQL>drop table test_1 purge;

Table dropped.

SQL>insert into test_2 values(6, 'A', 'A', 'A');

1 row created.

再看返回的结果，由于test_1空间释放，test_2可以分配第2个64k的extent，而他的block_id要小于原extent的block_id，因此这条记录的rowid，要比前5条记录rowid小，所以，现在返回的结果，又不是按照rowid排序，准确地说，同一个extent中，Oracle会按照rowid顺序读取，否则，就不一定了，

SQL> select id, rowid from test_2;

ID ROWID

---------- ------------------

2 AAADr1AAHAAAAB7AAA

3 AAADr1AAHAAAAB8AAA

4 AAADr1AAHAAAAB9AAA

5 AAADr1AAHAAAAB+AAA

1 AAADr1AAHAAAAB/AAA

6 AAADr1AAHAAAAAJAAA

6 rows selected.

补充一下，如果需要检索结果的顺序，是随机的，此时能用dbms_random包，构造随机数，order by一个随机数，结果就是随机的，如下所示，两次select结果的顺序，是不同的，

SQL> select id, rowid from test_2 order by dbms_random.value;

ID ROWID

---------- ------------------

3 AAADr1AAHAAAAB8AAA

6 AAADr1AAHAAAAAJAAA

5 AAADr1AAHAAAAB+AAA

1 AAADr1AAHAAAAB/AAA

2 AAADr1AAHAAAAB7AAA

4 AAADr1AAHAAAAB9AAA

6 rows selected.

SQL> select id, rowid from test_2 order by dbms_random.value;

ID ROWID

---------- ------------------

3 AAADr1AAHAAAAB8AAA

6 AAADr1AAHAAAAAJAAA

2 AAADr1AAHAAAAB7AAA

1 AAADr1AAHAAAAB/AAA

5 AAADr1AAHAAAAB+AAA

4 AAADr1AAHAAAAB9AAA

6 rows selected.

除了上面排序的问题，关于检索结果的排序，还碰见另一个坑，如果是Oracle 9i，使用group by，会默认按照group by的字段顺序，进行order by排序，但如果迁移至Oracle 10g，及以上的版本，就会因为不带order by，返回的结果，出现乱序，可以参考《Oracle不同版本group by的变化》。

因此，Oracle中，如果对检索结果，有排序的需求，就一定加上order by，明确指定排序的字段。