Re: displaying result sets in the order listed in the IN list

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 17 Apr 2004 15:15:11 +0100
Message-ID: <000401c42486$6602e5a0$7102a8c0@Primary>

Very cute, but I was about to write a note about naughty people and bind variables. Then I realised that there would be cases where it wouldn't work.

Oracle 9.2

SQL> create table t1 (n1 number, v1 varchar2(10));

Table created.

SQL> insert into t1 values (79,'asdf');
SQL> insert into t1 values (7902,'bnasdff');
SQL> insert into t1 values (790,'ersfda');
SQL> commit;

Commit complete.

SQL> select n1,v1 from t1
2 where n1 in (790,7902,79)
3 order by instr('790,7902,79',n1);

N1 V1
---------- ----------

        79 asdf
       790 ersfda
      7902 bnasdff

3 rows selected.

(The same occurs with an index on n1).

The note about results appearing in reverse order is (I think) the side effect of an optimisation for inlist iteration against indexes - by pre-sorting the in-list, Oracle can optimise index access
(leaf-block pins if nothing else). Why the traversal
is then in reverse order - who knows ?! One day I'll rig an experiment to check if the index leaf blocks really are read in reverse order.

But these things should not be trusted - someone on the newsgroup has just pointed out a wonderful Metalink note that's relevant for anyone who leaves out the ORDER BY because they think a GROUP BY will do it for them.

See 224815.1

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

April 2004 Iceland http://www.index.is/oracleday.php June 2004 UK - Optimising Oracle Seminar

Original Message ----- From: "Daniel Fink" <Daniel.Fink_at_Sun.COM> To: <oracle-l_at_freelists.org> Sent: Friday, April 16, 2004 7:22 PM Subject: Re: displaying result sets in the order listed in the IN list

If you are able to dynamically generate the statement, you can use the inlist as an input for the order by. The INSTR function will return the starting position for each string, so the first string will have a lower value than the second, etc.

select empno, ename
from emp
where empno in (7902,7788,7369,7900)
order by instr('7902,7788,7369,7900',empno);

EMPNO ENAME
---------- ----------

      7902 FORD
      7788 SCOTT
      7369 SMITH
      7900 JAMES

Create a pl/sql wrapper that accepts the INLIST and also use it in the order by.

Interestingly enough, my first cut at this revealed that the values were being returned in reverse order! It had to to with the execution plan, so you can't count on it being the same every time.

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Sat Apr 17 2004 - 09:11:55 CDT