Oracle - Word re-indexing challenge

Hi all,

 

We have configured our HPRM 8.1.0 with Oracle backend(Oracle Virtual Machine). And so far we are holding around 27 million of records. on Recent past we happend to perfrom word re-indexing, as many of our users were start complaining about the poor search and retrival performance.

Hence we had commenced our maintenance activities to re-index all the records. We had run the maintenance task for 2 weeks to complete the re-indexing of title,additional field,classification,location... etc except Note field.

The real challenge came when we have tried to re-index the note fields, as there are around 720 million words to delete and re-index. we had never able to complete the re-indexing so far, as the deletion of the records itself is taking more than a week which always got intereuptted in middle by some mean and the database will run the backout process (which also take equal amount of time as far the deletion query got executed). As oracle need the 'commit' command to be executed to commit the transaction.

Any advise/solution to re-index the note field would be so grateful.

  • From my observations word searching and reindexing is much slower on Oracle than on MS-SQL, especially in HPRM 8.1.x

    I recommend to change system option to "Use IDOL indexes where possible". If you can't keep it as permanent it may be nice workaround just for the time of indexing.

  • If your system is poorly performing on databases indexes, it will be worst on IDOL because it is resource hungry. You may ask your Database Admin to look at Oracle side Performance, especially the TempTablespace and data storage tablespaces, if data and indexes are separate tablespaces spanning over different disks  etc. Check how is the Oracle instance (in general) performing? Considering it is structured data only, it is not a challange for databases for 27M records which are designed for such context.  

     

  • I am not sure rebuilding the word indexes will fix a serach performance issue.

    Can you log a job with HPE Support and I am sure it will end up in my queue and then I can help do some tounleshooting.