Import and export in Oracle different row orders in the table

Hi,


I am trying to understand the import process in oracle here!

So I have a PROD and TEST env.

I did export from PROD and imp into TEST.

I have a table with many rows.

when I do on TEST
SELECT *
FROM TABLE1;

I got different row set order than what I got in PROD!

So, my question is that, why it is different? I am just wondering and I probably I can use ORDER BY to get the same, but I am curious about the results without order by clause!
alcsoftAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
The only way in Oracle to guarantee row order is to use an order by.

Select order w/o the order by has no meaning.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sdstuberCommented:
that makes sense.

the physical order of the data has no reliable influence on the result set.

you may have parallel operations that read data in chunks and the merging of those produced different ordering

if you want your data to be sorted a certain way, the ONLY way that is reliable is to use an ORDER BY

everything else, even if it looks like it returns the same results a million times in a row has no bearing on future results.
0
sdstuberCommented:
note, that's not just an Oracle thing.  The same is true (or at least, is "supposed" to be true) for any relational database.

SQL works in set-based operations.  Sets have no ordering.
0
brentgeeCommented:
the table record selects all have an associated rownum with each record. this is a pseudocolumn and is how oracle selects the records in sql. the order by clause will order it after the selection process. it doesn't matter how the data is inserted, it only matters how it is selected.

if you do an explain plan on the query in each instance, you probably will see slight differences in the plan. this is how the data is selected and thus the rownum will be different.
0
sdstuberCommented:
brentgee

while what you said about rownum and order by is true,  it doesn't really apply to to this question.

as you can see in the question, rownum is not being queried, so the impact or lack of impact of an order by with rownum is irrelevant.


perhaps you meant rowid instead of rownum, but even so, that's sort of irrelevant as well.
If you select data sorted by rowid and then insert it you still won't have a guarantee of similarly sorted results unless you specify an ORDER BY.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.