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