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

From: gazzag <gareth_at_jamms.org>
Date: Thu, 13 Jan 2011 04:28:59 -0800 (PST)
Message-ID: <093a1025-1828-4941-a279-945b96c12b14_at_p8g2000vbs.googlegroups.com>

On Jan 13, 12:17 pm, 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

Oracle does not guarantee the order a result set will be returned without an ORDER BY clause.

From: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b31695/dialogs.htm#sthref494

"Without an ORDER BY clause, no guarantee exists that the same query executed more than once will retrieve rows in the same order."

HTH -g Received on Thu Jan 13 2011 - 06:28:59 CST