Require help in tuning below query. with existing filter & join conditons i tried to tune the query but no major breakthrough in reducing Runtime and Total CPU. The query is stuck in Step 4 where it is using Sliding Window Merge JOIN, it was in same step for 25 min and eventually aborted by me.
Appreciate your help in advance.
Defined all stats on base tables.
SAC_VT_1 is Volatile Table ( defintion is below Explain)
Correct Volatile Table definition, please ignore previous:
create volatile table SAC_VT_1
AS (SEL * FROM SANDBOX.STG_INS_POLICY_STEP_2_AUtO_PPI WHERE RISK_CODE='V1') WITH DATA
primary index (Risk_SK)
PARTITION RANGE_N(BTRN_ETL_UPDATE_DTTM BETWEEN TIMESTAMP'2015-03-01 00:00:00' and TIMESTAMP'2016-12-01 23:59:59' Each Interval '1' Day, No Range, Unknown)
On Commit Preserve Rows;
The query does not specify any relationship between the two tables on the dates in the partitioning of the table. Because there is no relationship specified, a sliding window join must be done to join every partition to every partition. Without knowing your data is is hard to know for sure but it appears that the date for the rows that are to be joined is the same in each table and could have an equality condition. This would make the join a direcct merge join that would be quite a lot faster.