Re: Order by to user the index

Try adding:

where <indexed_column> > ' '

or some low value that it could never be...

Help (helper) <wangz_at_isd.net> wrote in message news:86dhcd$k4f$1_at_nnrp1.deja.com...
>
>
> Can not make the order-by to use the index. Any ideal!!
>
> I tried to write a sql to get first N rows from a sorted column order.
> I also created an index for the sorted column and analyzed both table
> and index. I just can not make the sql to use the index for avoiding
> the full table scan. This sql takes about 6 sec to run. If I do not put
> order-by it only take less than sec ofcuase it not on sorted order. I
> think it take less than 2 sec if it use the index. I am using 8i
> (8.1.5) with CBO. The following are the sql:
>
> SQL> l
> 1 SELECT file_name FROM (SELECT /*+ index(ED_BY_FN) */ record_id,
> file_name, file_info, file_creator
> 2 FROM dbo.editorial
> 3 order by 2
> 4 )
> 5* WHERE ROWNUM <= 30
> SQL> /
>
>
> Execution Plan
> ----------------------------------------------------------
>
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4211 Card=24295
> Bytes=826030)
>
>
>
> 1 0 COUNT (STOPKEY)
>
> 2 1 VIEW (Cost=4211 Card=24295 Bytes=826030)
>
> 3 2 SORT (ORDER BY STOPKEY) (Cost=4211 Card=24295 Bytes=16
> 03470)
>
>
> 4 3 TABLE ACCESS (FULL) OF 'EDITORIAL' (Cost=2429 Card=2
> 4295 Bytes=1603470)
>
> Thanks in advance
>
>
> --
> Zosen Wang (wangz_at_rocketmail.com)
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Sat Jan 22 2000 - 19:12:55 CST