Full-text indexing? You must read this

Posted by Kyle Hatlestad on Oracle Blogs See other posts from Oracle Blogs or by Kyle Hatlestad
Published on Thu, 22 Mar 2012 17:01:50 +0000 Indexed on 2012/03/22 23:34 UTC
Read the original article Hit count: 555

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.  

[Read more

© Oracle Blogs or respective owner

Full-text indexing? You must read this

Posted by Kyle Hatlestad on Oracle Blogs See other posts from Oracle Blogs or by Kyle Hatlestad
Published on Thu, 22 Mar 2012 16:59:09 +0000 Indexed on 2012/03/22 23:34 UTC
Read the original article Hit count: 555

Filed under:

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.  

begin
ctx_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%

Original fragmentation

After running the optimization procedure, it went down to 0%

After optimizing

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.

© Oracle Blogs or respective owner

Related posts about /WebCenter/WebCenter Content