Database

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

08-04-2011
08:02 AM

08-04-2011
08:02 AM

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;

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

08-16-2011
11:25 AM

08-16-2011
11:25 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

08-16-2011
07:54 PM

08-16-2011
07:54 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-19-2011
04:08 AM

09-19-2011
04:08 AM

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?