Oracle SQL -Query result was not ordered based on Index

Question

I have a table with 5 columns,

column1, column2, column3, column4, column5
of which column1, column2 and column3 constitute 'primary key'

temp_table

Column1 Column2 Column3 Column4 Column5

2 209 Raj Kumar K

2 27 Arvind Ram R

2 227 Mahesh Kumar M

whenever i query the table, the results would be ordered by the primary key columns even if i do not give order by in the query.

Select Column1, Column2, Column3 from temp_table;

every time i run this query i do get the result as,

Column1 Column2 Column3

2 27 Arvind

2 209 Raj

2 227 Mahesh

But at one particular instance only, the query result was not ordered by primary key columns.

The result was like below,

Column1 Column2 Column3

2 209 Raj

2 27 Arvind

2 227 Mahesh

Can somebody help to find the reason why.

Possible duplicate of [Default row ordering for select query in oracle](https://stackoverflow.com/questions/899514/default-row-ordering-for-select-query-in-oracle) — are, Oct 03 '17 at 06:42
generally oracle produces the result set in "inserting" or "accessing" order or random... ;) — Thomas, Oct 03 '17 at 08:21
This has been proposed as a duplicate of a question discussing whether there is a default order by. However to be a duplicate, that other question must actually answer what is asked there. None of the answers go into why there is no default, which is the key point here. I think a good answer to this question would discuss why it's better for a database like Oracle not to assume a default. So I don't think this is a dup — Sam Hartman, Oct 03 '17 at 15:32

score 0 · Answer 1 · answered Oct 03 '17 at 06:38

even if i do not give order by in the query

You should always use an ORDER BY clause if you expect to have reproducible and deterministic ordering in a result set. You should not assume any inherent order in a SQL table, as tables are modeled after (unordered) sets. As to why the ordering changed in that one instance, perhaps the way the underlying data was being stored, or maybe cached, was different for that one instance. But in any case, use you should always use ORDER BY if you expect a certain order.

score 0 · Answer 2 · answered Oct 03 '17 at 06:39

You have to use ORDER BY like :

Select Column1, Column2, Column3 
from temp_table
order by Column?;

And choose the column you want. You can also have DESC and ASC in the end like :

Select Column1, Column2, Column3 
from temp_table
order by Column? ASC;

For ordinates decreasing or increasing.

Oracle SQL -Query result was not ordered based on Index

2 Answers2