Access to Table EMER_PRICE_HIST Can Be Improved By Providing an Index (Doc ID 2119904.1)

Last updated on MARCH 28, 2016

Applies to:

Oracle Retail Merchandising System - Version 13.2.6 and later
Information in this document applies to any platform.

Symptoms

Oracle Retail Merchandising System (RMS) 13.2.6, we find that the table EMER_PRICE_HIST should have an index applied to improve performance.

<Patch 14690131> includes building a new table EMER_PRICE_HIST.   This table gets written to for emergency price changes (current date = action date), which can occur in ORDUPD (RMS Order Update) and Oracle Retail Price Management (RPM).  If a row already exists in EMER_PRICE_HIST for a given item/loc/tran_type/date, then some of its values get updated; otherwise, a row gets inserted.

Since no index is available, a full table scan is the only access path.  This means batch jobs that write to this table run more slowly as the table gets more rows.


Steps to Reproduce:

1. Create a large number of clearances (say,1,000) that are emergencies (effective_date = vdate).
2. Run the RPM Clearance Injector.  Notice the repeated full table scans against EMER_PRICE_CHANGE (if tracing is enabled, check the trace files; if not, check the AWR logs).
3. Add the index to the table.
4. Rerun the test.  Notice that it runs faster and the index is being used.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms