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

On Jan 13, 4:17*am, Frank Bergemann <FBergem... (AT) web (DOT) de> wrote:
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.
http://download.oracle.com/docs/cd/E...htm#CNCPT89017

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
--
@home.com is bogus.
http://www.eweek.com/c/a/Database/Or...s-2000-693441/

On 13 Jan., 18:56, joel garry <joel-ga... (AT) home (DOT) com> wrote:
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

On Jan 13, 11:01*am, Frank Bergemann <FBergem... (AT) web (DOT) de> wrote:
I can't remember where I've seen the answer, but basically IIRC it is
"that's the way it works if you need them grouped together, it has to
look at all of them first." Might have been in
http://asktom.oracle.com/pls/asktom/...4 81671347143
but I don't have time to search just now. Or maybe on oracle-base.com

You might look at whether the sort has to spill to disk, though. The
terms to search for are probably optimal, one-pass and multipass
executions in the pga workarea.

jg
--
@home.com is bogus.
http://www.zdnetasia.com/wikileaks-e...c-62201576.htm

With rule-based it was quite easy: you made sure an index on that column was used and the
results returned in the desired order, immediately.

But if you needed the full result set, that was rarely the fastest way.