Dilip Dilip - 4 months ago 12
SQL Question

I am trying to improve the performance of an Oracle SQL that is finding the differences between two tables

I have two Oracle tables and I am doing an UNION between them to find out the difference in the data stored in those two tables but when I run the query in SQL Developer then the query is too slow and I am using the same query in Informatica and its throughput is less too.

TABLE 1: W_SALES_INVOICE_LINE_FS EBS(NET_AMT,
INVOICED_QTY,
CREATED_ON_DT,
CHANGED_ON_DT,
INTEGRATION_ID,
'EBS' AS SOURCE_NAME)

TABLE 2: W_SALES_INVOICE_LINE_F DWH (NET_AMT,
INVOICED_QTY,
CREATED_ON_DT,
CHANGED_ON_DT,
INTEGRATION_ID,
'EBS' AS SOURCE_NAME)

I am attaching the query with the question:

SELECT EBS.NET_AMT,
nvl(EBS.INVOICED_QTY,
case nvl(EBS.NET_AMT,0) when 0 then EBS.INVOICED_QTY
else -1 end) INVOICED_QTY,
EBS.CREATED_ON_DT,
EBS.CHANGED_ON_DT,
EBS.INTEGRATION_ID,
'EBS' AS SOURCE_NAME
FROM
W_SALES_INVOICE_LINE_FS EBS
WHERE NOT EXISTS (SELECT INTEGRATION_ID FROM W_SALES_INVOICE_LINE_F DWH
WHERE EBS.INTEGRATION_ID = DWH.INTEGRATION_ID)
UNION
SELECT DWH.NET_AMT,
DWH.INVOICED_QTY,
DWH.CREATED_ON_DT,
DWH.CHANGED_ON_DT,
DWH.INTEGRATION_ID,
'DWH' AS SOURCE_NAME
FROM
W_SALES_INVOICE_LINE_F DWH
where DWH.IS_POS = 'N' and
not exists (SELECT INTEGRATION_ID FROM W_SALES_INVOICE_LINE_FS EBS
WHERE EBS.INTEGRATION_ID = DWH.INTEGRATION_ID);


Let me know if you want to see the explain plan. Can someone tell me how to improve the performance or let me know if the issues is with something else and not with the above query!
SQL EXPLAIN PLAN

Answer

You are not performing a JOIN, you are performing a UNION. You are performing subqueries however, and those may be slowing down the overall performance. You might change the EXISTS to IN which can take advantage of an index if it exists.

Try the following:

SELECT EBS.NET_AMT, 
nvl(EBS.INVOICED_QTY,
case nvl(EBS.NET_AMT,0) when 0 then EBS.INVOICED_QTY
else -1 end) INVOICED_QTY,
EBS.CREATED_ON_DT,
 EBS.CHANGED_ON_DT, 
     EBS.INTEGRATION_ID,
 'EBS' AS SOURCE_NAME
 FROM
 W_SALES_INVOICE_LINE_FS EBS
  WHERE EBS.INTEGRATION_ID NOT IN (
     SELECT  INTEGRATION_ID 
     FROM W_SALES_INVOICE_LINE_F
)
UNION ALL
 SELECT DWH.NET_AMT,
  DWH.INVOICED_QTY, 
  DWH.CREATED_ON_DT,
  DWH.CHANGED_ON_DT, 
  DWH.INTEGRATION_ID,
 'DWH' AS SOURCE_NAME
   FROM
 W_SALES_INVOICE_LINE_F DWH
 where DWH.IS_POS = 'N' 
 and DWH.INTEGRATION_ID not in (
     SELECT  INTEGRATION_ID 
     FROM W_SALES_INVOICE_LINE_FS
  );  

Also, as mentioned by others in comments, a UNION ALL might be more appropriate.

Also, you could try using a LEFT OUTER JOIN which, if you do have an index, is a more explicit way of doing the above. I don't have access to my oracle from my current location to try an explain plan, but the above and below may actually be optimized similarly.

SELECT EBS.NET_AMT, 
    Nvl(EBS.INVOICED_QTY,
        CASE Nvl(EBS.NET_AMT, 0) WHEN 0 
        THEN EBS.INVOICED_QTY
        ELSE -1 END
    ) AS INVOICED_QTY,
    EBS.CREATED_ON_DT,
    EBS.CHANGED_ON_DT, 
    EBS.INTEGRATION_ID,
    'EBS' AS SOURCE_NAME
FROM W_SALES_INVOICE_LINE_FS EBS
LEFT OUTER JOIN W_SALES_INVOICE_LINE_F DWH
ON DWH.INTEGRATION_ID = EBS.INTEGRATION_ID
WHERE DWH.INTEGRATION_ID IS NULL
UNION ALL
SELECT DWH.NET_AMT,
    DWH.INVOICED_QTY, 
    DWH.CREATED_ON_DT,
    DWH.CHANGED_ON_DT, 
    DWH.INTEGRATION_ID,
    'DWH' AS SOURCE_NAME
FROM W_SALES_INVOICE_LINE_F DWH
LEFT OUTER JOIN W_SALES_INVOICE_LINE_FS EBS
ON EBS.INTEGRATION_ID = DWH.INTEGRATION_ID
WHERE EBS.INTEGRATION_ID IS NULL
AND DWH.IS_POS = 'N'
;

Could you provide a brief description of the tables in your question? How many (approximately) records are in each table? Do you have any indexes? Are any of the fields calculated/derived? When you do an explain plan on these or your original query, where does it show the bottleneck?

Comments