Item Word Search Build Process Performance Fix (R41829)

fbrammer

Active Member
If you are having performance issues with the Item Word Search Build process (R41829), consider adding a non-unique index to F41829 on just IWITM.

My client has a VERY large Item Master. They add an average of around 1,000 items per week. It's not unusual to add up to 10k in a week, and when we convert data from a subsidiary we could add over a 100k in a day. It's textiles, and yes we have A LOT of "widgets". We've struggled with the Item Word Search build process for a long time. It's been a low priority issue. This week we started looking into the issue and found that 99% of the problem was the Oracle DB was doing a SKIP RANGE SCAN of the PK index F41829_0 because it's trying do DELETE by the IWITM field, which is in the second position on the PK index. By adding a non-unique index on IWITM our processing time dropped by over 90%.

If you are experience multi-hour/day run-times from R41829 consider adding a non-unique index on F41829 (IWITM).

I've submitted this finding to Oracle Support also.
 
Top