Re: performance on intermedia query with order by, help!!

I would be interested in hearing from Carlos again about the results from running the SQL as you suggested. I think this is not a InterMedia Text problem per se, but a sorting problem in general. You retrieve qualified rows and then your sort them. I am not sure

 select * from
  ( select /*+ FIRST_ROWS - recommended mode for interMedia Text */ *

      from articles where contains(title, :searchstr) > 0 )  order by title

can improve over

 select * from articles
 where contains(title,'study')>0
 order by title

Please post again, Carlos. Thx!

"Vladimir M. Zakharychev" <bob_at_dpsp-yes.com> wrote in message news:<afuf6s$g1u$1_at_babylon.agtel.net>...
> Try rewriting this query as
>
> select * from
> ( select /*+ FIRST_ROWS - recommended mode for interMedia Text */ *
> from articles where contains(title, :searchstr) > 0 )
> order by title
>
> This would perform search in an inline view first (without any sorting), and
> then sort search results and only them. If you happen to need sort on score(),
> this modification should do:
>
> select * from
> ( select /*+ FIRST_ROWS */ a.*, score(1) score
> from articles a where contains(title, :searchstr, 1) > 0 )
> order by score desc
>
> hth.
>
> --
> Vladimir Zakharychev (bob@dpsp-yes.com) http://www.dpsp-yes.com
> Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
> All opinions are mine and do not necessarily go in line with those of my employer.
>
>
> "Carlos Hernandez" <infoactiva_at_manquehuet.net> wrote in message
> news:aft276$o06$1_at_spiderman.nap.telefonicamundo.cl...
> > Hi, I'm tuning a simple intermedia query, like this:
> >
> > select * from articles
> > where contains(title,'study')>0
> > order by title
> >
> > the table "articles" has about 950000 rows, and the query above returns
> > about 10000 rows. The problem is the "order by". It takes about 29 secs to
> > sort the data. If I don't use it, the query takes about 2 secs to return
> > the data, which is completely reasonable. Please don't tell me not to use
> > the "order by" because I really need it.
> > The SORT_AREA_SIZE is set to 8Mb, should I increase it even more? I've
> > created a 3Mb text file with the 10000 rows.
> >
> > can I create an asc index on the "title" column so the data can be
> > pre-sorted? How?
> >
> > i'm running this on Oracle 8.1.7, Solaris 2.8
> >
> > thanks
> >
> >
> >
Received on Wed Jul 03 2002 - 12:53:26 CDT