[ORACLE]有趣的order by
来源:互联网 发布:做网站用java 还是php 编辑:程序博客网 时间:2024/05/19 18:41
在使用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
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