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

Well, first of all, thanks Vladimir for your help. I had tried before your hint, and, as kopek said, the select * from (select....) order by... structure doesn't help. It takes the same time (and resources) and the execution plan is basically the same.

The FIRST_ROWS hint and the order by score used together work incredibly well though, actually the response time now for that query is about 2 seconds, which is great, but I need to sort by another field, different from "score". In that case it's not necessary to use two "select" statements in the query, the "FIRST_ROWS" hint is enough. My client's dabatabase consists of nearly 4000 magazine titles and more that 1000000 articles related to those magazines, so maybe sorting by the article title may not be useful (that's what I've been trying to tell them!) but I need to sort also by the magazine title or the article's publication date, so my problem is not solved yet.

I realize it's not an intermedia problem, is a sorting problem in general and it is incredibly dissapointing.

I read somewhere that I can create an index so the data can be presorted, but don't know how to do tha considering that I have an intermedia index that will always be used. Any hints??

Thanks.

kopek <matezuka_at_yahoo.com> escribió en el mensaje de noticias f7cb1d69.0207030953.7839a688_at_posting.google.com...
> 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_at_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 - 14:22:58 CDT