performance problems on large joins??

Hello there

I hope some of you can give me some pointers here..

I'm working on a specialized text retrieval system, and have run into some performance problems

The general idea is to have one table containing all searchable words:

  Word_id number
  Word varchar2(100)
  ect..

this table contains about 250,000 records ...

then I have a table containing references to documents... this is a simple system, all with short documents, so I only keep track on the record level..   

  Word_id
  ref_key
  table_id
  field_map

where word_id points to the word table, ref_key points to the indexed record, table_id says in what table, and field_map is a bitmap of which field(s) contains this word..

this table contains about 4 million records

Now, to the queries:

Querying for only one word works like lightening.

in order to be able to do logic search, i.e. ('YELTSIN' OR 'JELTSIN') AND
'MOSCOW'

I have to do something like:
select doc_title from documents a,tw b,tw c where b.ref_key=a.doc_key
and c.ref_key=a.doc_key
and (b.word like 'YELTSIN' or b.word like 'JELTSIN') and c.word like
'MOSCOW'
(tw is a simple view joining the words table with the relations)

the problem is that this is very slow! (about 10 secs. to get a result set of about 300 documents)
there are not very many rows returned for any of these words, about 500 to 1000 of each

And if I happen to have a wildcard appended, i.e. 'YELTSIN%' things really go to pieces..even though, in this particular case YELTSIN% only returns one more row than YELTSIN

I'm writing to you now because I've run out of ideas on what to do next. I've checked and rechecked the indexes... Tried tweeking the memory settings, sort areas.. all with no result (well.. apart from the mayhem resulting from tweeking a bit too much with some of the parameters:))

What's the prob then???
Performance went out the door when the relations table came above a million records..

incidently: Running 7.2 on dual P NT... 100mhz and only 64M memory.. the system runs fine with all the rest of the database applications.. Oracle owns about 32megs of the memory

Ideas, please....

regards
 Kári Poulsen

pls. email also to kapo_at_utvarp.olivant.fo Received on Tue Jun 23 1998 - 16:46:20 CDT