I recently had to do a insert on the database, if the records weren't there, and an update if it was.
Performance was a problem.
Colleague of mine had the same problem when using an insert and update statement with an (exists (select 1)) on 2.5 million records.
After 5 minutes, 5000 rows were done. It would have taken 41 hours to fix them all.
After a merge1 statement, the entire thing was done in 2.5 minutes.
So, I had to get to grips with the merge statement as well. I hadn't used it before.
There's plenty of tutorials on how it works.
Oracle allows a DELETE statement in the MERGE statement, which seems not to be defined in the SQL Standard.
References
- [1] Wikipedia - Merge(SQL)
- https://en.wikipedia.org/wiki/Merge_(SQL)
- [2] Oracle PL/SQL Documentation - MERGE
- https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9016.htm#SQLRF01606
No comments:
Post a Comment