Friday 26 August 2011

Should I explicitly assign a default value of null

Question:
I have some questions about variable initialization. PL/SQL, by default, initializes all variables to null.
Thus:
(A) l_variable varchar2(10);
is equivalent to:
(B) l_variable varchar2(10) := null;
Suppose that 100 users are running 100 to 200 procedures, eight hours a day. Furthermore, almost every procedure and function does the type of initialization as shown in (B). My questions are:
1. Is (B) inefficient? I see this statement as being similar to:
l_var := 'a';
l_var := 'a'; a redundancy
2. Is there a performance issue (no matter how minor) when initializing in this manner?
Answer:
Nelson,
In Oracle Database 10g, the PL/SQL optimizing compiler guarantees that there's no penalty for writing a declaration as v := null. If you prefer to explicitly self-document your code in this fashion, then go ahead and write it. Personally, I prefer to rely on the documented semantics: variables are initialized to null. By taking this approach, I have less source code to maintain and one fewer coding standard rule about which to obsess.
By the way, the assignment of null as a default value in the declaration of a variable is not the only circumstance under which the compiler will optimize away such a statement. If the executable section of your program contains the statement v := null and it is the first direct mention of v, and if the compiler can prove that no side effect might have changed its value by this point, then here, too, the superfluous assignment is optimized away.
I also tested the impact of explicitly assigning a null default value for a variable under Oracle9i Database. Without the benefits of Oracle Database 10g's optimizing compiler, the explicit assignment did indeed increase the elapsed time of my test programs. When the test program included even a minimal amount of activity beyond the null assignment, however, the overhead of this statement was on the order of 2%. In a real-world program involving SQL statements and a substantial volume of code, I would expect that regardless of the approach you take in initializing your variables to null, the performance of your program will be largely unchanged.
One other result came out of my tests: when I ran my programs on Oracle Database 10g, they consistently completed in half the time as when run on Oracle9i Database. So if you are not yet running Oracle Database 10g, I strongly encourage you to do everything you can to upgrade as soon as possible!

No comments:

Post a Comment