Re: Sorting problems oracle 9i

From: <fitzjarrell_at_cox.net>
Date: 29 Dec 2004 08:21:56 -0800
Message-ID: <1104337316.737682.86130@c13g2000cwb.googlegroups.com>

csharpgeek wrote:
> Hi,
>
> I've created a pipelined oracle stored proc which returns 3 rows from
a
> 25 mil+ table. I need to sort on a price field, and limit the result
> set using ronum the query would be simply as follows.
>
> SELECT ITEM,PRICE FROM TBL WHERE ROWNUM <= 3 ORDER BY PRICE ASC;
> SELECT ITEM,PRICE FROM TBL WHERE ROWNUM <= 3 ORDER BY PRICE DESC;
>
> the queries return exacly thesame result, ie
>
> ITEM PRICE
> ---- ----------
> A 499
> B 499
> C 499
>
> This is incorrect because when i execute
>
> SELECT ITEM,PRICE FROM TBL WHERE INITIALS = 'AAA' AND TYPE = 4 AND
> ROWNUM <= 3 AND PRICE > 499 ORDER BY PRICE ASC;
>
> i get
>
> ITEM PRICE
> ---- ----------
> G 699
> H 799
> J 899
>
> I've tried using the following example
>
> SELECT ITEM,PRICE FROM TBL,dual b WHERE PRICE =
> decode(b.dummy(+),'X',NULL,NULL) AND ROWNUM <= 3 ORDER BY PRICE;
>
> but it is rather expensive.
>
> The core of me problem is the following retrieve 3 rows with the
> highest price from a 25 mill. row table.
>
> thx,
> carlos

You say you need the three recods with the highest price values. Only one of your queries appears to actually work, but may not return the rows with the highest price values. The following query will return the three records with the highest price values:

SQL> l
1 select p.item, p.price from
2 (select item, price from tbl order by price desc) p 3* where rownum <= 3
SQL> /

ITEM                      PRICE
-------------------- ----------
20000                   3980000
19999                   3979801
19998                   3979602

Statistics

---

0 recursive calls
0 db block gets
106 consistent gets
0 physical reads
0 redo size
636 bytes sent via SQL*Net to client
651 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
3 rows processed

SQL> Of course it will be 'expensive', as you're ordering a 25 million row table using a descending sort on price. Another query returning the proper results is listed below; this also returns the PLACED column, but does return the top three price values and the associated items from the table (my table has 20000 rows with progressively increasing prices):

SQL> l
1 select item,price,placed
2 from
3 (select item, price, rank() over (order by price desc) placed 4 from tbl)
5* where rownum <= 3
SQL> /

ITEM                      PRICE     PLACED
-------------------- ---------- ----------
20000                   3980000          1
19999                   3979801          2
19998                   3979602          3

Statistics

---

0 recursive calls
0 db block gets
106 consistent gets
0 physical reads
0 redo size
707 bytes sent via SQL*Net to client
651 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
3 rows processed

SQL> Doesn't appear too expensive to me, but again I'm querying 20,000 rows, unindexed, not 25 million.

Possibliy someone else has a different solution which is less costly. David Fitzjarrell Received on Wed Dec 29 2004 - 10:21:56 CST