Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Import and export in Oracle different row orders in the table

Posted on 2012-03-30
7
Medium Priority
?
457 Views
Last Modified: 2012-09-02
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!
0
Comment
Question by:alcsoft
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
7 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 700 total points
ID: 37787728
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
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 700 total points
ID: 37787735
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 37787745
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
 
LVL 1

Expert Comment

by:brentgee
ID: 37788363
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 37788476
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

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question