Re: defined order of records for SQL query without explicit instruction (order by)?
From: joel garry <joel-garry_at_home.com>
Date: Thu, 13 Jan 2011 16:20:15 -0800 (PST)
Message-ID: <ec25feb1-7b33-4abf-9945-b4c5d6191ec6_at_f35g2000vbl.googlegroups.com>
On Jan 13, 11:01 am, Frank Bergemann <FBergem..._at_web.de> wrote:
> 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
Date: Thu, 13 Jan 2011 16:20:15 -0800 (PST)
Message-ID: <ec25feb1-7b33-4abf-9945-b4c5d6191ec6_at_f35g2000vbl.googlegroups.com>
On Jan 13, 11:01 am, Frank Bergemann <FBergem..._at_web.de> wrote:
> 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
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/f?p=100:11:2759293522499671::::P11_QUESTION_ID:19481671347143 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
-- _at_home.com is bogus. http://www.zdnetasia.com/wikileaks-estranged-co-founder-becomes-a-critic-62201576.htmReceived on Thu Jan 13 2011 - 18:20:15 CST