Re: text indexes - dilemas between transactional and performance

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:
> "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 Tue Jul 18 2006 - 17:37:23 CDT