Query with Very High CPU Usage

Database
Vga
Enthusiast

Query with Very High CPU Usage

Hello All,

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?

SELECT

   ALL_WCS1.ORDER_NBR

 , MAX(kcon_ord_dt.kcon_ord_ts) ORDER_DT

FROM

(

 SELECT DISTINCT ORDER_NBR, SUB_ORD_ID

   FROM TEMP_TABLE_1

) X

LEFT JOIN

(

 SELECT c.ID , cast(max(c.ord_ts) as date) kcon_ord_ts

   FROM TEMP_TABLE_2 c

 group by c.ID

) Z

ON

Z.ID =

(

 CASE WHEN Z.ID = X.ORDER_NBR THEN X.ORDER_NBR

      WHEN Z.ID = X.SUB_ORD_ID THEN X.SUB_ORD_ID

 END

)

GROUP BY X.ORDER_NBR;

--GV

1 REPLY
Senior Apprentice

Re: Query with Very High CPU Usage

The join condition is equal to

Z.ID = X.ORDER_NBR

OR

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.

Dieter