Which of the following query will be good in performance??

Database
Highlighted
Enthusiast

Which of the following query will be good in performance??

I suppose the first one as all the columns in the join are the UPI of both the tables and statistics are also collected on the UPI.

SELECT CWH.ARTCL_NUM
,CWH.SHIP_SITE_NUM
,CWH.RCV_CUST_NUM
,CWH.SHIP_DT
,CWH.TOT_ORD_QTY
,CWH.EXT_DT
,CWH.REC_CRE_TMS
,CWH.REC_CHNG_TMS
,CASE WHEN CWH.TOT_ORD_QTY<>HIST.TOT_ORD_QTY THEN 1 ELSE 0 END AS CHNG_FLAG
FROM IPFRDEV_T.IPFR315_CURR CWH
INNER JOIN IPFRDEV_T.IPFR315_HIST HIST
ON CWH.ARTCL_NUM=HIST.ARTCL_NUM
AND CWH.SHIP_SITE_NUM=HIST.SHIP_SITE_NUM
AND CWH.RCV_CUST_NUM=HIST.RCV_CUST_NUM
AND CWH.SHIP_DT=HIST.SHIP_DT
WHERE CHNG_FLAG=1;

/***************** Solution 2**************************************/

EXPLAIN
SELECT CWH.ARTCL_NUM
,CWH.SHIP_SITE_NUM
,CWH.RCV_CUST_NUM
,CWH.SHIP_DT
,CWH.TOT_ORD_QTY
FROM IPFRDEV_T.IPFR315_CURR CWH

MINUS
SELECT CWH.ARTCL_NUM
,CWH.SHIP_SITE_NUM
,CWH.RCV_CUST_NUM
,CWH.SHIP_DT
,CWH.TOT_ORD_QTY
FROM IPFRDEV_T.IPFR315_HIST CWH;
3 REPLIES
Enthusiast

Re: Which of the following query will be good in performance??

It doesn't look like the two queries are doing the same thing, but the first one will be more efficient if the join is being done on the UPI columns. The second one will redistribute both tables before doing the MINUS operation.

Re: Which of the following query will be good in performance??

The second oone will be more efficient and even performance wise also faster, since minus scripts are not always accurate results.
Enthusiast

Re: Which of the following query will be good in performance??

Both the given queries perform different operations.

In your first query you are extracting chng_flag value based on the data obtained after joining the 2 tables. But where is this operation performed in the 2nd query?