Re: performance problems on large joins??

Use the explain plan facility of Oracle to see which table is driving the view as well as the query and make sure it isn't the 4 million record one as well as avoiding full table scans.

Hint: Using a wildcard negates use of indexes so you will always do a full table scan. Therefore, do not use a view, it will only make things slower.

Kári Poulsen (kapo_at_utvarp.olivant.fo) wrote:
: 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

--

Jared Hecker	| HWA Inc. - Oracle architecture and Administration
jared_at_hwai.com	|  ** serving NYC and New Jersey **