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

From: Frank Bergemann <>
Date: Thu, 13 Jan 2011 11:01:30 -0800 (PST)
Message-ID: <>

On 13 Jan., 18:56, joel garry <> wrote:
> On Jan 13, 4:17 am, 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
> Consider this:
> You have blocks with rows in them.  Say one block is almost full, and
> you update one row so that it is too big to fit in the block.  It goes
> to some other block, right?  That's row migration.
> Now imagine you are getting a whole lot of rows from that table,
> including the one you updated and the ones before and after it.  Don't
> you think it might be faster for Oracle to give you them out of order
> if you didn't specifically ask for them in order?  Now imagine someone
> else has a transaction that started long before you updated the row,
> and is now doing direct reads off the disk - is it possible that
> Oracle already updated what is on disk, and has to go into the SGA to
> find the older block, or even reconstruct it from undo records on
> disk?
> When you start considering these concurrency issues, it makes a lot of
> sense for Oracle to do what it does.  Never depending on the order of
> rows is very basic, in the last century it was on the first page of
> the app developers guide IIRC, and still there were myths about group
> by doing ordering.
> jg
> --
> is bogus.

okay i understand this.
But what is then the efficient approach to forward n times Xi selected records to worker threads being implemented as parallel pipelined functions (btw. i am using cluster by column <x> instead of order by for the select cursor)?
I mean i have Xi records per n transactions, for which i need to take care, that i'll have some all-over tx mgt. 'cluster' could/should be weaker than 'order by' because i don't care of all records value x=5 are before or after all records of value x=7. But still it seems to force oracle to read ALL the data first, then dispatch to the parallel worker threads. I.e. i can't make it continuously dispatch records while executing the data selection.
Hope you can get my point. Maybe i need to post some example.... rgds,
Frank Received on Thu Jan 13 2011 - 13:01:30 CST

Original text of this message