Home » SQL & PL/SQL » SQL & PL/SQL » without orderby clause (oracle 9i)
without orderby clause [message #360822] Mon, 24 November 2008 01:42 Go to next message
vishaldba12
Messages: 3
Registered: November 2008
Location: pune India
Junior Member

hi
I want to know which view I will be getting when I will not use the Orderby clause
Re: without orderby clause [message #360825 is a reply to message #360822] Mon, 24 November 2008 01:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
unordered one.

Regards
Michel
Re: without orderby clause [message #360850 is a reply to message #360825] Mon, 24 November 2008 02:41 Go to previous messageGo to next message
vishaldba12
Messages: 3
Registered: November 2008
Location: pune India
Junior Member

hi michel
thanks for your response, well one thing to elaborate
on what basis the view will be sorted

[Updated on: Mon, 24 November 2008 02:42]

Report message to a moderator

Re: without orderby clause [message #360852 is a reply to message #360850] Mon, 24 November 2008 02:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Unordered means there is no order so you can say it is randomly ordered.

Regards
Michel
Re: without orderby clause [message #360856 is a reply to message #360852] Mon, 24 November 2008 02:47 Go to previous messageGo to next message
vishaldba12
Messages: 3
Registered: November 2008
Location: pune India
Junior Member

and everytime I run the sql, I get the same view,
is it because that the recently used sql resultset are stored in buffer cache.

Thanks
Vishal
Re: without orderby clause [message #360865 is a reply to message #360856] Mon, 24 November 2008 03:09 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
hi vishal,

Quote:


I get the same view,





if your table permits duplicate records, insert a duplicate record and insert new valued records too. Hope you shall have different view. (hope you are already familiar with various sort mechanisms adopted on views/engines and ofcourse i am not Shocked )

yours
dr.s.raghunathan
Re: without orderby clause [message #360868 is a reply to message #360856] Mon, 24 November 2008 03:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
and everytime I run the sql, I get the same view,

You can't rely on this even if you got it 10000 times, the next one can be different.

Regards
Michel
Re: without orderby clause [message #360880 is a reply to message #360856] Mon, 24 November 2008 04:22 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Without an Order by clause, there is no guarantee that the results will be returned in a consistent order.
They almost certainly will keep coming back in the same order, but if anything happens to the underlying tables - insert rows, delete rows, update rows, rebuild , etc etc then the order of the rows may change.

As a general rule, you shouldn't put Order By clauses in views - you have no idea what the view will get used for, and the time spend ordering the rows will be wasted if the query looking at the view doesn't care about the order, or imposes it's own order on the data.
Previous Topic: string manipulation
Next Topic: parallel processing in PL/SQL
Goto Forum:
  


Current Time: Thu Dec 08 22:13:09 CST 2016

Total time taken to generate the page: 0.05812 seconds