Order by to user the index

From: helper <wangz_at_isd.net>
Date: Sun, 23 Jan 2000 00:18:32 GMT
Message-ID: <86dhcd$k4f$1@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 - 18:18:32 CST