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

From: Vladimir M. Zakharychev <bob_at_dpsp-yes.com>
Date: Wed, 3 Jul 2002 13:12:42 +0400
Message-ID: <afuf6s$g1u$1@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 - 04:12:42 CDT