Re: defined order of records for SQL query without explicit instruction (order by)?

From: onedbguru <onedbguru_at_yahoo.com>
Date: Thu, 13 Jan 2011 18:20:21 -0800 (PST)
Message-ID: <85fd6a37-c786-433b-8570-e291b2825753_at_e20g2000vbn.googlegroups.com>



On Jan 13, 7:17 am, Frank Bergemann <FBergem..._at_web.de> wrote:
> if i select data from #2 joined tables like this:
>
> select a.x, b.z from table a, b
> where a.x = b,x;
>
> And let a.x is unique, while b.x isn't uniq.
>
> Can i rely on getting record from b, which have a certain value b.x,
> one after another?
> Or is (oracle) "allowed" to deliver results in undefined order here?
>
> Because if oracle is free to deliver in any order it would require to
> use
>
> select a.x, b.z from table a, b
> where a.x = b,x
> order by b.x;
>
> Which i currently do - for safety reasons.
>
> But using 'oder by' seems to have the issue, to
> 1. fetch ALL records then
> 2. order record before
> 3. hand records over to recipient.
>
> And that again is a performance issue, if using parallel pipelined
> functions as consumers.
> Because it creates a a delay between SQL query and startup of worker
> threads for the parallel pipelined functions (using SQL query cursor
> as argument).
>
> - thanks!
>
> rgds,
> Frank

As others have stated there is no guarantee of order. If you are having performance issues, you might want to make a.x and b.x more selective by adding another column to the selectivity?

select a.x,b.z from a a join b b on a.x=b.x where a.? order by ...

Not knowing the full scope of your app makes it difficult to give you a full and complete analysis to fix your problem. Received on Thu Jan 13 2011 - 20:20:21 CST

Original text of this message