Natural order by [message #289707] |
Tue, 25 December 2007 13:42  |
dude4084
Messages: 222 Registered: March 2005 Location: Mux
|
Senior Member |
|
|
Hi
First of all i must mention that i know the rule that "without order by clause, the order of the output is not gurantted every time we run a query".
But i have noticed a starnge thing. My project was running on a PC. When i copied it on another PC, the following thing came in front of me.
I have a simple table cash table.
>Select * from cash;
cashdate receiptno amt
25-DEC-2007 1 100
Then i entered a record through form. After insertion, the table was showing the result as follow
> Select * from cash;
cashdate receiptno amt
25-DEC-2007 2 200
25-DEC-2007 1 100
While i was expecting the following result
> Select * from cash;
cashdate receiptno amt
25-DEC-2007 1 100
25-DEC-2007 2 200
Interestingly, first PC works according to my expectation as shown above !
Can you guide me what has happened? How can i set the "natural order by" ?
|
|
|
|
Re: Natural order by [message #289713 is a reply to message #289707] |
Tue, 25 December 2007 15:11   |
 |
Barbara Boehmer
Messages: 9104 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
In an Oracle relational database, there is no such thing as "natural order by", so there is nothing to set. The data is not stored and/or retrieved in the order entered. There are many factors that affect what order the data might be retrieved in if you do not tell it what order you want by using an order by clause.
[Updated on: Tue, 25 December 2007 15:12] Report message to a moderator
|
|
|
|
Re: Natural order by [message #289728 is a reply to message #289723] |
Tue, 25 December 2007 22:40   |
 |
Barbara Boehmer
Messages: 9104 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
There are too many factors that determine what execution path the optimizer may choose to retrieve the data to predict it. You would spend more time trying to do that than the keystrokes that you save by not using an order by clause. The example that you have provided is a simple one, but I don't know that it is complete. In your original example there were 2 rows, not 30. The execution plan may depend on indexes, statistics, amount of data, and various other things. You can SET AUTOTRACE ON EXPLAIN before running your query to see what it is doing. With such a small data set it may be doing a full table scan or if there is an index that contains all the results it may use that and just read from the index. There are also things that affect what data is stored in what blocks. The possibilities are too numerous. Rather than try to learn what order the optimizer returns things by default, it would be better to learn that THE ONLY WAY TO GUARANTEE THE ORDER RETURNED IS TO USE AN ORDER BY CLAUSE. Also, just because it returns it one way one time, does not mean it will return it the same way the next time.
[Updated on: Tue, 25 December 2007 22:42] Report message to a moderator
|
|
|
|
Re: Natural order by [message #289928 is a reply to message #289758] |
Wed, 26 December 2007 11:32   |
dude4084
Messages: 222 Registered: March 2005 Location: Mux
|
Senior Member |
|
|
Michel Cadot wrote on Wed, 26 December 2007 12:10 |
Quote: | the new record is entering at 31st LAST position.
|
What is 31st position?
The record is inserted somewhere in the table that depends on many and many parameters.
Regards
Michel
|
Respected Michel
Do you what is 2nd last position means? It means 1 position before the last position.
Similarly "31st Last position " means 30 positions before the last position. I hope that i am able to explain the idea.
Barbara >> Thank you for your reply. Yes, i have quaoted a very simple example based on the problem/observation i was facing.
I will if SET AUTOTRACE ON EXPLAIN in oracle 8.
|
|
|
Re: Natural order by [message #289929 is a reply to message #289928] |
Wed, 26 December 2007 11:52   |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
dude4084 wrote on Wed, 26 December 2007 12:32 |
Michel Cadot wrote on Wed, 26 December 2007 12:10 |
Quote: | the new record is entering at 31st LAST position.
|
What is 31st position?
The record is inserted somewhere in the table that depends on many and many parameters.
Regards
Michel
|
Respected Michel
Do you what is 2nd last position means? It means 1 position before the last position.
|
But that's what the problem with a statement like this is. "position" has no meaning in a relational database. Think of it as if the word did not exist.
|
|
|
Re: Natural order by [message #289932 is a reply to message #289928] |
Wed, 26 December 2007 12:16  |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Quote: | Do you what is 2nd last position means? It means 1 position before the last position.
|
I think I should say "define position" or "What is the 31st last apple in my basket?"
Can you tell me?
Regards
Michel
|
|
|