Table: RETAIL_SHIPMENT / Records - 7 Billion / Partitions - 120 Weekly Partitions
Table: CURR_YAGO_CAL - Has Weekend Dates and their Corresponding Year Ago Dates. Sample 2 records
I have a query which looking at some metrics for a date and its Year Ago date. The Problem I am facing is when I pass the filter on the CAL_DT and Join the Shipment Table in CAL_DT it accesses only one partition of the RETAIL_SHIPMENT which is what we need.
Problem arises when we filter on the CAL_DT and join on the YAGO_DT with the RETAIL_SHIPMENT then the optimizer is doing a complete table scan which is taking hell lot of time.
Below are the two versions of the query and their corresponding Explain Plans. PLease see explain step no 4 and the last 2 statements of the query. Can anyone please share a way to resolve this or may be propose a different design to just read 2 partitions of RETAIL_SHIPMENT. Please ignore the low/high confidences for now as we have collected all the relevant stats and also the explain is from DEV hence low no of records. Any help would be appreciated.