Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: November 26, 2015 - 9:16 am UTC

Last updated: November 26, 2015 - 10:44 am UTC

Version: Oracle 12

Viewed 1000+ times

You Asked

Hello Tom,

could you comment please on the following topic that still does not have (to my mind) a clear answer?

Suppose you have some table MYTABLE and a simple query (no order by)
select * from MYTABLE
that gives some result set back.

If we create a dump of the database and import this dump into another Oracle instance,
can we expect, that the same query will return records in the same order as in the original database?

What if we export from Oracle 9i and import into Oracle 12c?

Regards,

Vladimir

and Chris said...

No. To ensure that data returns in a given order, you must specify an order by clause. From the docs:

Use the ORDER BY clause to order rows returned by the statement. Without an order_by_clause, no guarantee exists that the same query executed more than once will retrieve rows in the same order.


http://docs.oracle.com/database/121/SQLRF/statements_10002.htm#SQLRF20039

This applies whether you export/import to another database, or just run the same query within the same db.

If you need your data in a given order, use order by!

Rating

  (2 ratings)

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

Comments

from Tomkyte blog

Rajeshwaran, Jeyabal, November 26, 2015 - 12:43 pm UTC

http://tkyte.blogspot.com/2005/08/order-in-court.html

Thanks

vbe, November 26, 2015 - 1:48 pm UTC

Thank you very much.
Regards,
Vladimir
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.