Re: Order By Problem with rownum

Hi Add more questions about this rownum with order-by performance.

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 of cuase 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)

In article <861vag$3gk$1_at_bob.news.rcn.net>,   "Shaojie Hu" <maichen_at_rols.com> wrote:
> It dose not work, but thanks anyway.
>
> Mohamed Buhari <mbuhari_at_assigncorp.com> wrote in message
> news:3883B7FD.E25BFC5_at_assigncorp.com...
> > Here U go :-)
> >
> > 1. U cant user '*' in this type of query, so u need to specify each
column
> name
> > explicitly.Here is an example
> >
> > select alias.x, alias.y, alias.z from
> > (select x,y,z from xyz
> > group by x,
> > order by y ) alias;
> >
> > Mohamed
> >
> > Shaojie Hu wrote:
> >
> > > Hi, All:
> > >
> > > Oracle does not allow to use Order By clause in subquery, how do I
solve
> the
> > > problem?
> > > what I want is something like this:
> > >
> > > select * from (select x.*, rownum r from foo
> > > order by col1);
> > >
> > > Thanks.
> >
>
>

--
Zosen Wang (wangz_at_rocketmail.com)

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Jan 21 2000 - 17:18:42 CST