使用order by排序时Oracle的排序算法是非稳定的排序算法吗？

Yong Huang

liujiannan047,

You should always tell us Oracle version.

Can you try your test after you set the following parameter, if you're using 10gR2 or newer?

alter session set "_newsort_enabled" = false;

Please read
http://jonathanlewis.wordpress.com/2007/06/03/sorting/

Yong Huang

[ 本帖最后由 Yong Huang 于 2010-8-25 13:08 编辑 ]

liujiannan047

原帖由 Yong Huang 于 2010-8-26 03:06 发表
liujiannan047,

You should always tell us Oracle version.

Can you try your test after you set the following parameter, if you're using 10gR2 or newer?

alter session set "_newsort_enabled" = false;

Please read
http://jonathanlewis.wordpress.com/2007/06/03/sorting/

Yong Huang

首先感谢Yong Huang告诉了我Oracle排序算法随着版本在不断改进。我试着用你给出的方法又做了一次，不过结果没有改变：
ETL@RACTEST> select a,b,rowid from ttt1;

A           B ROWID
-- ---------- ------------------
a           1 AAE39tAAJAAACg1AAA
b           1 AAE39tAAJAAACg1AAB
c           1 AAE39tAAJAAACg1AAC
d           1 AAE39tAAJAAACg1AAD

Elapsed: 00:00:00.02
ETL@RACTEST> select a,b,rowid from ttt1 order by b;

A           B ROWID
-- ---------- ------------------
a           1 AAE39tAAJAAACg1AAA
d           1 AAE39tAAJAAACg1AAD
c           1 AAE39tAAJAAACg1AAC
b           1 AAE39tAAJAAACg1AAB

Elapsed: 00:00:00.01
ETL@RACTEST> alter session set "_newsort_enabled" = false;

Session altered.

Elapsed: 00:00:00.00
ETL@RACTEST> select a,b,rowid from ttt1 order by b;

A           B ROWID
-- ---------- ------------------
a           1 AAE39tAAJAAACg1AAA
d           1 AAE39tAAJAAACg1AAD
c           1 AAE39tAAJAAACg1AAC
b           1 AAE39tAAJAAACg1AAB

Elapsed: 00:00:00.00
ETL@RACTEST> alter session set "_newsort_enabled" = true;

Session altered.

Elapsed: 00:00:00.01
ETL@RACTEST> select a,b,rowid from ttt1 order by b;

A           B ROWID
-- ---------- ------------------
a           1 AAE39tAAJAAACg1AAA
d           1 AAE39tAAJAAACg1AAD
c           1 AAE39tAAJAAACg1AAC
b           1 AAE39tAAJAAACg1AAB

Elapsed: 00:00:00.00




以下是版本信息：
ETL@RACTEST> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Solaris: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production