Please take a look at the definition bellow:
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;
ctxsys.ctx_ddl.set_attribute ('cust_lexer','base_letter','YES'); -- removes diacritics
ctx_ddl.set_attribute('cust_lexer', 'alternate_spelling', 'GERMAN');
The person table has about 11.000.000 records.
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?
Do you optimize the index at all?
How frequently is the table updated?
With "sync(on commit)" your index will get more and more fragmented as each new record is sync'd.
You need to run ctx_ddl.optimize_index on a regular basis to remove this fragmentation. If you do that, you shouldn't need to recreate the index regularly.
A typical optimzation regime is to run it in "FULL" mode nightly, and then run "REBUILD" mode once a week, or once a month.
Didn't know that I could do optimization on that type of index.
The table is updated about 1.000 times every day.
Now I'll have to wait for about a month to see how it's going. I'll keep you posted about this.
Thank you very much.