Re: Obtains rows 10-20 in a SELECT with ORDER BY!

Jurij's solution works in the case when JOIN operation is (sort) MERGE JOIN. I tried the same selects with the hints USER_NL or USE_HASH and it did not work. BTW, I discovered I didn't have to use dual table for this trick.

This works for me:

SELECT /*+ use_merge(b) */ rownum, a.empno, a.ename, a.sal from emp a, dual b
where a.sal = decode(b.dummy(+),'X',NULL,NULL) and rownum <= 10
MINUS
SELECT /*+ use_merge(b) */ rownum, a.empno, a.ename, a.sal from emp a, dual b
where a.sal = decode(b.dummy(+),'X',NULL,NULL) and rownum <= 5

I would like to hear Jurij's comment, too.

Regards,
Tanya

-- 
Tanya Injac
Oracle Developer/DBA
Unisys NZ Ltd

Peter Schneider <peter.schneider_at_okay.net> wrote in article
<6kf8oh$c55$1_at_trader.ipf.de>...


> 

> [...]

> 

> >To return bottom_10 records (based on sal), you can use the following

> >query (extremely fast even on large tables):

> >

> >SQL> select rownum, a.empno, a.ename, a.sal from emp a, dual b

> >  2  where a.sal = decode(b.dummy(+),'X',NULL,NULL)

> >  3  and rownum <= 10

> >  4  order by 4;

> 

> [...]

> 

> 

> Well, that's great !

> 

> Thank you very much Jurij for this most interesting posting. I wasn't

> aware of this way of solving the problem.

> 

> But I'm totally in the dark why and how this is working. According to

> what the docs say about the rownum pseudocolumn, it should not work

> this way.

> 

> Could someone shed some light on this ?

> Is this specific to an Oracle release, or to an optimizer mode ?

> 

> Or can one expect that this is portable and guaranteed to always work

> this way ?

> 

> TIA,

> Peter

> 

> 

> -- 

> Peter Schneider

> peter.schneider_at_okay.net

>