OraFAQ Forum: SQL & PL/SQL » without orderby clause

Home » SQL & PL/SQL » SQL & PL/SQL » without orderby clause (oracle 9i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

without orderby clause [message #360822]

Mon, 24 November 2008 01:42

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

Report message to a moderator

Re: without orderby clause [message #360825 is a reply to message #360822]

Mon, 24 November 2008 01:50

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

unordered one.

Regards
Michel

Report message to a moderator

Re: without orderby clause [message #360850 is a reply to message #360825]

Mon, 24 November 2008 02:41

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

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Unordered means there is no order so you can say it is randomly ordered.

Regards
Michel

Report message to a moderator

Re: without orderby clause [message #360856 is a reply to message #360852]

Mon, 24 November 2008 02:47

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

Report message to a moderator

Re: without orderby clause [message #360865 is a reply to message #360856]

Mon, 24 November 2008 03:09

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

Report message to a moderator

Re: without orderby clause [message #360868 is a reply to message #360856]

Mon, 24 November 2008 03:34

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: without orderby clause [message #360880 is a reply to message #360856]

Mon, 24 November 2008 04:22

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.

Report message to a moderator

Previous Topic:	string manipulation
Next Topic:	parallel processing in PL/SQL

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jul 11 13:30:37 CDT 2025