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;
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.