text indexes - dilemas between transactional and performance

yoram.ayalon@structuredwe · *nix forums beginner Joined: 29 Mar 2006 Posts: 19

we are trying to create a test search system.

the master table we want to search has 1.7M rows (and growing), and has
10 searchable fields (and many others). we created a search table which
PK is exactly the same one as for the master table, and has one
varchar2(4000) field which holds all the searchable information. any
update to the master table updates in same way the search table. it
also contains a field "siteid", which has 2000 unique values. the
largest siteid has 55K rows, but most have either 4 or 3 digit number
of rows.

so, we have:

CREATE TABLE mastertab( pid, siteid, field1, field2, .......)

CREATE TABLE searchtab(pid, siteid, searchfield varchar2(4000))

we need basic text search capabilities only, keywords, wildcard, NOT

the master table is heavily updated. some updates are from the user
interface, but we also have mass updates initiated by customers,
deletions /additions/updates.

we had a functional requirement to be transactional, that is any update
had to be searchable immediately. because of this, we created a CTXCAT
type index on the search table, using an index set in which "siteid"
was indexed and the search field was used for the text index itself.
any query to this table was using the "sited = xxxx" parameter in the
call to CTXCAT, and the search was running fast.

however, we found out that in mass update situation our database is
slowed down because of the intense work of updating the text indexes.
this was affecting the entire database, and our site crawled down while
the updates went on

we understand that partitioned table index might help performance, if
we can partition per siteid (or hash of), but then we need a CONTEXT
index and we lose the transactional feature.

do we have any way out? do we have to switch to CONTEXT index? in this
case, what should be the optimal sync and optimize schedule in terms
of overall performance? Would this type of index work in mass updates
situations?

Alternatively, if we can change the requirements, and remove the update
of the search table for mass updates, what would we the best way to
update the index (off-hours) ? it seems to me we might be better off to
completely drop and rebuild the index. does it seem right?

Matthias Hoys · *nix forums Guru Joined: 18 Apr 2005 Posts: 400

"Yoram Ayalon" <yoram.ayalon@structuredweb.com> wrote in message
news:1152716226.292216.190430@m73g2000cwd.googlegroups.com...

yoram.ayalon@structuredwe · *nix forums beginner Joined: 29 Mar 2006 Posts: 19

the master table is huge, with many indexes, and we didn't want to add
the text index to it.

the update is in the stored proc which handle every
update/insert/delete.

when I looked at the instance it was always the update of the text
index itself which hanged the system, I can't believe that having it on
the master table would have changed the outcome significantly

Matthias Hoys wrote:

Google