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

From: Frank Bergemann <>
Date: Thu, 13 Jan 2011 04:17:29 -0800 (PST)
Message-ID: <>

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!

Frank Received on Thu Jan 13 2011 - 06:17:29 CST

Original text of this message