alcsoft
asked on
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!
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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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.
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.
SQL works in set-based operations. Sets have no ordering.