Re: defined order of records for SQL query without explicit instruction (order by)?
Date: Thu, 13 Jan 2011 11:01:30 -0800 (PST)
Message-ID: <7ce4d105-7b03-4355-9c9f-87856ca1a280_at_u32g2000yqe.googlegroups.com>
On 13 Jan., 18:56, joel garry <joel-ga..._at_home.com> wrote:
> On Jan 13, 4:17 am, 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
>
> 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/E11882_01/server.112/e16508/logica...
>
> 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
> --
> _at_home.com is bogus.http://www.eweek.com/c/a/Database/Oracle-Ends-MySQL-Support-for-Windo...
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