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

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 14 Jan 2011 09:31:55 -0000
Message-ID: <ldOdnTqsh5V9iq3QnZ2dnUVZ7oadnZ2d_at_bt.com>

"Frank Bergemann" <FBergemann_at_web.de> wrote in message news:6764e920-b626-4e07-9d34-9085b259b80b_at_j25g2000yqa.googlegroups.com...
>
> select a.x, b.z from table a, b
> where a.x = b,x
> order by b.x;
>

As others have pointed out, you HAVE to have the "order by" clause to guarantee the data will be in order. This does not mean, however, that Oracle has to sort the data if it can find another way of ordering it without sorting. In your example, for instance, it you had the correct indexes you could push the optimizer down an index-driven path:

select /*+ leading (a b) use_nl(b) index(a unique_index) index(b join_index) */

    a.x, b.z
from table a, b
where

        a.x is not null
and a.x = b,x
order by a.x;

The plan you got from this would probably show something like "sort order by (nosort)" as the ordering operation.

> 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).

The plan that avoids the sorting may still not work for you with your 'parallel pipeline' because it can only run as a serial process to avoid sorting - perhaps you can parallelise by creating a mechanism that allows each parallel thread to change the "a.x is not null" to "a.x between M and N".

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Received on Fri Jan 14 2011 - 03:31:55 CST