Instead of Join, can we use Exists to imporve performance

Bindu K

Team,

The With Temp1 is executing in 10 secs and it fetches - 700 records.

The FT_BASE table should fetch only the SUBSCRIBER_ID's that are there in Temp1 Table.

I have highlighted the statement which is causing the performance, please let me know if there is any alternate way of writing the query in Using 'Exists' OR IN. Please help me in changing the query.

The Final results should show only 700 Subscriber ID's

Thanks!

Bindu

With Temp1 AS

(

SELECT

            S.SUBSCRIBER_MDN,

            TO_CHAR(S.MASTER_SUBSCRIBER_ID) AS
SECONDARY_MASTER_SUBSCRIBER_ID,            TO_CHAR(SP.PRIMARY_MASTER_SSCR_ID) AS PRIMARY_MASTER_SSCR_ID,

            TO_CHAR(PRI.SUBSCRIBER_ID) AS PRIMARY_SSCR_ID,

          
NTWK_ACCESS_PLAN_OFFER_NM AS
PRIMARY_NAP,

            SUM(ADDN_GROSS_CNT) AS GA, SUM(DFCT_GROSS_CNT) AS GD

FROM

          
FT_SSCR_ADS A

            INNER JOIN DIM_CUSTOMER C ON A.CUSTOMER_KEY = C.CUSTOMER_KEY

            INNER JOIN DIM_CUSTOMER_STATUS CS

                 ON A.CUSTOMER_STATUS_KEY = CS.CUSTOMER_STATUS_KEY

            INNER JOIN DIM_SUBSCRIBER S ON A.SUBSCRIBER_KEY = S.SUBSCRIBER_KEY

            INNER JOIN DIM_EQUIPMENT E ON A.EQUIPMENT_KEY = E.EQUIPMENT_KEY

            INNER JOIN DIM_DATE D ON A.ADS_EVENT_DATE_KEY = D.DATE_KEY

            INNER JOIN DWWRK.STG_WRK_SSCR_PAIR_STATUS SP

                 ON S.MASTER_SUBSCRIBER_ID = SP.SEC_MASTER_SSCR_ID

                        AND D.DAY_DT BETWEEN SP.ROW_EFF_DT AND SP.ROW_EXP_DT

            INNER JOIN DIM_SUBSCRIBER PRI

                 ON PRI.MASTER_SUBSCRIBER_ID = SP.PRIMARY_MASTER_SSCR_ID

                        AND D.DAY_DT BETWEEN PRI.ROW_EFF_DT AND PRI.ROW_EXP_DT

            INNER JOIN BRDG_DIM_SSCR_DIM_SSCR_SA SA

                 ON PRI.SUBSCRIBER_KEY = SA.SUBSCRIBER_KEY

                        AND D.DAY_DT BETWEEN SA.ROW_EFF_DT AND SA.ROW_EXP_DT

            INNER JOIN DIM_SSCR_SERVICE_AGREEMENT
AGR

                 ON SA.SSCR_SERVICE_AGREEMENT_KEY = AGR.SSCR_SERVICE_AGREEMENT_KEY

WHERE

          
ADS_EVENT_DATE_KEY BETWEEN TO_CHAR(sysdate-6,'YYYYMMDD') AND TO_CHAR(sysdate,'YYYYMMDD')

            AND C.EMBEDDED_DEVICE_RESELLER_IND = 'N'

            AND CS.REVENUE_GENERATING_IND = 'Y'

            AND E.EQUIPMENT_CATEGORY1_DESC = 'Connected Device'

            AND E.EQUIPMENT_CATEGORY2_DESC = 'Wearable Solution'

            AND E.EQUIPMENT_CATEGORY3_DESC = 'Watch (Cellular)'

GROUP BY

            S.SUBSCRIBER_MDN,                                                                              
             S.MASTER_SUBSCRIBER_ID,

            SP.PRIMARY_MASTER_SSCR_ID,                    

          
PRI.SUBSCRIBER_ID,

          
NTWK_ACCESS_PLAN_OFFER_NM

)

select distinct S.SUBSCRIBER_ID from FT_BASE F

INNER JOIN DIM_SUBSCRIBER S ON

1. F.SUBSCRIBER_KEY = S.SUBSCRIBER_KEY

INNER JOIN Temp1

ON S.SUBSCRIBER_ID = Temp1.PRIMARY_SSCR_ID

where F.SNAP_DATE_KEY BETWEEN 20180801 AND TO_CHAR(sysdate-7,'YYYYMMDD')

Gaz in Oz

If your question is related to mysql then yes. If it is related to Oracle database server then you should move your post to:

AFTER first reading

As a general rule of thumb, EXISTS() is usually faster than IN() due to the fact that with IN() all values are read in the in-list where as with EXISTS() the processing stops as soon as a match is found.

Bindu K

Can anyone suggest me if this is the right group I posted

Gaz in Oz

If your question is related to mysql then yes. If it is related to Oracle database server then you should move your post to:

AFTER first reading

As a general rule of thumb, EXISTS() is usually faster than IN() due to the fact that with IN() all values are read in the in-list where as with EXISTS() the processing stops as soon as a match is found.

Dave Stokes-MySQL Community Team-Oracle

What does the query plan say if you compare both of your options?