Performance Impact of NOT NULL Constraint in PL/SQL

A NOT NULL constraint in PL/SQL requires that a variable contains value, if no value is specified for that variable then an error will occur. But, using this constraint in PL/SQL, we have to pay a small performance price.

Let us create two procedures and perform a small test, the first procedure (TEST_NOTNULL) will be contain "NOT NULL" constraint while the second procedure (TEST_NULL) will be without it.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL>
SQL> create or replace procedure test_notnull(p_iterations in number) is
  2    i  number;
  3    x  number NOT NULL := 0;
  4    t1 number;
  5  begin
  6    t1 := dbms_utility.get_time;
  7    for i in 1..p_iterations loop
  8      x := x + i;
  9    end loop;
 10    dbms_output.put_line( 'Time taken : ' || to_char(dbms_utility.get_time - t1));
 11  end;
 12  /

Procedure created.

SQL>
SQL>
SQL> create or replace procedure test_null(p_iterations in number) is
  2    i  number;
  3    x  number := 0;
  4    t1 number;
  5  begin
  6    t1 := dbms_utility.get_time;
  7    for i in 1..p_iterations loop
  8      x := x + i;
  9      if x is null then
 10        dbms_output.put_line('i know this will never happen, but still ... ');
 11      end if;
 12    end loop;
 13    dbms_output.put_line( 'Time taken : ' || to_char(dbms_utility.get_time - t1));
 14  end;
 15  /

Procedure created.

SQL>
SQL>
SQL>
SQL> set serveroutput on
SQL> exec perf_notnull(1000000);
Time taken : 20

PL/SQL procedure successfully completed.

SQL> exec perf_null(1000000);
Time taken : 17

PL/SQL procedure successfully completed.

SQL>

Executing these procedures, we notice that TEST_NOTNULL procedure consumes a little more than its counterpart TEST_NULL. Yes, we are looping heavily as the point is to compare performance of NOT NULL contraint in PL/SQL.

The reason the NOT NULL Constraint incurs performance cost is: As x is constriant by NOT NULL, the value of the expression "x + i" is first assigned to a temporary variable, this temporary variable is validated against nullity. If the variable is not null, its value is assigned to "x", otherwise an exception is raised. So, this is where the extra time is consumed when one applies NOT NULL constraint in PL/SQL.

An alternative approach is to manually test for nullity and raise an exception as we did in TEST_NULL procedure.