Re: Qestion about querying using intermedia

Hello Tom,

Thanks, here is what I have:

I have over a million rows and data and it is around 1.5gigs. I have text stored in CLOB column.

I flattened out the table to remove joins. The table however is still narrow as I took a star schema approach and use quite a few ID columns. I already know the ID to look for I don't have to do a look up.

A query might look like:

where contains() and categoryid = 4

I am using the standard version of oracle as I cant afford Enterprise.

I have a single 650 processor and 748megs of ram on the machine. I have the data spread out over two hard drives and the indexes on one separate drive. Total of three drives.

I am using 8.1.7 release 3.

>o how many hits your single keyword query generates vs your two keyword query
This takes a long time

  1 select count(*)
  2 from articles
  3* where contains(article, 'plsql',1)> 0 SQL> /   COUNT(*)

       884

This takes a long time
select count(*)
  from tarticles
    where contains(article, 'plsql',1)> 0       and categoryid = 3
/
  COUNT(*)

       833

This takes a long time.
  1 select count(*)
  2 from articles
  3* where contains(article, 'sql',1)> 0 SQL> /   COUNT(*)

    151619

This is quick?
  1 select count(*)
  2 from articles
  3* where contains(article, 'have a problem',1) > 0 SQL> /   COUNT(*)

    151593

Thanks Tom.

Kev

On Wed, 06 Jun 2001 11:27:18 -0400, Thomas Kyte <tkyte_at_us.oracle.com> wrote:

>A copy of this was sent to Kev <java2e_at_yahoo.com>
>(if that email address didn't require changing)
>On Wed, 06 Jun 2001 01:39:47 -0700, you wrote:
>
>>Hello All,
>>
>>I am using interMedia and the problem is this.
>>
>>If I do a search where I am using more than one word or a phrase
>>the reponse time is extremely fast but when I query one word; say the
>>word help for example the hardrives light up like christmas trees. I
>>increased the sort_area_size to 3 megs which help speed up the more
>>then one word of phrase queries but didnt help and single word query.
>>
>>Also the CPU maxes out. What can I do so that people can search with
>>fast response times.
>>
>>Thanks,
>>
>>Kev
>
>
>you'll have to provide a tad more background then that.
>
>if you goto http://asktom.oracle.com/ (or www.oracle.com, or otn.oracle.com) and
>click on the search tab -- you'll have an intermedia search screen.
>
>The searches don't take more then 1 second using a single word.
>
>We would need to understand things like
>
>o volume of data (#rows and megabytes)
>
>o the query involved (is it a "where containts() and sysdate > column" -- mixed
>text and relational, just relational, etc)
>
>o how many hits your single keyword query generates vs your two keyword query
>
>o VERSIONS OF SOFTWARE
Received on Wed Jun 06 2001 - 14:24:06 CDT