When does return order change if no ORDER BY used?

[GremlinHunter]

Basic question is when does the order of a query return change when no order by is used? I think the DB used the row number as default order but it has been years since I took the class.

Reason is we have a routine that dumps daily partitions into csv's for archival. However the routine is getting the column order from dba_tab_columns with no reference to column_id. As it is I am not positave how many of the archived csv's have invalid column orders...

 

[Turkbear]

Hi,
without an Order By clause, there is no guarantee as to the order of records returned by a query..The Optimizer will try to determine the most efficient method of retreiving records and this may/will change with changes to the data..In theory, a READ-ONLY table should repeat the order every time, but even that is not certain..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[SantaMufasa]

...the routine is getting the column order from dba_tab_columns with no reference to column_id...

So, GH, are you enquiring about row order or are you asking about column order?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[GremlinHunter]

Turkbear - That is what I was afraid of. Staying with the same query as the routine seems to give me matching results. So far the only ones that are showing out of order were from new columns added into the tables. That is what had me wondering on the row number thing. I thought I remembered from my old DBA class that if something was deleted from the table at a higher postion then it was possible for new data end up ahead of older data. If that makes any sense... I have slept a thousand or so times since then.


SantaMufasa - The row order from dba_tab_columns is used to build the select for the csv file. Since the origional select against dba_tab_columns did not take into account the column_id field the csv can end up with erratic column orders in it.