my problem with

From: dead <OVOIZBACI-vlasic_at_vodatel.net>
Date: Thu, 26 Aug 2004 09:14:54 +0200
Message-ID: <cgk2lk$q4v$1@sunce.iskon.hr>

Does anyone can explain this to me :
I have table "T1" with 300.000 rows, one index on field "F1" and rows are not inserted in sequence order (F1 : first inserted "000002", second inserted "000003", then "000008", "000001", "000004",... => I need sort to have first 5 as result)
SELECT /*+FIRST_ROWS*/ * FROM t1 WHERE ROWNUM <= 50 ORDER BY f1 thoesn't work well, and
SELECT /*+FIRST_ROWS*/ * FROM ( SELECT * FROM t1 ORDER BY f1 ) WHERE ROWNUM <= 50
work well but slow because this take full table scan.

This is my question :

Why when I use :

SELECT /*+FIRST_ROWS*/ * FROM     t1
WHERE     f1     IN


(

SELECT     f1     FROM     t1     WHERE ROWNUM <= 50

)
ORDER BY f1
it need 5.5 seconds for result

and if I change that in

SELECT /*+FIRST_ROWS*/ * FROM     t1
WHERE     f1     IN


(

SELECT     f1    FROM     t1    WHERE ROWNUM <= 50
MINUS
SELECT     f1    FROM     t1    WHERE ROWNUM <= 0

)
ORDER BY f1
it work well and need only 0.02 sec; why this work better with "minus"? Received on Thu Aug 26 2004 - 02:14:54 CDT