Below query is running fine, but resulting in a Very High CPU Usage. Culprit may be the CASE statement.
As per explain plan, this query is "Left Outer joined using Product Join". How come that be avoided to make the CPU Usage Lower?
, MAX(kcon_ord_dt.kcon_ord_ts) ORDER_DT
SELECT DISTINCT ORDER_NBR, SUB_ORD_ID
SELECT c.ID , cast(max(c.ord_ts) as date) kcon_ord_ts
FROM TEMP_TABLE_2 c
group by c.ID
CASE WHEN Z.ID = X.ORDER_NBR THEN X.ORDER_NBR
WHEN Z.ID = X.SUB_ORD_ID THEN X.SUB_ORD_ID
GROUP BY X.ORDER_NBR;
The join condition is equal to
Z.ID = X.ORDER_NBR
Z.ID = X.SUB_ORD_ID
You should always try to avoid ORed join conditions, they always lead to product joins.
To fix this you have to split the ORed join into two seperate queries using one of the conditions each and UNION ALL them.