NVL()

Here’s a little detail about how the optimizer can handle the nvl() function that I hadn’t noticed before (and it goes back to at least 8i). This is running on 11.2.0.3, and table t1 is just all_objects where rownum <= 20000:

Null – again

Here’s a (camouflaged) constraint definition I came across a little while ago in a production system:

create table t1 (
	v1 varchar2(10),
	constraint c1 check (v1 = 'A' or v1 = null)
);

Quick question – will the following insert statement work or return an error ?

insert into t1 values('B');

NULL

A recent question from OTN:

What’s the difference between “{expression} is null” and “{expression}= null” ?

Technically we can say the following:

• {expression} is null will evaluate to one of TRUE or FALSE
• {expression} = null will evaluate to NULL

As a follow on from this: a predicate is satisfied if (and only if) it evaluates to TRUE; a constraint is satisfied if (and only if) it doesn’t evaluate to FALSE.

Three-valued logic catches everybody out occasionally.

Constraints

There’s an important detail about constraints – check constraints in particular – that’s easy to forget, and likely to lead you into errors. Here’s a little cut-n-paste demo from an SQL*Plus session:

SQL> select count(*) from t1;

  COUNT(*)
----------
         4

1 row selected.

SQL>
SQL> alter table t1 add constraint t1_ck_v1 check (v1 in ('a','b','c'));

Table altered.

SQL>
SQL> select count(*) from t1 where v1 in ('a','b','c');

  COUNT(*)
----------
         3

1 row selected.

We count the number of rows in a table – and it’s four.
We add a constraint to restrict the values for a certain column – and every column survives the check.
We use the corresponding predicate to count the number of rows that match the check constraint – and we’ve lost a row !

Why ?

A predicate returns a row if it evaluates to TRUE.
A constraint allows a row if it does not evaluate to FALSE - which means it is allowed to evaluate to TRUE or to NULL.

I have one row in the table where v1 is null, and for that row the check constraint evaluates to NULL, which is not FALSE. So the row passes the check constraint, but doesn’t get returned by the predicate. I think it’s worth mentioning this difference because from time to time I see production systems that “lose” data because of this oversight.

To make the constraint consistent with the predicate I would probably add a NOT NULL declaration to the column or rewrite the constraint as (v1 is not null and v1 in (‘a’,’b’,’c’)).

nullif()

Have you ever written SQL that had to protect against a “divide by zero” error ?

The way I used to do this was typically to introduce a decode() function to produce some vaguely appropriate result if the divisor were zero; but there is a tidier option that appeared a few years ago. For example:

Good Nulls

I’ve often been heard to warn people of the accidents that can happen when they forget about the traps that appear when you start allowing columns to be NULL – but sometimes NULLs are good, especially when it helps Oracle understand where the important (e.g. not null) data might be.

An interesting example of this came up on OTN a few months ago where someone was testing the effects of changing a YES/NO column into a YES/NULL column (which is a nice idea because it allows you to create a very small index on the YESes, and avoid creating a histogram to tell the optimizer that the number of YESes is small).

They were a little puzzled, though, about why their tests showed Oracle using an index to find data in the YES/NO case, but not using the index in the YES/NULL case. I supplied a short explanation on the thread, and was planning to post a description on the blog, but someone on the thread supplied a link to AskTom where Tom Kyte had already answered the question, so I’m just going to leave you with a link to his explanation.

Hints and Nulls

From time to time I check the site statistics to see if they give me any clues about why people are coming to blog – and recently I noticed that over the last year a particular referral from the OTN Database forum was had appeared fairly regularly – and it’s one that covers a small but significant optimizer detail that combines two crtical questions:  why is the optimizer not using my index and why is the optimizer ignoring my hint under the heading “Index hint does not work”.

I’ll leave you to read the thread – but the short answer is NULL.

A hint is illegal if using it could produce the wrong answer, and indexes where every column is nullable won’t necessarily reference every row in its table.

[Further reading on "ignoring hints"]