My Oracle Support Banner

Full Scan Of SA_TRAN_DISC When Using V_SA_TRAN_ITEM_NET_ROLLED for Totals Creation (Doc ID 2700043.1)

Last updated on AUGUST 18, 2020

Applies to:

Oracle Retail Sales Audit - Version 16.0.2 and later
Information in this document applies to any platform.

Symptoms

Full Scan of SA_TRAN_DISC when using V_SA_TRAN_ITEM_NET_ROLLED for Totals Creation. No partitions used by SATOTALS batch. This view joins SA_TRAN_ITEM with V_SA_TRAN_ITEM_DISC_NET_ROLLED, however since the STORE and DAY do not exist in V_SA_TRAN_ITEM_DISC_NET_ROLLED there is no join included between SA_TRAN_ITEM and V_SA_TRAN_ITEM_DISC_NET_ROLLED on STORE and DAY. This results in a full partition scan of SA_TRAN_DISC in the V_SA_TRAN_ITEM_DISC_NET_ROLLED view. Thus the totals are running slow due to full scans.

By simply adding STORE and DAY into the V_SA_TRAN_ITEM_DISC_NET_ROLLED view and then be including a join on STORE and DAY in the V_SA_TRAN_ITEM_NET_ROLLED view this will make the query hit to a specific partition/sub-partition when queries from the totals are executed.

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.