oracle 排序值相等排名相等,Oracle中排序列中值相同引发的问题（译）

This queston came up on the Oracle

newsgroup a few days ago:

这个问题在Oracle的新闻中心被提出了一段时间：

I have a table (call it policy) with three

columns a, b and c. The table has two rows, with column c having value zero for

both rows. I run the following query

有个表(表名是Policy)，有三个字段：a、b、c，这个表有两行，c列中的数据始终为0，我运行一下的sql语句

select * from policy order by c;

As both the rows have a value of zero, the

result should be sorted ascending by rowid, but I see the opposite;  viz.

the result set is sorted descending by rowid.

照理说，结果应该按照rowid来升序排序，但是相反的是，结果却按照rowid降序排序。

Is that an issue with the version of 10g

server, I am using or is it some settings of the Oracle server?

这个是10g的问题，还是我使用的问题，或者还是设置的问题？

Various people replied to say that you

should never assume any ordering beyond the order you explicitly state in the

order by clause. But the question does raise a couple of interesting

points.

其他的人说，最好是显式的声明排序的条件，比如rowid desc。但是这个问题引发了一个有趣的观点。

Let’s start by running the test (it’s not

hard to write up a test case, so why not do so when you ask the question). The

following is good enough – and I’ve appended the output of the query when

running on 10.2.0.1:

当你遇到问题的时候，最好写一个测试的例子，例如下面的例子，运行在10.2.0.1上：

drop tablet1;create table t1 (a number, b number, c number);insert into t1 values(1,1,0);insert into t1 values(1,1,0);commit;select t1.*, t1.rowid from t1 order byc;

A B C ROWID---------- ---------- ---------- ------------------

1 1 0AAATncAAGAAABSKAAB1 1 0AAATncAAGAAABSKAAA

2 rows selected.Sure enough, the results are in the

“wrong” order.

两行数据被查询出来，果然数据排序错误。

So what do you do next ? The first couple

of ideas are: add a third, fourth and fifth row to see if the “descending order”

observation is accurate; then try running the test on a different version of

Oracle.

接下来你要怎么做？第一个想法是，添加第三行、第四行、第五行数据，查看“descending

order”是否准确，然后运行在不同版本的oracle中。

Here’s the output from 10.2.0.1, after

adding more and more rows:

下面的结果集是在10.2.0.1中添加第三行、第四行、第五行数据，并查询的结果

A B C ROWID---------- ---------- ---------- ------------------

1 1 0AAATncAAGAAABSKAAA1 1 0AAATncAAGAAABSKAAC1 1 0AAATncAAGAAABSKAAB

A B C ROWID---------- ---------- ---------- ------------------

1 1 0AAATncAAGAAABSKAAA1 1 0AAATncAAGAAABSKAAD1 1 0AAATncAAGAAABSKAAC1 1 0AAATncAAGAAABSKAAB

A B C ROWID---------- ---------- ---------- ------------------

1 1 0AAATncAAGAAABSKAAA1 1 0AAATncAAGAAABSKAAB1 1 0AAATncAAGAAABSKAAE1 1 0AAATncAAGAAABSKAAD1 1 0 AAATncAAGAAABSKAAC

The results are NOT in descending order of

rowid – it just looks that way in the very first case.

结果是并没有按照rowid进行降序排序，

But here’s the output from the same test

running on 9.2.0.8:

同样的测试运行在9.2.0.8：

A B C ROWID---------- ---------- ---------- ------------------

1 1 0AAALJkAAJAAABIKAAA1 1 0AAALJkAAJAAABIKAAB

A B C ROWID---------- ---------- ---------- ------------------

1 1 0AAALJkAAJAAABIKAAA1 1 0AAALJkAAJAAABIKAAB1 1 0AAALJkAAJAAABIKAAC

A B C ROWID---------- ---------- ---------- ------------------

1 1 0AAALJkAAJAAABIKAAA1 1 0AAALJkAAJAAABIKAAB1 1 0AAALJkAAJAAABIKAAC1 1 0AAALJkAAJAAABIKAAD

A B C ROWID---------- ---------- ---------- ------------------

1 1 0AAALJkAAJAAABIKAAA1 1 0AAALJkAAJAAABIKAAB1 1 0AAALJkAAJAAABIKAAC1 1 0AAALJkAAJAAABIKAAD1 1 0 AAALJkAAJAAABIKAAE

So is seems more likely that

there is a sorting effect (possibly accidental) on rowids in 9.2.0.8.

在9.2.0.8中，是按照rowid进行了升序排序

The Answer

Oracle introduced a new sorting algorithm

(sometimes known as the Version 2 sort, which is how it is labelled in the 10032

trace) in 10.2.

答案

Oracle 10.2引入了一个新的排序算法，称为版本2。

The previous algorithm was effectively

building an in-memory index on the incoming data using a balanced binary tree

and seeking to the righ (i.e. optimised towards data that appeared in the

correct order and keeping such data in the order of appearance - hence the

apparent sorting of rowids in our example in 9i).

前面的例子有效的构建一个内存中索引输入数据使用平衡二叉树和(即优化对数据出现在正确的顺序和保持这些数据出现的顺序，明显的例子就是9i的查询结果)

The CPU and memory overheads for this

algorithm are a bit fierce for large sorts, so the new algorithm does something

completely different (possibly based on a variant of the heapsort, though it

isn’t actually a heapsort) which is more efficient on memory and CPU. It has the

side-effect though, of re-ordering incoming rows even when the data is not

arriving out of order.

这个算法的cpu和内存开销有点大，所以新的算法做了一些改变。类似于堆排序的一种变体，但不是堆排序。它也有副作用，就是重新排序行

Someone who knew their sorting algorithms

really well might even be able to infer the algorithm Oracle was using by

extending the test case and watching the rowids re-ordering themselves as the

result set grows. But I’m not going to volunteer for that task.

人们知道他们的排序算法很好，但是我不愿意做小白鼠。

If you want to disable the new sorting

mechanism, there is a hidden parameter to affect it. As usual, you shouldn’t use

hidden parameters without first receiving confirmation from Oracle support that

you need to, but the relevant parameter is: _newsort_enabled, which defaults to

true in 10g.

如果你想禁用新的排序机制，有个隐藏的参数“_newsort_enabled”，默认为true。

原文：http://www.cnblogs.com/smallidea/p/3624338.html