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 09:56:54 -0800 (PST)
Message-ID: <c3caf6da-b788-4901-a174-3af6b417650c_at_g26g2000vba.googlegroups.com>



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/logical.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

--
_at_home.com is bogus.
http://www.eweek.com/c/a/Database/Oracle-Ends-MySQL-Support-for-Windows-2000-693441/
Received on Thu Jan 13 2011 - 11:56:54 CST

Original text of this message