Home » SQL & PL/SQL » SQL & PL/SQL » Natural order by
Natural order by [message #289707] Tue, 25 December 2007 13:42 Go to next message
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 #289711 is a reply to message #289707] Tue, 25 December 2007 14:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Why do you expect any result if you know "without order by clause, the order of the output is not gurantted"?

Your results are consistent with the rule.

Regards
Michel
Re: Natural order by [message #289713 is a reply to message #289707] Tue, 25 December 2007 15:11 Go to previous messageGo to next message
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 #289723 is a reply to message #289707] Tue, 25 December 2007 22:10 Go to previous messageGo to next message
dude4084
Messages: 222
Registered: March 2005
Location: Mux
Senior Member
Hi



1) Sometime i have to see the tables in SQL and if i want to save my keystrokes or want to keep the thing simple and every table is behaving like this.

Infact, on PC no 2, in cash table, the new record is entering at 31st LAST position. So i have to see above the 30 rows in order to see the new entry.

This 30 rows thing also arose in a very interesting way.i.e
Quote:


Step-1) Created a temp table by create table cash_temp select * from cash order by cashdate, receiptno

Step-2) Drop table cash

Step-3) Create table cash select * from cash_temp



and the number of recrods present in the table during the above steps were 30.

2) I want to learn the new factors as mentioned by Barbara (Given that they are not out of scope of my learning) Razz
Re: Natural order by [message #289728 is a reply to message #289723] Tue, 25 December 2007 22:40 Go to previous messageGo to next message
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 #289758 is a reply to message #289723] Wed, 26 December 2007 01:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
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
Re: Natural order by [message #289928 is a reply to message #289758] Wed, 26 December 2007 11:32 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: update the year in the date
Next Topic: Testing for Numerics???
Goto Forum:
  


Current Time: Sat Feb 15 19:41:38 CST 2025