Problems caused by the same median in Oracle Sequence)

This queston came up on the Oracle newsgroup a few days ago:

This issue was raised in Oracle's press center for a period of time:

 

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

There is a table (the table name is Policy) with three fields: a, B, and c. The table has two rows, and the data in Column c is always 0, the SQL statement that I run

   policy   c;

As both the rows have a value of zero, the result shocould be sorted ascending by rowid, but I see the opposite; viz. the result set is sorted descending by rowid.

The results should be sorted in ascending order by rowid, but on the contrary, the results are sorted in descending order by rowid.

 

Is that an issue with the version of 10g server, I am using or is it some settings of the Oracle server?

This is a problem of 10 Gbit/s, a problem of my use, or a problem of setup?

 

Various people replied to say that you shoshould 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.

Others say it is better to explicitly declare the sorting conditions, such as rowid desc. However, this question raises an interesting point of view.

 

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:

When you encounter a problem, it is best to write a test example, for example, the following example runs on 10.2.0.1:

   t1 (a , b , c   t1 (,,  t1 (,, t1., t1.rowid  t1                                                   

The two rows of data are queried, and the data sorting is incorrect.

 

So what do you do next? The first couple of ideas are: add a third, fourth and second th row to see if the "descending order" observation is accurate; then try running the test on a different version of Oracle.

What are you going to do next? The first idea is to add the data in the third, fourth, and fifth rows to check whether the "descending order" is accurate and then run in different versions of oracle.

 

Here's the output from 10.2.0.1, after adding more and more rows:

The following result set adds the data of the third, fourth, and fifth rows to 10.2.0.1 and queries the result.

                                                                                                                                                                                                                                                                            AAATncAAGAAABSKAAC

The results are NOT in descending order of rowid-it just looks that way in the very first case.

The result is not sorted in descending order by rowid,

 

But here's the output from the same test running on 9.2.0.8:

The same test runs in 9.2.0.8:

                                                                                                                                                                                                                                                                                                                             AAALJkAAJAAABIKAAE

In 9.2.0.8, rowid is sorted in ascending order.

 

The Answer

Oracle introduced a new sorting algorithm (sometimes known as the Version 2 sort, which is how it is labeled in the 10032 trace) in 10.2.

Answer

Oracle 10.2 introduces a new sorting algorithm called version 2.

 

The previous algorithm was implements tively 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 ).

The preceding example effectively constructs an in-memory index. The input data uses a balanced binary tree and (that is, optimizes the order in which the data appears and maintains the order in which the data appears, the obvious example is the 9i query result)

 

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.

The cpu and memory overhead of this algorithm is a little large, so the new algorithm has made some changes. A variation similar to heap sorting, but not heap sorting. It also has a side effect, that is, re-Sort rows.

 

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.

People know that their sorting algorithm is good, but I don't want to be a mouse.

 

If you want to disable the new sorting mechanic, there is a hidden parameter to affect it. as usual, you shouldn't use hidden parameters without first inserting ing confirmation from Oracle support that you need to, but the relevant parameter is: _ newsort_enabled, which defaults to true in 10g.

If you want to disable the new sorting mechanism, there is a hidden parameter "_ newsort_enabled". The default value is true.

 

 

Source: http://jonathanlewis.wordpress.com/2007/06/03/sorting/

References: http://blog.sina.com.cn/s/blog_6ff05a2c0100mlco.html