Tuning Oracle SQL having NOT EXIST clause

Question

I want to tune below query eliminating NOT EXIST clause specified in it. Can you please help.

GLT_temp_upload is temporary table where as DA_DUEDATE is partitioned table having huge data in it.

Please help

SELECT  DISTINCT
              batchid,
              store_area,
                 STORE_AREA
              || ','
              || STORE_ID
              || ','
              || SMS_ID
              || ','
              || SMS_SERVICE
              || ','
              || SYNERGY_MODE_ID
              || ','
              || FREQUENCY
              || ','
              || DUEDATE
              || ','
              || STUDY_ID
              || ','
              || YEAR
              || ''
              || WEEK_ID
              ||',Not exist in Da_Duedate'
         FROM GLT_temp_upload upload
        WHERE     upload.batchid = 1
              AND NOT EXISTS
                         (SELECT due.week_id,
                                 due.country_id,
                                 due.year,
                                 due.study_id,
                                 due.store_id,
                                 due.store_area,
                                 due.synergy_mode_id,
                                 upload.batchid,
                                 due.due_date,
                                 upload.sms_service
                            FROM DA_DUEDATE due
                           WHERE     due.store_id = upload.store_id
                                 AND due.study_id = upload.study_id
                                 AND due.store_area = upload.store_area
                                 AND due.frequency = upload.frequency
                                 AND due.sms_service = upload.sms_service
                                 AND due.week_id = upload.week_id
                                 AND due.country_id = upload.country_id
                                 AND due.year = upload.year
                                 AND due.sms_id = upload.sms_id
                                 AND due.synergy_mode_id =
                                        upload.synergy_mode_id)

Answer 1

You may try NOT EXISTS / LEFT JOIN / NOT IN

In your NOT EXISTS it's enough to SELECT 1 instead of the list of columns

Sometimes LEFT JOIN can be more beneficial (depending on indexes, the size of the tables etc)

SELECT  DISTINCT
              batchid,
              store_area,
                 STORE_AREA
              || ','
              || STORE_ID
              || ','
              || SMS_ID
              || ','
              || SMS_SERVICE
              || ','
              || SYNERGY_MODE_ID
              || ','
              || FREQUENCY
              || ','
              || DUEDATE
              || ','
              || STUDY_ID
              || ','
              || YEAR
              || ''
              || WEEK_ID
              ||',Not exist in Da_Duedate'
         FROM GLT_temp_upload upload left join DA_DUEDATE due
              ON due.store_id = upload.store_id
                 AND due.study_id = upload.study_id
                 AND due.store_area = upload.store_area
                 AND due.frequency = upload.frequency
                 AND due.sms_service = upload.sms_service
                 AND due.week_id = upload.week_id
                 AND due.country_id = upload.country_id
                 AND due.year = upload.year
                 AND due.sms_id = upload.sms_id
                 AND due.synergy_mode_id = upload.synergy_mode_id
        WHERE upload.batchid = 1 and due.store_id is NULL;

I'd recommend you looking at the execution plan to find an optimal solution for your case.