Oracle Scratchpad

September 5, 2010

Not NULL

Filed under: Execution plans,Infrastructure — Jonathan Lewis @ 5:56 pm BST Sep 5,2010

Here’s a little detail that I’ve known for years – but keep forgetting until I embarrass myself by rediscovering it (usually in front of a client). I’ll start with a demonstration of a useful little feature of mandatory columns:


drop table t1 purge;

create table t1
as
select
	*
from
	all_objects
where
	rownum <= 10000
;

execute dbms_stats.gather_table_stats(user,'t1')

create index t1_i1 on t1(object_name);

set autotrace traceonly explain

select count(*) from t1;

/*

--------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Cost (%CPU)| Time
--------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |     1 |    13   (0)| 00:00:0
|   1 |  SORT AGGREGATE       |       |     1 |            |
|   2 |   INDEX FAST FULL SCAN| T1_I1 | 10000 |    13   (0)| 00:00:0
--------------------------------------------------------------------

*/

Oracle can use the index on column object_name to count the number of rows in the table because the column has been declared NOT NULL, so every row in the table also has to appear in the index. Let’s just demonstrate that by changing the column definition:


alter table t1 modify object_name null;
select count(*) from t1;

/*

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    40   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T1   | 10000 |    40   (0)| 00:00:01 |
-------------------------------------------------------------------

*/

Now let’s make the column mandatory again – by adding a constraint:


alter table t1 add constraint t1_ck_on_nn check(object_name is not null);
select count(*) from t1;

What do you think the execution path is going to be ?

Oracle uses a full table scan – even if you supply the optimizer with an index hint.

The special NOT NULL optimisation only applies if the column is declared not null – an ordinary constraint isn’t just good enough.

You may remember a short note I wrote a few days ago about adding a not null constraint to a table with 200 million rows without causing the system to stop for a few minutes. I did it with the intent of giving the optimizer a few extra options for exection plans – but now I need to go back and see whether or not the constraint has any effect at all on any of the plans we generate.

Update 6th Sept 2010

Some of the early comments have highlighted the importance of being very careful with language when describing a problem. In this case I haven’t made the distinction between an “ordinary constraint” and a “not null declaration” clear enough.  To ensure that a column does not allow nulls you can add a check constraint, or you can declare it “not null” – which, in effect, is a special type of constraint. When you declare a column to be not null Oracle will create a check constraint for that column, but also set the null$ column in sys.col$ to be non-zero.

A quick cut and paste job to show the differences (in 11.1)

SQL> alter table t1 modify n1 not null;

Table altered.

SQL> desc t1
 Name                    Null?    Type
 ----------------------- -------- ----------------
 N1                      NOT NULL NUMBER
 N2                               NUMBER
 V1                               VARCHAR2(180)

SQL> alter table t1 modify n1 null;

Table altered.

SQL> alter table t1 add constraint t1_ck_n1_nn check(n1 is not null);

Table altered.

SQL> desc t1
 Name                    Null?    Type
 ----------------------- -------- ----------------
 N1                               NUMBER
 N2                               NUMBER
 V1                               VARCHAR2(180)

SQL> alter table t1 drop constraint t1_ck_n1_nn;

Table altered.

SQL> alter table t1 modify n1 not null enable novalidate;

Table altered.

SQL> desc t1
 Name                    Null?    Type
 ----------------------- -------- ----------------
 N1                               NUMBER
 N2                               NUMBER
 V1                               VARCHAR2(180)

SQL

I haven’t queried user_constraints to show it but in all these cases there will be a check constraint with the search_condition: “N1″ IS NOT NULL. As you can see, though, the only time that the describe command shows the column as NOT NULL (and col$.null$ becomes non-zero) is when you use the not null declaration with the implicit attributes of enable, validate.

It’s only this declaration/setting of null$ that allows the optimizer to do its special optimisation. You might have thought that the optimizer could be programmed to read the constraint information to look for a “check column is not null” constraint to do the same trick but (at present) it simply doesn’t work that way.

