CODEWITHSUNDEEP
oracle-databaseoracle11goracle10g
karthik
karthik

Reputation: 261

Alternative Query to Implement Minus Query logic

we are using the below-mentioned minus query logic to find out the non-existing record between the 2 tables, is there an alternative logic that can be used via SQL to achieve the same this is causing performance issues and running for a very long time.

SELECT EMPLID,EMPL_RCD,EFFDT,HR_STATUS,EMPL_STATUS
FROM EDWHRSTG.PS_JOB_FULL_S
WHERE EMPLID = '09762931'
MINUS
SELECT EMPLID,EMPL_RCD,EFFDT,HR_STATUS,EMPL_STATUS
FROM SUODS.PS_JOB_S
WHERE EMPLID = '09762931'

Upvotes: 0

Views: 57

Answers (2)

BobC
BobC

Reputation: 4416

You need to identify where time is being spent, in order to determine root cause if the performance problem; otherwise it’s just a guess. An Active SQL Monitor report is your diagnostic tool of choice

Upvotes: 0

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

You can try using an OUTER JOIN:

SELECT EMPLID,EMPL_RCD,EFFDT,HR_STATUS,EMPL_STATUS
  FROM EDWHRSTG.PS_JOB_FULL_S a
  LEFT OUTER JOIN (SELECT EMPLID,EMPL_RCD,EFFDT,HR_STATUS,EMPL_STATUS
                     FROM SUODS.PS_JOB_S
                     WHERE EMPLID = '09762931') b
    ON b.EMPLID = a.EMPL_ID AND
       b.EMPL_RCD = a.EMPL_RCD AND
       b.EFFDT = a.EFFDT AND
       b.HR_STATUS = a.HR_STATUS AND
       b.EMPL_STATUS = a.EMPL_STATUS
  WHERE b.EMPLID IS NULL AND
        b.EMPL_RCD IS NULL AND
        b.EFFDT IS NULL AND
        b.HR_STATUS IS NULL AND
        b.EMPL_STATUS IS NULL

However, I doubt this will perform any better. Your best option is to add an index on the five fields in play here (EMPL_ID, EMPL_RCD, EFFDT, HR_STATUS, EMPL_STATUS) to both tables, or in other words

CREATE INDEX EDWHRSTG.PS_JOB_FULL_S_1
  ON EDWHRSTG.PS_JOB_FULL_S (EMPL_ID, EMPL_RCD, EFFDT, HR_STATUS, EMPL_STATUS);

and

CREATE INDEX SUODS.PS_JOB_S_1
  ON SUODS.PS_JOB_S (EMPL_ID, EMPL_RCD, EFFDT, HR_STATUS, EMPL_STATUS);

Upvotes: 1

Related Questions