Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Merge query performance slow for delta load

user12251389Jan 20 2021 — edited Jan 20 2021

I have below merge query which is working fine for small datasets but for example for 800000k rows its taking more than 40 minutes for execution.
In this Merge query i am trying to perform Delta import logic using Insert/Update/Delete. For new data with combination of Unique column(ID_LL_ ID_UU, TKR) i want to insert data into TEST_RUA_MER table otherwise update data. Delete will delete the data from test_rua_mer table which does not exist in test_rua table.
The query logic is working fine but its very slow.
How can i improve performance in this query ? I can also handle Delete part separately without Merge but will it slow down performance again as i need to handle again null values ?
This question is extending my previous question https://community.oracle.com/tech/developers/discussion/4479762/delta-load-logic-using-oracle-merge-delete-sql-statement where i mentioned the datasets:

MERGE
  INTO TEST_RUA_MER T
  USING (
         SELECT  T.ROWID T_RID,
                 CASE
                   WHEN S.ROWID IS NULL THEN 1
                   WHEN     DECODE(S.CLASS,T.CLASS,1) = 1
                        AND
                            DECODE(S.NAME,T.NAME,1) = 1
                     THEN 1
                 END DELETE_FLAG,
                 S.*
           FROM      TEST_RUA S
                 FULL JOIN
                     TEST_RUA_MER T
                   ON(
                          DECODE(T.ID_LL,S.ID_LL,1) = 1
                      AND
                          DECODE(T.ID_UU,S.ID_UU,1) = 1
                      AND
                          DECODE(T.TKR,S.TKR,1) = 1
                     )
        ) S
  ON (
          T.ROWID = S.T_RID
     )
  WHEN MATCHED
    THEN
      UPDATE
         SET T.CLASS = S.CLASS,
             T.NAME  = S.NAME
      DELETE WHERE S.DELETE_FLAG = 1
  WHEN NOT MATCHED
    THEN
      INSERT
      VALUES(
             S.CLASS,
             S.ID_LL,
             S.ID_UU,
             S.TKR,
             S.NAME
            )

I tried to remove the DECODE part from the query now the query is running very fast but as i removed the Decode part so its not handling null values correctly for delta logic...for example when there is null value in any fields its not deleting the data from TEST_RUA_MER table even if the row is same like in TEST_RUA table

MERGE INTO TEST_RUA_MER T
     USING (SELECT T.ROWID    T_RID,
                   CASE
                       WHEN S.ROWID IS NULL THEN 1
                       WHEN S.CLASS = T.CLASS AND S.NAME = T.NAME THEN 1
                   END        DELETE_FLAG,
                   S.*
              FROM TEST_RUA  S
                   FULL JOIN TEST_RUA_MER T
                       ON (T.ID_LL = S.ID_LL AND T.ID_UU = S.ID_UU AND T.TKR = S.TKR)) S
        ON (T.ROWID = S.T_RID)
WHEN MATCHED
THEN
    UPDATE SET T.CLASS = S.CLASS, T.NAME = S.NAME
    DELETE
             WHERE S.DELETE_FLAG = 1
WHEN NOT MATCHED
THEN
    INSERT     VALUES (S.CLASS,
                       S.ID_LL,
                       S.ID_UU,
                       S.TKR,
                       S.NAME)

Below is the data set in fiddle : https://dbfiddle.uk/?rdbms=oracle_18&fiddle=124ddf95c44e8efdc7a987b11fd4d82d

This post has been answered by Paulzip on Jan 20 2021
Jump to Answer
Comments
Post Details
Added on Jan 20 2021
19 comments
711 views