Re: OR and sequence of results

Your result comes from a unique index or a PK on col. If you have not an index, the plan is a full scan and then the results of the 2 queries are the same one. With the index, Oracle generates a concatenation and, as the syntax analysis begins at the end of the statement you get, the result from the last part to the first part of the query like in the case of an union:

v734> create table t (col varchar2(2));
v734> insert into t values ('C');
v734> insert into t values ('A');
v734> insert into t values ('B');
v734> commit;
v734> select * from t;

CO

--
C
A
B

3 rows selected.

v734> select rownum, col from t where col='A' or col='B' or col='C';

    ROWNUM CO
---------- --
         1 C
         2 A
         3 B

3 rows selected.

v734> select rownum, col from t where col='C' or col='B' or col='A';

    ROWNUM CO
---------- --
         1 C
         2 A
         3 B

3 rows selected.

v734> explain plan for select rownum, col from t where col='A' or col='B' or col='C';

Explained.
...
SELECT STATEMENT
  COUNT
    TABLE ACCESS FULL T

v734> create unique index t_i on t(col);

Index created.

v734> select rownum, col from t where col='A' or col='B' or col='C';

    ROWNUM CO
---------- --
         1 C
         2 B
         3 A

3 rows selected.

v734> select rownum, col from t where col='C' or col='B' or col='A';

    ROWNUM CO
---------- --
         1 A
         2 B
         3 C

3 rows selected.

v734> explain plan for select rownum, col from t where col='A' or col='B' or col='C';

Explained.
...
SELECT STATEMENT
  COUNT
    CONCATENATION
      INDEX UNIQUE SCAN T_I
      INDEX UNIQUE SCAN T_I
      INDEX UNIQUE SCAN T_I

--
Have a nice day
Michel


<rjones_at_cincom.com> a écrit dans le message : 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.