Sorting problems oracle 9i

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 Received on Wed Dec 29 2004 - 07:52:34 CST