在使用
Oracle的orderby时发现了一个有趣的现象,不多废话,看现象:
ETL@RACTEST> create table ttt1 (a varchar2(2),bnumber);
Table created.
ETL@RACTEST> insert into ttt1 values('a',1);
1 row created.
Elapsed: 00:00:00.01
ETL@RACTEST> insert into ttt1 values('b',1);
1 row created.
Elapsed: 00:00:00.00
ETL@RACTEST> select * from ttt1 order by b;
A
B
-- ----------
b
1
a
1
Elapsed: 00:00:00.01
ETL@RACTEST> insert into ttt1 values('c',1);
1 row created.
Elapsed: 00:00:00.00
ETL@RACTEST> select * from ttt1 order by b;
A
B
-- ----------
a
1
c
1
b
1
Elapsed: 00:00:00.00
ETL@RACTEST> insert into ttt1 values('d',1);
1 row created.
Elapsed: 00:00:00.00
ETL@RACTEST> select * from ttt1 order by b;
A
B
-- ----------
a
1
d
1
c
1
b
1
Elapsed: 00:00:00.00
ETL@RACTEST> insert into ttt1 values('e',1);
1 row created.
Elapsed: 00:00:00.00
ETL@RACTEST> select * from ttt1 order by b;
A
B
-- ----------
a
1
b
1
e
1
d
1
c
1
Elapsed: 00:00:00.00
有心的人一定已经看到现象了,我每次插入一条新值时,Oracle对几个1的排序顺序都是不一样的,而且看不出什么规律来。如果我们查询时不加orderby,那么Oracle将按照
rowid的大小来读取数据:
ETL@RACTEST> select a,b,rowid from ttt1;
A
B ROWID
-- ---------- ------------------
a
1AAE7FHAAJAAACg4AAA
b
1AAE7FHAAJAAACg4AAB
c
1AAE7FHAAJAAACg4AAC
d
1AAE7FHAAJAAACg4AAD
e
1AAE7FHAAJAAACg4AAE
Elapsed: 00:00:00.02
从这个现象我可以判定出Oracle的orderby使用的是一种非稳定的排序算法,因为只有非稳定的排序算法才会改变排序key相同的值的位置。起初我以为Oracle使用的是一种快速排序,因为快速排序是非稳定的排序算法,并且它的性能比堆排序好,但是我突然想到一个问题,就是快速排序在基本有序的情况下性能退化为O(N2)(N的平方)。而数据库里的存储在很多情况下恰恰是基本有序的,因此用快速排序肯定是不明智的选择。
又查了一些资料,得知Oracle的orderby在9i之前是在内存中构建一棵索引树来进行排序,对于值相同的数据按照rowid大小来进行排序。但是如果数据量很大的时候,在内存中构建索引树效率是很低的,因此9i以后Oracle调整了orderby的算法,改为一种变形的堆排序算法。因为堆排序也是非稳定的排序算法,所以和我之前的结论也不矛盾。
此外,根据这篇参考资料的描述,我们还可以得到一个有用的信息,那就是orderby如果走的是索引的话,那么对于值相同的数据会按rowid来进行排序,我们来验证一下:
ETL@RACTEST> truncate table ttt1;
Table truncated.
Elapsed: 00:00:00.02
ETL@RACTEST> alter table ttt1 modify b notnull;
Table altered.
Elapsed: 00:00:00.08
ETL@RACTEST> create index i_ttt1 on ttt1(b);
Index created.
Elapsed: 00:00:00.01
ETL@RACTEST> insert into ttt1 values('a',1);
1 row created.
Elapsed: 00:00:00.00
ETL@RACTEST> insert into ttt1 values('b',1);
1 row created.
Elapsed: 00:00:00.01
ETL@RACTEST> select a,b,rowid from ttt1 order byb;
A
B ROWID
-- ---------- ------------------
a
1AAE7FSAAJAAACg4AAA
b
1AAE7FSAAJAAACg4AAB
Elapsed: 00:00:00.00
ETL@RACTEST> insert into ttt1 values('c',1);
1 row created.
Elapsed: 00:00:00.00
ETL@RACTEST> select a,b,rowid from ttt1 order byb;
A
B ROWID
-- ---------- ------------------
a
1AAE7FSAAJAAACg4AAA
b
1AAE7FSAAJAAACg4AAB
c
1AAE7FSAAJAAACg4AAC
Elapsed: 00:00:00.00
ETL@RACTEST> insert into ttt1 values('d',1);
1 row created.
Elapsed: 00:00:00.01
ETL@RACTEST> select a,b,rowid from ttt1 order byb;
A
B ROWID
-- ---------- ------------------
a
1AAE7FSAAJAAACg4AAA
b
1AAE7FSAAJAAACg4AAB
c
1AAE7FSAAJAAACg4AAC
d
1AAE7FSAAJAAACg4AAD
Elapsed: 00:00:00.00
ETL@RACTEST> insert into ttt1 values('e',1);
1 row created.
Elapsed: 00:00:00.00
ETL@RACTEST> select a,b,rowid from ttt1 order byb;
A
B ROWID
-- ---------- ------------------
a
1AAE7FSAAJAAACg4AAA
b
1AAE7FSAAJAAACg4AAB
c
1AAE7FSAAJAAACg4AAC
d
1AAE7FSAAJAAACg4AAD
e
1AAE7FSAAJAAACg4AAE
Elapsed: 00:00:00.00
这回看到的排序结果很舒服了吧,因为它走的是索引排序。
ETL@RACTEST> create table ttt1 (a varchar2(2),bnumber);
Table created.
ETL@RACTEST> insert into ttt1 values('a',1);
1 row created.
Elapsed: 00:00:00.01
ETL@RACTEST> insert into ttt1 values('b',1);
1 row created.
Elapsed: 00:00:00.00
ETL@RACTEST> select * from ttt1 order by b;
A
-- ----------
b
a
Elapsed: 00:00:00.01
ETL@RACTEST> insert into ttt1 values('c',1);
1 row created.
Elapsed: 00:00:00.00
ETL@RACTEST> select * from ttt1 order by b;
A
-- ----------
a
c
b
Elapsed: 00:00:00.00
ETL@RACTEST> insert into ttt1 values('d',1);
1 row created.
Elapsed: 00:00:00.00
ETL@RACTEST> select * from ttt1 order by b;
A
-- ----------
a
d
c
b
Elapsed: 00:00:00.00
ETL@RACTEST> insert into ttt1 values('e',1);
1 row created.
Elapsed: 00:00:00.00
ETL@RACTEST> select * from ttt1 order by b;
A
-- ----------
a
b
e
d
c
Elapsed: 00:00:00.00
有心的人一定已经看到现象了,我每次插入一条新值时,Oracle对几个1的排序顺序都是不一样的,而且看不出什么规律来。如果我们查询时不加orderby,那么Oracle将按照
rowid的大小来读取数据:
ETL@RACTEST> select a,b,rowid from ttt1;
A
-- ---------- ------------------
a
b
c
d
e
Elapsed: 00:00:00.02
从这个现象我可以判定出Oracle的orderby使用的是一种非稳定的排序算法,因为只有非稳定的排序算法才会改变排序key相同的值的位置。起初我以为Oracle使用的是一种快速排序,因为快速排序是非稳定的排序算法,并且它的性能比堆排序好,但是我突然想到一个问题,就是快速排序在基本有序的情况下性能退化为O(N2)(N的平方)。而数据库里的存储在很多情况下恰恰是基本有序的,因此用快速排序肯定是不明智的选择。
又查了一些资料,得知Oracle的orderby在9i之前是在内存中构建一棵索引树来进行排序,对于值相同的数据按照rowid大小来进行排序。但是如果数据量很大的时候,在内存中构建索引树效率是很低的,因此9i以后Oracle调整了orderby的算法,改为一种变形的堆排序算法。因为堆排序也是非稳定的排序算法,所以和我之前的结论也不矛盾。
此外,根据这篇参考资料的描述,我们还可以得到一个有用的信息,那就是orderby如果走的是索引的话,那么对于值相同的数据会按rowid来进行排序,我们来验证一下:
ETL@RACTEST> truncate table ttt1;
Table truncated.
Elapsed: 00:00:00.02
ETL@RACTEST> alter table ttt1 modify b notnull;
Table altered.
Elapsed: 00:00:00.08
ETL@RACTEST> create index i_ttt1 on ttt1(b);
Index created.
Elapsed: 00:00:00.01
ETL@RACTEST> insert into ttt1 values('a',1);
1 row created.
Elapsed: 00:00:00.00
ETL@RACTEST> insert into ttt1 values('b',1);
1 row created.
Elapsed: 00:00:00.01
ETL@RACTEST> select a,b,rowid from ttt1 order byb;
A
-- ---------- ------------------
a
b
Elapsed: 00:00:00.00
ETL@RACTEST> insert into ttt1 values('c',1);
1 row created.
Elapsed: 00:00:00.00
ETL@RACTEST> select a,b,rowid from ttt1 order byb;
A
-- ---------- ------------------
a
b
c
Elapsed: 00:00:00.00
ETL@RACTEST> insert into ttt1 values('d',1);
1 row created.
Elapsed: 00:00:00.01
ETL@RACTEST> select a,b,rowid from ttt1 order byb;
A
-- ---------- ------------------
a
b
c
d
Elapsed: 00:00:00.00
ETL@RACTEST> insert into ttt1 values('e',1);
1 row created.
Elapsed: 00:00:00.00
ETL@RACTEST> select a,b,rowid from ttt1 order byb;
A
-- ---------- ------------------
a
b
c
d
e
Elapsed: 00:00:00.00
这回看到的排序结果很舒服了吧,因为它走的是索引排序。