text indexes - dilemas between transactional and performance

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? Received on Wed Jul 12 2006 - 09:57:06 CDT