Re: text indexes - dilemas between transactional and performance

"Yoram Ayalon" <yoram.ayalon_at_structuredweb.com> wrote in message news:1152716226.292216.190430_at_m73g2000cwd.googlegroups.com...
> 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?
>

Why did you use a separate search table ? How is this table updated ? Triggers on the master table ? Couldn't you just have used a Text index on the searchable fields of the master table ? Received on Wed Jul 12 2006 - 15:05:18 CDT