13 Comments »

  1. I found this news troubling, because I often design tables with all constraints named, so that the client application can be better informed as to why the operation failed (easier to compose a user-friendly message when the constraint is my_tab_nn_first_name rather than SYS_C00234336). I was briefly concerned that I had been creating tables with “inferior” constraints.

    However, the test below put those fears to rest. Unfortunately, it won’t help with avoiding the table lock.

    SQL> alter table t1 modify object_name constraint foo not null;
    
    Table altered.
    
    SQL> select count(*) from t1;
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3812644976
    
    ------------------------------------------------------------------
    | Id  | Operation	 | Name  | Rows  | Cost (%CPU)| Time	 |
    ------------------------------------------------------------------
    |   0 | SELECT STATEMENT |	 |     1 |     6   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE  |	 |     1 |	      | 	 |
    |   2 |   INDEX FULL SCAN| T1_I1 | 10000 |     6   (0)| 00:00:01 |
    ------------------------------------------------------------------
    
    SQL> set pages 200 autotrace off
    SQL> select * from user_constraints where constraint_name = 'FOO' and table_name = 'T1';
    
    OWNER			       CONSTRAINT_NAME		      C
    ------------------------------ ------------------------------ -
    TABLE_NAME
    ------------------------------
    SEARCH_CONDITION
    --------------------------------------------------------------------------------
    R_OWNER 		       R_CONSTRAINT_NAME	      DELETE_RU STATUS
    ------------------------------ ------------------------------ --------- --------
    DEFERRABLE     DEFERRED  VALIDATED     GENERATED      BAD RELY LAST_CHAN
    -------------- --------- ------------- -------------- --- ---- ---------
    INDEX_OWNER		       INDEX_NAME		      INVALID
    ------------------------------ ------------------------------ -------
    VIEW_RELATED
    --------------
    ARISODS 		       FOO			      C
    T1
    "OBJECT_NAME" IS NOT NULL
    									ENABLED
    NOT DEFERRABLE IMMEDIATE VALIDATED     USER NAME	       05-SEP-10
    

    Comment by Corey — September 5, 2010 @ 8:47 pm BST Sep 5,2010 | Reply

  2. Shouldn’t Oracle extend the query with (non-deferred, enabled, and validated) check constraint predicates, resulting in
    select count(*) from t1 where object_name is not null;
    ?
    Shouldn’t that query then be able to use the index?
    A bug?

    Comment by Flado — September 5, 2010 @ 9:11 pm BST Sep 5,2010 | Reply

  3. Great reminder and yet another great post…short but sweet and worth its weight in gold;)

    Fascinating how often Oracle RDBMS discussions degrade to “how many angels can dance on the head of an Oracle pin” and these types of fundamental lessons are forgotten.

    Comment by ellis — September 5, 2010 @ 9:12 pm BST Sep 5,2010 | Reply

  4. Interesting test !

    Constraint
    Name C SEARCH_CONDITION STATUS DEFERRABLE DEFERRED VALIDATED GENERATED
    —————————— – ——————————————————————————– ——– ————– ——— ————- ————–
    SYS_C002299687 C “OBJECT_NAME” IS NOT NULL ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED GENERATED NAME —>

    Constraint
    Name C SEARCH_CONDITION STATUS DEFERRABLE DEFERRED VALIDATED GENERATED
    —————————— – ——————————————————————————– ——– ————– ——— ————- ————–
    T1_CK_ON_NN C object_name is not null ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME —>

    Does that mean,CBO only considers IMPLICIT CHECK constraint (dba_constraints.generated=’GENERATED NAME’) and does not look at EXPLICIT check constraint (dba_constraints.generated=’USER NAME’)while deciding index access ?

    Even in case of explicit check constraint (T1_CK_ON_NN), CBO has surety that index does not contain any NULL values and thus it should use the index…isn’t it ?

    Is this a BUG or expected behavior ?

    Comment by Bhavik Desai — September 6, 2010 @ 6:52 am BST Sep 6,2010 | Reply

  5. cmp. also the discussion on this topic on http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2665514800346181577#2666583500346063752

    I find it interesting to mention that since the use of LNNVL is now officially supported, one could also specify a not-null-constraint for example via
    alter table t1 add constraint ch_on_nn check(lnnvl(object_name is null));

    (works at least in 11.2)

    Comment by Sokrates — September 6, 2010 @ 9:49 am BST Sep 6,2010 | Reply

  6. considering the potential impact of

    alter table test enable novalidate constraint test_pk
    followed by
    alter table part_tab exchange partition p_max with table test without validation

    might be one of the reasons not to rely on constraint information on a follwing select count(*)

    at least with primary key constraints (ok I probably should have sticked to the not null constraints) the ‘NOT VALIDATE’ on the table seems not to be transfered to user_constraints of the partitioned table.

    Comment by Stefan — September 7, 2010 @ 3:58 pm BST Sep 7,2010 | Reply

  7. Stefan

    Nice thought.

    It’s always a good idea to ask the question: “can I think of any circumstances which explain why feature X does not behave the way I might expect?” You’ve certainly found an area that’s worth looking at a little more closely by doing exactly that.

    Comment by Jonathan Lewis — September 7, 2010 @ 5:52 pm BST Sep 7,2010 | Reply

  8. but I should have tested the exact topic at question, not with another type of constraint
    I tested and it doesn’t work like that with not null constraints, exchange fails with check constraint mismatch or type mismatch depending on intial not null check or declaration(why it worked above in my case, the remaining index …)

    however I like to mention, upon disabling a not null declared column via the system generated constraint name, then the nullable flag is set to Y. Upon reenabling it is back there to N. (also null$ switches from 1 to 0 to 1) So the “declarative” nature of the constraint must be stored somewhere as well, I just do not know where.
    (10.2.0.4)

    Comment by Stefan — September 7, 2010 @ 10:11 pm BST Sep 7,2010 | Reply

  9. “the importance of being very careful with lnaguage”

    ;)

    Comment by Nigel — September 13, 2010 @ 12:58 pm BST Sep 13,2010 | Reply

  10. [...] enticing features for the DBAs and the performance tuners, and so its no wonder that Jonathan Lewis re-discovers that not all “not null” are the [...]

    Pingback by Log Buffer #202, A Carnival of The Vanities for DBAs — February 26, 2013 @ 4:28 pm BST Feb 26,2013 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,528 other followers