Hello Team,
I understand that without specifying ORDER BY clause, Oracle doesn't guarantee sort order of a select query. However, I am trying to find reason for a particular situation am facing in my company

We have a production database (let's say ORMP) and for development purpose, we have 4 Dev databases ORMD/T/OCMD/T, which is a copy of ORMP on different timeframe. We have a table TABLE1 in ABC schema in each one of these databases. This table has many columns, let's say COL1, COL2, COL3 etc, where COL1 is populated by a sequence and is primary key

We are running a query as below in 4 of these Dev databases, all are on same oracle version 11g R2

SELECT COL1, COL2, COL3 from ABC.TABLE1 WHERE COL3 = 'TEST' ORDER BY COL2;

This query returns data in increasing order of COL1 (sequence Id column) in first 3 databases but not in 4th one. We repeated this operation multiple times, however we get the result in same order as mentioned above

I have explained to the team that sort order cannot be guaranteed on COL1 since it is not included in ORDER BY clause, however I am not able to explain why we get data in the same order in 3 databases except last one.

Could you please share your thoughts on how to explain this behaviour?

Please let me know if you need more information.

Thanks,
Manoj

create table t as 
  select level c1, 1 c2 
  from   dual
  connect by level <= 10
  order by level;
  
select * from t
order  by c2;

C1    C2   
    1     1 
    2     1 
    3     1 
    4     1 
   10     1 
    6     1 
    7     1 
    8     1 
    9     1 
    5     1 
  
drop table t purge;
create table t as 
  select level c1, 1 c2 
  from   dual
  connect by level <= 10
  order by level desc;
  
select * from t
order  by c2;

C1    C2   
   10     1 
    9     1 
    8     1 
    7     1 
    1     1 
    5     1 
    4     1 
    3     1 
    2     1 
    6     1