Re: Help! with PL/SQL

From: André Klück <akluck01_at_wxs.nl>
Date: Sat, 19 Aug 2000 16:51:52 +0200
Message-ID: <399E9F08.BF01A027@wxs.nl>

Don't use ORDER BY, GROUP BY in combination with ROWNUM.

Use this:
Select the n-highest/lowest results of a (complicated) query.

Combinations of joins, hints, GROUP BY and ORDER BY might give you a long list of results, while
you are only interested in part of the resultset. ROWNUM and ORDER BY do not go together to fix this problem.

This query can give you the right set without performance lost:

SELECT A.column1 col1

     , A.column2 col2
     , ROWNUM    ranking
FROM (SELECT SUM(columnX) column1
           , SUM(columnY) column2
           , columnZ      column3
     FROM test
     GROUP BY columnZ            ) A
   , SYS.DUAL                      B

WHERE -1*A.column2 = DECODE(B.dummy(+),'X',0,0)   AND ROWNUM <= n
;

Check out the executionplan of this statement: First tables A and B will be used (B with a full scan, but that's ok).

After access of table A, the results are joined with those of B (merge join).
In order to do this properly Oracle will first SORT the results of A and B,
than the outer join will follow. The sorting on B is what we are interested in.
Default Oracle will sort ASCENDING. First on A.column2, because it is in the WHERE-clause.
That column will be merged with the DECODE of B.dummy.

Because values from table B are all the same, the SORT is in effect on tha values from table A.
This is what we want. Mutiply by +1 for ASC, by -1 for DESC (check this yourself).

After the SORT we want to restrict on the amount of rows by ROWNUM. Joining with DUAL will give a small overhead on performance.

gdas_at_my-deja.com wrote:

> This one is really frustrating me... The following cursor declaration
> is giving me an error:
>
> cursor c1(maxid number, account number) is select feature_id, freq,
> feature, type, transform_id, unique_id, subject_id, account_id from
> (select a.feature_id, a.freq, substr(a.feature, 0, 500) as feature,
> a.type, a.transform_id, a.unique_id, a.subject_id,
> b.account_id from feature a, datastage b where a.unique_id=b.unique_id
> and b.account_id = account and a.feature_id > maxid order by
> a.feature_id ) where rownum <= 500;
>
> I'm trying to get only the first 500 rows, so I'm sorting inside the
> inline view and then applying a rownum constraint in the outer select.
> The sql itself works just fine if I run it through sql plus (and of
> course filling in some literals for the input parameters.)
>
> When trying to complile the procedure however, I keep getting this
> error :
>
> PLS-00103: Encountered the symbol "ORDER" when expecting one of
> the following:
> . ( ) * @ % & - + / mod rem with an exponent (**) and or
> group having intersect minus start union where connect ||
>
> For some reason it does not like the order by clause inside of the
> inline view definition ("order by a.feature_id "). If I remove that
> order by clause the procedure will compile successfully.
>
> I'm running 8.1.5 on NT. I can't see what I am doing wrong here, I'm
> hoping perhaps another pair of eyes looking at this might be able to
> spot something obviously. But the fact that the sql itself runs fine
> after inserting literals for the input parameters has me thinking I
> might have stumbled on a either a bug or some kind of limitation of
> PL/SQL...
>
> Thanks for any tips.
> Gavin
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Sat Aug 19 2000 - 09:51:52 CDT