RE: strategies for optimizing 'like' operations
Date: Fri, 7 Mar 2008 13:36:08 -0600 (CST)
The index must indeed be maintained, but this can be done with a DBMS job (or Scheduler job). If memory serves, the maintenance job was created automagically as a DBMS job in 9iR2 under the CTXSYS account.
For that DB, the character column that I indexed was updated perhaps only a few dozen times per day, so I ran the job every minute with no discernable impact on performance. The 250K rows in this table were originally on a 6-CPU 200Mhz HP K570 -- not exactly a workhorse by today's standards.
As part of the SLA the users understood that there could be up to a 60-second delay in updating the search index. This may not be acceptable in every case. If not, then a DML trigger on the affected colums could spawn the update process for as near a real-time update as could be expected. I believe you'd want to make sure that only one CTX update could run at a time, which would involve queuing, which is why it was deemed acceptable to run the update every minute for us.
I'm not necessarily advocating Text, but instead trying to be clear on how I had once used it. I'd much prefer the app handle parsing of the column to create a foreign key table containing references to the searchable text for maximum flexibility and maintenance considerations.
> The problem with oracle text as I understand it is that the index must
> be manually maintained.
> Our users have an expectation of immediate availability through the
> Thanks for the suggestion!