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

Breadcrumb

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 100+ 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 we 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)

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.