In the first post of this "Joy" series, I explained the problem: a procedure that runs in a daily batch job threatening to take more than a day to complete. Not a good situation. I invited readers to analyze the situation and come up with their own solution before I continued. Now I continue! My Dismay and My Delight – First, the Dismay I was dismayed when I saw the implementation of reset_meme_status because I found in that procedure a cursor FOR loop that contained two non-query DML statements (. That is a classic "anti-pattern", meaning a general pattern of coding that should be avoided. It should be avoided because the inserts and updates are changing the tables on a row-by-row basis, which maximizes the number of context switches and consequently greatly slows the performance of the code. Fortunately, this classic anti-pattern has a classic, well-defined solution: use BULK COLLECT and FORALL to switch from row-by-row processing to bulk proces...
For the last twenty years, I have managed to transform an obsession with PL/SQL into a paying job. How cool is that?