Several years ago I wrote a series of 8 posts about constraint creation optimization. I think it’s time to add some more posts to the series.
I showed there, among other things, that Oracle does a nice optimization – that may save plenty of time – when we add in a single ALTER TABLE statement a new (nullable with no default value) column and a corresponding:
– inline (a.k.a. “column-level”) check constraint
or
– foreign key constraint (either inline or out-of-line)
What about NOT NULL constraints?
Oracle has a special optimization for the case of adding a column with a NOT NULL constraint (and no default value).
It checks if the table contains at least one record. If it does, then we cannot add the new column, becuase the NOT NULL constraint will be violated for all the existing rows. If the table doesn’t contain any records, the new column can be added.
Let’s start with an empty table:
SQL> create table t (c1 varchar2(1000)); Table created.
Now, let’s turn SQL Trace on, and add a new column – C2 – with a NOT NULL constraint.
SQL> exec dbms_monitor.session_trace_enable PL/SQL procedure successfully completed. SQL> alter table t add (c2 number not null); Table altered. Elapsed: 00:00:00.02
In the trace file we can see the following recursive query:
=====================
PARSING IN CURSOR #2187579520792 len=72 dep=1 uid=109 oct=3 lid=109 tim=123417987924 hv=3909541963 ad='7ff6b5629a48' sqlid='6x9pr5bnhds2b'
select /*+ full(P) noparallel(P) */ 1 from "DEMO"."T" P where rownum = 1
END OF STMT
PARSE #2187579520792:c=0,e=2058,p=0,cr=6,cu=0,mis=1,r=0,dep=1,og=1,plh=508354683,tim=123417987923
EXEC #2187579520792:c=0,e=10,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=508354683,tim=123417987983
FETCH #2187579520792:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=508354683,tim=123417987994
STAT #2187579520792 id=1 cnt=0 pid=0 pos=1 obj=0 op='COUNT STOPKEY (cr=0 pr=0 pw=0 str=1 time=3 us)'
STAT #2187579520792 id=2 cnt=0 pid=1 pos=1 obj=76902 op='TABLE ACCESS FULL T (cr=0 pr=0 pw=0 str=1 time=2 us cost=2 size=0 card=1)'
CLOSE #2187579520792:c=0,e=1,dep=1,type=0,tim=123417988057
=====================
We can see that Oracle checks if the table is empty, by executing this query:
select /*+ full(P) noparallel(P) */ 1 from "DEMO"."T" P where rownum = 1
Since no rows were found (as indicated by “r=0” in the FETCH line), the new column was added successfully.
SQL> desc t
Name Null? Type
----------------------- -------- ----------------
C1 VARCHAR2(1000)
C2 NOT NULL NUMBER
Now, let’s fill the table with some rows and try to add another column – C3 – with a NOT NULL constraint:
SQL> insert /*+ append */ into t (c1,c2) 2 select lpad(rownum,1000,'x'), rownum from dual connect by level<=1e6; 1000000 rows created. SQL> commit; Commit complete. SQL> exec dbms_monitor.session_trace_enable PL/SQL procedure successfully completed. SQL> alter table t add (c3 number not null); alter table t add (c3 number not null) * ERROR at line 1: ORA-01758: table must be empty to add mandatory (NOT NULL) column Elapsed: 00:00:00.01 SQL>
Adding the column has failed, with the dedicated ORA-01758 error message.
Looking at the trace file, we can see the same query as before – that checks if the table is empty or not:
===================== PARSING IN CURSOR #2187704306680 len=72 dep=1 uid=109 oct=3 lid=109 tim=123938757516 hv=3909541963 ad='7ff6b5629a48' sqlid='6x9pr5bnhds2b' select /*+ full(P) noparallel(P) */ 1 from "DEMO"."T" P where rownum = 1 END OF STMT PARSE #2187704306680:c=0,e=892,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=508354683,tim=123938757516 EXEC #2187704306680:c=0,e=12,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=508354683,tim=123938757574 FETCH #2187704306680:c=0,e=16,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=1,plh=508354683,tim=123938757598 STAT #2187704306680 id=1 cnt=1 pid=0 pos=1 obj=0 op='COUNT STOPKEY (cr=3 pr=0 pw=0 str=1 time=17 us)' STAT #2187704306680 id=2 cnt=1 pid=1 pos=1 obj=76902 op='TABLE ACCESS FULL T (cr=3 pr=0 pw=0 str=1 time=16 us cost=2 size=0 card=1)' CLOSE #2187704306680:c=0,e=37,dep=1,type=0,tim=123938757642 EXEC #2187640642528:c=0,e=14598,p=0,cr=52,cu=22,mis=0,r=0,dep=0,og=1,plh=0,tim=123938764892 ERROR #2187640642528:err=1758 tim=123938764947
Since this time the table is not empty (note the “r=1” in the FETCH line), it is forbidden to add the new column.
The important part here is the rownum = 1 condition. It doesn’t matter if the table contains a single record or one billion records – in both cases it is forbidden to add the column, so Oracle can stop looking for existing records once the first one is found.
We can also see that we got the error message within a fraction of a second, and according to the trace file there were only 3 consistent reads for this check. If we had to scan the whole table, it would have taken much more time and resources:
SQL> select count(*) from t; COUNT(*) ---------- 1000000 Elapsed: 00:00:02.03 ===================== PARSING IN CURSOR #2187280771792 len=22 dep=0 uid=109 oct=3 lid=109 tim=125340250730 hv=2763161912 ad='7ff6c55c11b8' sqlid='cyzznbykb509s' select count(*) from t END OF STMT PARSE #2187280771792:c=12706,e=5333,p=0,cr=38,cu=0,mis=1,r=0,dep=0,og=1,plh=2966233522,tim=125340250730 EXEC #2187280771792:c=33,e=33,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2966233522,tim=125340250828 WAIT #2187280771792: nam='SQL*Net message to client' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=125340250867 WAIT #2187280771792: nam='db file scattered read' ela= 9910 file#=12 block#=136 blocks=8 obj#=76902 tim=125340260905 WAIT #2187280771792: nam='db file scattered read' ela= 301 file#=12 block#=145 blocks=7 obj#=76902 tim=125340261471 WAIT #2187280771792: nam='db file scattered read' ela= 284 file#=12 block#=152 blocks=8 obj#=76902 tim=125340261895 . . . FETCH #2187280771792:c=692417,e=2021821,p=142853,cr=142880,cu=0,mis=0,r=1,dep=0,og=1,plh=2966233522,tim=125342272710 STAT #2187280771792 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=142880 pr=142853 pw=0 str=1 time=2021820 us)' STAT #2187280771792 id=2 cnt=1000000 pid=1 pos=1 obj=76902 op='TABLE ACCESS FULL T (cr=142880 pr=142853 pw=0 str=1 time=526947 us cost=38935 size=0 card=1000000)'
So this check is very efficient, and does not depend on the number of existing records in the table.
This investigation made me think of one extreme case.
What happens if the table has no rows, but its segment does have many allocated blocks under the high water mark?
Or, similarly, the table does have rows, but they are all located on the “right part” of the segment, while the (big) “left side” contains only empty blocks.
The check in this case should take a long time, becuase many (empty) blocks will be scanned.
Let’s check. Our table T contains one million rows, that are stored in 142880 blocks. Let’s delete all the rows:
SQL> delete t; 1000000 rows deleted. Elapsed: 00:00:21.59 SQL> commit; Commit complete. Elapsed: 00:00:00.10 SQL> exec dbms_stats.gather_table_stats(user,'T') PL/SQL procedure successfully completed. SQL> alter system flush buffer_pool all; System altered.
Let’s try adding C3 now:
SQL> alter table t add (c3 number not null);
Table altered.
Elapsed: 00:00:01.99
We succeeded this time, as there are no rows in the table, but indeed it took much longer this time – 2 seconds.
And we can see in the trace file that we had 142881 consistent reads.
=====================
PARSING IN CURSOR #2187280660512 len=72 dep=1 uid=109 oct=3 lid=109 tim=127303704852 hv=3909541963 ad='7ff6b5629a48' sqlid='6x9pr5bnhds2b'
select /*+ full(P) noparallel(P) */ 1 from "DEMO"."T" P where rownum = 1
END OF STMT
PARSE #2187280660512:c=0,e=1285,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=508354683,tim=127303704851
EXEC #2187280660512:c=0,e=17,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=508354683,tim=127303704926
WAIT #2187280660512: nam='db file sequential read' ela= 586 file#=12 block#=130 blocks=1 obj#=76902 tim=127303705536
WAIT #2187280660512: nam='db file scattered read' ela= 573 file#=12 block#=131 blocks=5 obj#=76902 tim=127303706197
WAIT #2187280660512: nam='db file scattered read' ela= 572 file#=12 block#=136 blocks=8 obj#=76902 tim=127303706867
WAIT #2187280660512: nam='db file scattered read' ela= 443 file#=12 block#=145 blocks=7 obj#=76902 tim=127303707374
.
.
FETCH #2187280660512:c=553143,e=1901199,p=142859,cr=142881,cu=0,mis=0,r=0,dep=1,og=1,plh=508354683,tim=127305606133
STAT #2187280660512 id=1 cnt=0 pid=0 pos=1 obj=0 op='COUNT STOPKEY (cr=142881 pr=142859 pw=0 str=1 time=1901202 us)'
STAT #2187280660512 id=2 cnt=0 pid=1 pos=1 obj=76902 op='TABLE ACCESS FULL T (cr=142881 pr=142859 pw=0 str=1 time=1901197 us cost=38926 size=0 card=1)'
CLOSE #2187280660512:c=8,e=8,dep=1,type=0,tim=127305606381
=====================
So in this case, the time it takes to perform the check depends on the “size” of the table.
If we have an index on a NON NULL column of the table, using the index rather than the table may be more efficient, but the /*+ full(P) */ hint prevents this.
Anyway, this is a rare case. And actually, if we have this situation, then we probably need to do some maintenance anyway.