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

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

There is no guarantee of order in SQL. Best to think of sets of data and note that a set does not have any sense of order.

Note that there is always a performance hit for ordering data, where that is because you have to explicitly request an order by or because the underlying engine automatically does it. The fact most SQL engines only do it when requested is a good thing as it avoids the overhead when it isn't necessary.

I don't think that adding the order by will necessarily mean ALL records any more than not having the order by. i.e. if your query was not limiting the range of values for the join column then all records would be selected regardless. If it does limit the rose, only those rows that remain will be ordered.

Tim

-- 
tcross (at) rapttech dot com dot au