Re: OR and sequence of results

There'll be always only one solution to get sorted output: take the order by clause. There's no need to 'fix' anything. When using cost based optimizer (and indexes are regularly analyzed) the "order" of records returned (without using order by) may constantly vary; may be at one point Oracle uses a full table scan and a few months later the same query will be executed using indexes - you never know unless specifying hints in your query.

Regards

Klaus
<rjones_at_cincom.com> schrieb in im Newsbeitrag: 8kfbjl$kr5$1_at_nnrp1.deja.com...
> I've recently noticed that in the absence of any other sequencing, the
> position of OR statements will influence the sequence of results for a
> simple query. Take a single column table X with values 'A', 'B'
> and 'C', the query...
>
> select rownum, col from x where col = 'A' or col = 'B' or col = 'C'
>
> ...will give:
>
> ROWNUM COL
> --------- ---
> 1 C
> 2 B
> 3 A
>
> But...
>
> select rownum, col from x where col = 'C' or col = 'B' or col = 'A'
>
> ...will give:
>
> ROWNUM COL
> --------- ---
> 1 A
> 2 B
> 3 C
>
> This doesn't seem to be documented anywhere. It's a potentially useful
> feature, if indeed it is a feature is not going to be 'fixed' in the
> next release.
>
> Any thoughts???
>
> Rod
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Tue Jul 11 2000 - 00:00:00 CDT