Re: Order by to user the index

The syntax for an index hint is:

     /*+ index(table_name index_name) */

In your case this would be:

    /*+ index (dbo.editorial ED_BY_FN) */

Hope this helps,

DeVerne

Help (helper) wrote in message <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 - 20:36:28 CST