Re: displaying result sets in the order listed in the IN list
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 17 Apr 2004 15:15:11 +0100
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@xxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
