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

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: 1998/05/27
Message-ID: <356bd07d.8170881@www.sigov.si>#1/1

On 26 May 1998 23:20:54 GMT, "Tanya Injac" <tanyainjac_at_unn.unisys.com> wrote:

>
>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 is right, it works only when merge join is used - it fails when nested loop or hash join is used instead. For that matter, a hint USE_MERGE in a query might be a good idea, in case that CBO decides not to use merge join. If RBO is used, then in my experience merge join will allways be used on outer joins.

I recived some e-mail responses from people asking why rownum works in this case, what is the purpose of "dummy" join, what is the purpose of decode function etc...

Why rownum works here? Rownum is assigned to the row before any explicite sorting (as a result of ORDER BY clause) is done. But when oracle has to perform an implicit sorting (for example, because of the GROUP BY or merge join) then rownum will be assigned to the presorted result set. For this reason I outer joined the table emp with the small table - the smaller, the better for performace sake. It could be any table, but sys.dual is the most appropriate, as it is ususaly the smallest table and publicly available. The joining condition is irrelevant, since in any case all the rows from the emp will be included in the result set.

What is the purpose of the decode function? The columns emp.sal and dual.dummy are of different datatypes and the direct comparison would result in some ORA-error, complaining about incompatible datatypes. With the use of decode I ensure that the result is allways NULL, which is compatible with all datatypes. So in this particular join with use of decode, it doesn't realy matter if sal is of type numbe, varchar, date,...

Btw, when oracle performs an implicit sort, it allways sorts in ascending order, so my sollution works on BOTTOM_N type of problems. If you wan't to apply it to TOP_N, you must use another trick. For example, we wan't to select 5 records from emp with *highest* salaries:

SQL> SELECT /*+ USE_MERGE */ a.empno, a.ename, a.sal   2 FROM emp a, dual b
  3 WHERE -1*sal = DECODE(b.dumMY(+),'X', NULL, NULL)   4 AND rownum <= 5
  5 ORDER BY sal DESC

    EMPNO ENAME SAL
--------- ---------- ---------

     7839 KING            5000
     7788 SCOTT           3000
     7902 FORD            3000
     7566 JONES           2975
     7698 BLAKE           2850

5 rows selected.

The trick here is to make oracle perform implicit sorting in ascending order, but on *negative values* of the sorted column, hence the "-1*sal" in the WHERE clause. This results in the descending order of the salary amounts!

Regards,

---

Jurij Modic                             Republic of Slovenia
jurij.modic_at_mf.sigov.mail.si		Ministry of Finance
============================================================

The above opinions are mine and do not represent any official standpoints of my employer Received on Wed May 27 1998 - 00:00:00 CDT