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:

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

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