Oracle PL/SQL - package constants defined as (implicit) not null-CodePudding

Is there any positive or negative (performance) impact if constants in Oracle packages/procedures/functions are defined as (implicit) not null? E.g.

// Varchar2
C_LF_V1 constant varchar2(1)          := chr(10);
C_LF_V2 constant varchar2(1) not null := chr(10);
// PLS number
C_MAX_PLS_STR_LENB_V1 constant pls_integer          := 32767;
C_MAX_PLS_STR_LENB_V2 constant pls_integer not null := 32767;
C_MAX_PLS_STR_LENB_V3 constant positiven            := 32767;
C_MAX_PLS_STR_LENB_V4 constant simple_integer       := 32767;
// Date
C_MAX_DATE_V1 constant date          := date '9999-12-31'
C_MAX_DATE_V2 constant date not null := date '9999-12-31'
// ...

The documentation is not specific for such details at all, as (unfortunately) way too often - having checked from 11 to 19 (in case it actually is version specific, implementation as of 19 would be of interest).

Of course, running some tests is possible/easy, but that would only provide platform/version/machine specific results, whereas the low-level-implementation/conceptual logic is much more interesting, i.e.

will (implicit) not null be compiled as known to be not-NULL or will it cause a NULL check upon every access?

Update (clarification):

my questions relates to constants that are known to be NOT NULL (so it's not about NULL handling)
I'm not after "wannabe" nanoseconds tuning at all - I'd like to know what actually happens behind the scene; e.g. for simple_integer the doc says that it may make heavy computations more efficient, which implies that upon compilation it "knows" that such a variable can never be NULL -> so does this implication also apply to e.g. a date defined as NOT NULL? - or a bit more tricky to a varchar2? (where defining a NOT NULL subtype is allowed but does not make sense as the nullable attribute is ignored)

CodePudding user response：

I don't know.

Just thinking aloud.

This compiles:

SQL> create or replace package pkg_test as
  2    c_v1 constant varchar2(1) := null;
  3  end;
  4  /

Package created.

Specifying the constant to be NOT NULL and assigning NULL to it won't work:

SQL> create or replace package pkg_test as
  2    c_v1 constant varchar2(1) not null := null;
  3  end;
  4  /

Warning: Package created with compilation errors.

SQL> show err
Errors for PACKAGE PKG_TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/8      PL/SQL: Declaration ignored
2/41     PLS-00382: expression is of wrong type

Although empty strings are treated as NULLs, this will not raise any errors:

SQL> create or replace package pkg_test as
  2    c_v1 constant varchar2(1) not null := '';
  3  end;
  4  /

Package created.

SQL>

Does it make sense? A constant whose value is NULL? Maybe, maybe not, I can't remember I used it.

As of a package body: let's reuse the last code and add a procedure:

SQL> create or replace package pkg_test as
  2    c_v1 constant varchar2(1) not null := '';
  3
  4    procedure p_test;
  5  end;
  6  /

Package created.

SQL> create or replace package body pkg_test is
  2    procedure p_test is
  3    begin
  4      c_v1 := 'x';
  5    end;
  6  end;
  7  /

Warning: Package Body created with compilation errors.

SQL> show err
Errors for PACKAGE BODY PKG_TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/5      PL/SQL: Statement ignored
4/5      PLS-00363: expression 'C_V1' cannot be used as an assignment
         target

SQL>

Right; it doesn't even make sense - it is a constant, you aren't supposed to modify its value.

Documentation says:

The information in "Declaring Variables" also applies to constant declarations, but a constant declaration has two more requirements: the keyword CONSTANT and the initial value of the constant.
(...)
In a variable declaration, the initial value is optional unless you specify the NOT NULL constraint . In a constant declaration, the initial value is required.

It looks like Oracle treats variable and constant declarations almost the same, with some differences. As if constants "inherited" NOT NULL constraint from variables. Does it make sense? Can't tell.

More from the same document:

If the declaration is in a package specification, the initial value is assigned to the variable or constant for each session (whether the variable or constant is public or private).

So: it is assigned once for the session. It is a constant, after all ...

Therefore, from my point of view, I wouldn't expect any performance impact on whether you impose a NOT NULL constraint to a constant or not. I'd say that there are other things to worry about, such as tuning queries within the package body; they, if written poorly, will have significant impact on performance. Constants being NULL or NOT NULL will be the least of your worries.

CodePudding user response：

There are some minor compile time optimizations that can be utilised, but its unlikely they'd have any significant benefits, eg if you do something like

create or replace 
package PKG is
  x constant int  not null := 0;
  procedure p;
end;
/

create or replace 
package body PKG is

 procedure p is
   y int;
 begin
 for i in 1 .. 1000000
 loop
   if x is not null then
   if x is not null then
   if x is not null then
   if x is not null then
   if x is not null then
   if x is not null then
   if x is not null then
   if x is not null then
   if x is not null then
   if x is not null then
   if x is not null then
   if x is not null then
   if x is not null then
   if x is not null then
   if x is not null then
   if x is not null then
   if x is not null then
   if x is not null then
   if x is not null then
   if x is not null then
   if x is not null then
   if x is not null then
   if x is not null then
   if x is not null then
   if x is not null then
   if x is not null then
   if x is not null then
   if x is not null then
   if x is not null then
   if x is not null then
   if x is not null then
   if x is not null then
   if x is not null then
   if x is not null then
   if x is not null then
   if x is not null then
   if x is not null then
   if x is not null then
   if x is not null then
   if x is not null then
   if x is not null then
   if x is not null then
   if x is not null then
   if x is not null then
   if x is not null then
   if x is not null then
   if x is not null then
   if x is not null then
   if x is not null then
   if x is not null then
   if x is not null then
   if x is not null then
   if x is not null then
   if x is not null then
   if x is not null then
   if x is not null then
   if x is not null then
   if x is not null then
   if x is not null then
   if x is not null then
      y := i;   
   end if;
   end if;
   end if;
   end if;
   end if;
   end if;
   end if;
   end if;
   end if;
   end if;
   end if;
   end if;
   end if;
   end if;
   end if;
   end if;
   end if;
   end if;
   end if;
   end if;
   end if;
   end if;
   end if;
   end if;
   end if;
   end if;
   end if;
   end if;
   end if;
   end if;
   end if;
   end if;
   end if;
   end if;
   end if;
   end if;
   end if;
   end if;
   end if;
   end if;
   end if;
   end if;
   end if;
   end if;
   end if;
   end if;
   end if;
   end if;
   end if;
   end if;
   end if;
   end if;
   end if;
   end if;
   end if;
   end if;
   end if;
   end if;
   end if;
   end if;
end loop;
end;
end;
/

set timing on
exec pkg.p;

you'll see that removing CONSTANT in the constant definition yields a faster result, but we're still talking micro/nanoseconds of benefit per call. Similarly, if it is not a constant but has the NOT NULL you get the same benefit.

The main intent of such declarations is just another form of code and data correctness rather than performance.