How to sync and optimize an Oracle Text index?

oracle full-text-search oracle-text
trunkc picture trunkc · Dec 16, 2009 · Viewed 30.7k times · Source

We want to use a ctxsys.context index type for full text search. But I was quite surprised, that an index of this type is not automatically updated. We have 3 million documents with about 10k updates/inserts/deletes per day.

What are your recommendations for syncing and optimizing an Oracle Text index?

Answer

ewernli picture ewernli · Dec 16, 2009

What do you mean by "not automatically updated"?

The index can be synchronized on commit or periodically. 

Create index ... on ... INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('SYNC ( ON COMMIT)')
Create index ... on ... INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS 'SYNC (EVERY "SYSDATE+1/24")')

I you don't need real-time search accuracy our DBA recommended to sync the index periodically, say each 2 min. If you can afford to do it overnight, then even better. What is best depends on your load and the size of the document.

These links can probably provide you with more information:

For DBA advice, maybe serverfault is better?

Related questions

Does Oracle support full text search?

Is there an Oracle equivalent to MS SQL's full text search service? If so, has anyone implemented it and had good / bad experiences?

Read More
How to get around 4000 characters limitation of text_query in Oracle's CONTAINS operator?

In Oracle, the full text search syntax of Contains operator is: CONTAINS( [schema.]column, text_query VARCHAR2 [,label NUMBER]) RETURN NUMBER; which means the text_query can not be more than 4000 characters long or an error will occur. I repeatedly …

Read More
Searching for Text within Oracle Stored Procedures

I need to search through all of the stored procedures in an Oracle database using TOAD. I am looking for anywhere that the developers used MAX + 1 instead of the NEXTVAL on the sequence to get the next ID number. I've …

Read More