Home » SQL & PL/SQL » SQL & PL/SQL » WAY OF STORING DATA IN ORACLE (gendral)
WAY OF STORING DATA IN ORACLE [message #386908] Tue, 17 February 2009 04:24 Go to next message
good
Messages: 27
Registered: July 2000
Junior Member
Hi,

I have question, I am loading data from flat file to oracle staging table through ETL. The data is in the same order like what it was there in the flat file. But my question is that when I transfer the data from staging table to production table through query, the order get changed ? why ? what logic the oracle read the data from staging table ?

If you have a answer to this query, please let me know.

thanks in advance.

Re: WAY OF STORING DATA IN ORACLE [message #386911 is a reply to message #386908] Tue, 17 February 2009 04:36 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
There is no order followed by oracle, Only user can specify the order of the data by ORDER BY CLAUSE.

Thanks
Trivendra
Re: WAY OF STORING DATA IN ORACLE [message #386912 is a reply to message #386908] Tue, 17 February 2009 04:37 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Rows in a table have no inherent order.
A query with no order by clause can return data in any order.
If you want data displayed in a particular order you need to use an order by clause.
Re: WAY OF STORING DATA IN ORACLE [message #386914 is a reply to message #386908] Tue, 17 February 2009 04:46 Go to previous messageGo to next message
good
Messages: 27
Registered: July 2000
Junior Member
I think people doesn't understand my question, let me try to explain litle bit more ...

If, I use a select statement to select the staging table it display the record which in the same order what was there in the flat file (note: I am not taking about the order by clause)

Then, by using the query populate the data to production table from staging table,now if i use the simple select statement which i used to select the staging table, the order of the row was different from staging or flat file, why ?

thanks.
Re: WAY OF STORING DATA IN ORACLE [message #386916 is a reply to message #386914] Tue, 17 February 2009 04:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What don't you understand to the answer:
Quote:
Rows in a table have no inherent order.

If there is no order, then there is no reason to have an order, whatever you think the order should be or was.

Regards
Michel
Re: WAY OF STORING DATA IN ORACLE [message #386919 is a reply to message #386908] Tue, 17 February 2009 04:52 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
We did understand the question.
The answer is the same. Sometimes you get data back in the order it was inserted but that is not guaranteed.
Re: WAY OF STORING DATA IN ORACLE [message #386977 is a reply to message #386914] Tue, 17 February 2009 07:54 Go to previous message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
good wrote on Tue, 17 February 2009 05:46

If, I use a select statement to select the staging table it display the record which in the same order what was there in the flat file


Pure coincidence. Read all of the other responses. Understand them. Don't say you do and then bring up the question again.
Previous Topic: Escape special character !! (merged)
Next Topic: Numbering groups of consecutive dates
Goto Forum:
  


Current Time: Thu Dec 08 18:33:21 CST 2016

Total time taken to generate the page: 0.10045 seconds