We undertook a re-index of our database over the weekend and ever since any searching based on record numbers has been extremely slow (well over 1 minute when previously under 5 sec's). Searching on words or an expanded number search works fine, only searches based on the record number are affected. Does anyone have an idea on possible issues or solutions.
Just to double-check, what specifically did you do when you "re-index"?
If you used the TRIM Enterprise Studio's Dataset->Utilities->Reindex features, that doesn't change anything to do with record numbers. If you used the Dataset->Schema->Repair feature to fix/rebuild the indexes then maybe the indexes got messed up on the database side.
Thanks for the quick reply. The re-index was undertaken from TRIM Enterprise Studio, no Schema Repair has been undertaken, yet. I beleive the process undertkane was to turn off Refernetial Integrity (using Oracle), run the re-index and then turn Referential Integiry back on. I beleive the administrator also ran an Integrity Check. Is it worthwhile runnining a schema repair?
Record number searches do not rely so much on a TRIM created index.
I suggest the Oracle DBA ensure full statistics are being generated on a nightly basis and he can look at recreating any indexes in Oracle itself.
It's also possible that the database server is just underspecced and a reboot has occurred recently that has wiped any 'cache' in Oracle that previously stored common searches (such as testing a record number search range).
Hope this gives you a few ideas.
:::::::::::::::::::::: NOT A HP EMPLOYEE ::::::::::::::::::::::
We sometimes experience a problem that the indexes are all there with proper statistics, but that the state of the index is UNUSABLE . Then we need to rebuild the index which is best done in out of office hours because rebuilding when users are active may result in 'object in use errors' . But sometimes we shutdown TRIM during daytime and fix the index issues so that users can at least have proper performance the rest of the day.
Usually these unusable indexes are caused by a buggy overactive Oracle Resource Manager, killing long running jobs (especially multi hour reindexing tasks).
(Any opinions expressed in this forum are my own personal opinion and should not be construed as an official statement by DXC Technology.)
Analytics & Data Management Application & Business Services DXC Technology