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

From: gazzag <>
Date: Thu, 13 Jan 2011 04:28:59 -0800 (PST)
Message-ID: <>

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


"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

Original text of this message