What to do if the order field value is the same for all records
Everyone, let's say the SQL looks like below.
Select a, b ,c from table1 order by c
If all lines in table1
have the same field value in field c. I want to know if the result is in the same order for every time I execute SQL.
Let's say the data in table1
looks like this.
a b c
-------------------------------------------
1 x1 2014-4-1
....
100 x100 2014-4-1
....
1000 x1000 2014-4-1
....
How does Oracle determine a sequence of strings for the same order by value?
Added
Will they be a random sequence each time?
source to share
One simple answer: NO . There is no guarantee that ORDER BY
at equal values will return the same result every time sorted. It may seem to you that it is always stable, but there are many reasons why it can change.
For example, sorting by equal values might be deferred after:
- Collecting statistics
- Adding an Index to a 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.
Sorting by a column having similar values is as follows:
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.
Thus, similar data in a table bot, however, ORDER BY
in a column with equal values, dos does not guarantee the same sorting.
source to share