6

All, Let's say the SQL looks like below.

Select a, b ,c from table1 order by c

If all the rows in table1 have the same field value in the field c. I want to know if the result has the same order for each time I executed the SQL.

Let's say data in the table1 looks like below.

a              b                 c 
-------------------------------------------
1              x1                 2014-4-1
....
100            x100               2014-4-1 
....
1000           x1000              2014-4-1
....

How Oracle determine the rows sequence for the same order by value?

Added

Will they be random sequence for each time?

Joe.wang
  • 11,537
  • 25
  • 103
  • 180

2 Answers2

7

One simple answer is NO. There is no guarantee that the ORDER BY on equal values will return the same sorted result every time. It might seem to you it is always stable, however, there are many reasons when it could change.

For example, the sorting on equal values might defer after:

  1. Gathering statistics
  2. Adding an index on the column

For example,

Let's say I have a table t:

SQL> SELECT * FROM t ORDER BY b;

         A          B
---------- ----------
         1          1
         2          1
         3          2
         4          2
         5          3
         6          3

6 rows selected.

The sorting on the column having similar values is just like:

SQL> CREATE TABLE t1 AS SELECT * FROM t ORDER BY b, DBMS_RANDOM.VALUE;

Table created.

SQL> SELECT * FROM t1 ORDER BY b;

         A          B
---------- ----------
         1          1
         2          1
         4          2
         3          2
         5          3
         6          3

6 rows selected.

So, similar data in bot the tables, however, ORDER BY on the column having equal values, dos not guarantee the same sorting.

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
1

They must not be random (change each time), but the order is not guaranteed (change sometimes).

Pavel Gatnar
  • 3,987
  • 2
  • 19
  • 29