Here are some good tips to make your PL/SQL fast. Do not rely on implicit data type conversions. Also choose the smallest data type that will fit your data. The PLS_INTEGER data type is good. The SIMPLE_INTEGER type is better. Smaller types are better.
You should code less code per module for a small gain. The big boost will come from using a SQL statement to do the work instead of a whole procedure worth of PL/SQL. I struggle with this. Our data sets are huge. I am sometimes afraid to do something huge with one SQL statement. The thing may never complete. Or it may bomb in the middle and get rolled back. At least my PL/SQL routines can commit often and avoid redoing work on errors.
I thought I could fit all of this into one post. But I have more to say. Look for me to cover bulk binding next time, along with some other misc tips.
Good-fast-cheap. Pick two. - I got invited to a meeting with the customer today. There was a problem in production. And the customer wanted answers. When it came time, I explained wha...