Skip to Main Content
  • Questions
  • External table - order of returned rows

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, TomazD.

Asked: March 31, 2021 - 12:28 pm UTC

Last updated: April 12, 2021 - 5:22 am UTC

Version: 12.2

Viewed 1000+ times

You Asked

Hello,

rather basic question.

Does database guarantee to return rows from external table, OS file, in the same order as they are positioned in file.

I am well aware that only proper order by clause can achieve this. But we have some 10+ years old database job which was migrated from 10.2 to 11.2 to 12.2 during these years and it was always returning rows in order of external file till version 12.2.

External table is joined to two other tables but sql is without order by clause.


Regards, Tomaž

and Chris said...

No - as with any query, the only want to guarantee rows return in a particular sequence is to use order by!

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Discussion on this topic

AndyP, April 06, 2021 - 2:12 pm UTC

Hi Tomaz,
You might like to look at this thread:
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4347075367803 and search for "query on external table".

I'm not sure it's conclusive, but there are some opinions there as to expectations on ordering
Connor McDonald
April 12, 2021 - 5:22 am UTC

Agreed.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.