Re: my problem with

dead wrote:

> 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"?

Perhaps I am misunderstanding your question ... but you state "rows are not inserted in sequence order" so perhaps you don't understand relational databases. There is no such thing in a heap table of a row being "stored in an order".

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)