Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> RE: performance on intermedia query with order by, help!!

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

From: Carlos Hernandez <infoactiva_at_manquehuet.net>
Date: Wed, 3 Jul 2002 17:20:35 -0400
Message-ID: <afvq61$c2t$1@spiderman.nap.telefonicamundo.cl>


I take it back, I'm not sure it's not an intermedia problem.

I tried another query, with a different where clause which doesn't use an intermedia index, just an ordinary index. This query returns about 10000 rows, and uses an order by on the "title" field. This query takes about 2 secs. I didn't use the FIRST_ROWS hint or anything like that. This is the query:

select * from articles where cod_titulo_revista = 338 or cod_titulo_revista=658
order by title

I tried another query, using the original where clause (using the intermedia index). This query returns about 5500 rows, and it uses the same order by as before. This one takes about 30 secs. I just don't understand. This is the second query:

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

thanks

Carlos Hernandez <infoactiva_at_manquehuet.net> escribió en el mensaje de noticias afvj84$1fr$1_at_spiderman.nap.telefonicamundo.cl...
> 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 - 16:20:35 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US