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.
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.
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