1 person found this helpful
If you can reproduce the error on different databases, but other indexes behave correctly on all systems, then the error is with the definition of the index, in all probability.
If you query ctx_user_sections, is there a SEC_ID of 149? If so, what is the data type? That would probably tell you where to look next.
If SEC_ID of 149 does not exist, then it's possible that there is some lingering reference to an old set of preference values that may be at play. When you rebuild the index, do you drop all preferences and recreate them as well?
If nothing jumps out at you, you'll need to put together an example that would let us see the same behavior -- index definition and some sample data that we can run, along with DB version.
Thanks a lot for the answer, I have been under pression because of deadlines but I'll focus on this problem next week.
We have a serious performance issue with a text index on a XMType column (the index is very big one, 14G). The only way to get decent performance is to pin the DR$ I table in memory, but I had a bad surprise : when one do a ctx_ddl.optimize_index with rebuild the index becomes much bigger (from 14G to 18G) and so it does not fit in the keep cache anymore. A rebuild or a drop/create put back the index to the original size. I suspect that some storage options are changed but could not understand which. I'll do a test case and post it on this forum.
That's why I wanted the ctx_report.analyze_index but it fails (even when the base table is truncated, so you might be right, there could be a leftover from a previous index). I'll set-up a test case with a freshly created database.
The product manager, Richard Ford, wrote a white paper about putting the index in the keep cache, it makes a very big difference for us. But I think it should be adapted to the Oracle 12c case (with separate offset and BIG_IO for example).
I'll post the test case asap, thanks already for pointing me to the right direction.
I tried to reproduce it with a limited test case on a new database, but I could not. Maybe it has to do with the fact that the index column is a XMLType column stored as a securefile ? My databases are normally created from a RMAN restore of a restore, or from a new database where I use impdp to import a baseline. We store a big XML in this XMLType column and then it is indexed with a PATH_SECTION_GROUP (which means that all tags and attribute are automatically indexed by oracle but it also means that the resulting index is big). We also use SDATA section to do range filtering and sorting. I find it not clear from the documentation whether SDATA sections are allowed with PATH_SECTION_GROUP or not, but it works
The real problem I am investigating is that my text index has became much bigger now in version 12c, compared to version 10g. After I do a ctx_ddl.optimize_index with REBUILD, the size of the index increase significantly, while one would expect the contrary. It is a problem for me because I can't pin the index in the keep buffer pool anymore. I found a work-around, I will post it in another post as it might help other people