CREATE INDEX SCHEMA.prs_names_ldx ON SCHEMA.PERSON(NAMES_XML) INDEXTYPE IS CTXSYS.CONTEXT
parameters ('LEXER cust_lexer SYNC (ON COMMIT) filter ctxsys.null_filter section group ctxsys.auto_section_group') ONLINE;
The fact is that about two months after the index was created, the search got slower and slower. I tried to SYS.DBMS_STATS.GATHER_TABLE_STATS and to rebuild the index, but neither worked. The only solution that solved the issue was to drop and recreate the index.
And this happens every 1-2 months after the index is recreated.
I'm planning to create a job that will recreate the index before the operators can complain (in a shorter time span). This is just a dirty workaround, I know, but for now I don't have any other ideas.
Regarding this situation, I have two questions.
1. Is there any nasty implication about automatizing the index recreate procedure?
2. Do you have any suggestions about how I can further investigate the index degradation problem in time?