Full-text indexing? You must read this
- by Kyle Hatlestad
For those of you who may have missed it, Peter Flies, Principal Technical Support Engineer for WebCenter Content, gave an excellent webcast on database searching and indexing in WebCenter Content. It's available for replay along with a download of the slidedeck. Look for the one titled 'WebCenter Content: Database Searching and Indexing'.
One of the items he led with...and concluded with...was a recommendation on optimizing your search collection if you are using full-text searching with the Oracle database. This can greatly improve your search performance. And this would apply to both Oracle Text Search and DATABASE.FULLTEXT search methods. Peter describes how a collection can become fragmented over time as content is added, updated, and deleted. Just like you should defragment your hard drive from time to time to get your files placed on the disk in the most optimal way, you should do the same for the search collection. And optimizing the collection is just a simple procedure call that can be scheduled to be run automatically.
beginctx_ddl.optimize_index('FT_IDCTEXT1','FULL', parallel_degree =>'1');end;
When I checked my own test instance, I found my collection had a row fragmentation of about 80%
After running the optimization procedure, it went down to 0%
The knowledgebase article On Index Fragmentation and Optimization When Using OracleTextSearch or DATABASE.FULLTEXT [ID 1087777.1] goes into detail on how to check your current index fragmentation, how to run the procedure, and then how to schedule the procedure to run automatically. While the article mentions scheduling the job weekly, Peter says he now is recommending this be run daily, especially on more active systems.
And just as a reminder, be sure to involve your DBA with your WebCenter Content implementation as you go to production and over time. We recently had a customer complain of slow performance of the application when it was discovered the database was starving for memory. So it's always helpful to keep a watchful eye on your database.
