Performance Issues with MERGE Procedure in Oracle - Wrong query?

Question

I am currently facing performance issues with an Oracle stored procedure that performs a MERGE operation between two tables. The purpose of this procedure is to archive changes from one table to another, but it is taking too much time it never ends. I have two tables: the first table gets overwritten periodically, and I want to create a new table that detects changes and archives them.

SOURCE: My source table is a view, This view is build like this:

Select t.field1, t.key1, t.date, t.type, t.component From table A Union ALL Select some complex manipulation as t.field1 t.key1, t.date, t.type, t.component from table b 

DESTINATION: my destination is a table with the same structure as the view. In this table we should write a new row everytime an update or insert is made on the view.

How should I do this?

Any help would be much apreciated.

thanks

I've tried this but it takes too long,

CREATE OR REPLACE PROCEDURE ArchiveData AS BEGIN MERGE INTO target_table t USING source_table s ON (t.key1 = s.key1 AND t.date = s.date AND t.type = s.type AND t.component = s.component) WHEN NOT MATCHED THEN INSERT ( t.field1, t.key1, t.date, t.type, t.component, ) VALUES ( s.field1, s.key1, s.date, s.type, s.component, ); END ArchiveData; 

I waited like 2 hours and it's still running, even if the tables involved are just 5k rows.

If I try to build the view without the UNION ALL, so getting the data just from tableA or just from tableB it works perfectly fine, and it runs in 0.2 seconds. But if I build the view using the UNION ALL when i run the procedure it never ends

Answer 1

I am currently facing performance issues with an Oracle stored procedure that performs a MERGE operation between two tables. The purpose of this procedure is to archive changes from one table to another, but it is taking a significantly long time to complete.

The tables that you are trying to modify may have been modified by another session but that session has not issued a COMMIT or ROLLBACK so the table/row is locked and the other session is waiting for the lock to be released.

Try running the procedure and if the code is waiting "forever" then go to whatever other clients you are using (i.e. SQL Developer, SQL*Plus, etc. or whichever tool you have that has an open session with uncommitted data) and run a COMMIT (or ROLLBACK) command and see if the procedure then suddenly completes. If it does then it was waiting for the lock to be released on the table/row.

If you don't know which session has locked the row then you can try closing everything else and, when the connection is closed, it will implicitly ROLLBACK any changes. If there are multiple users using the table then ask all of them to COMMIT their sessions. If this still doesn't work then you can try terminating all the inactive sessions connected to the database (ask your colleagues first just so you don't kill something that someone is actively using).