Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Obtains rows 10-20 in a SELECT with ORDER BY!

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

From: Tanya Injac <tanyainjac_at_unn.unisys.com>
Date: 1998/05/26
Message-ID: <01bd88fc$efffd960$6762df81@nz6220.nz.unisys.com>#1/1

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
>
Received on Tue May 26 1998 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US