Oracle Scratchpad

August 13, 2013


Filed under: CBO,Conditional SQL,Execution plans,NULL,Oracle — Jonathan Lewis @ 7:14 am BST Aug 13,2013

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, and table t1 is just all_objects where rownum <= 20000:


September 6, 2012

Null – again

Filed under: NULL,Oracle — Jonathan Lewis @ 5:41 pm BST Sep 6,2012

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');


September 19, 2011


Filed under: NULL,Oracle — Jonathan Lewis @ 4:58 pm BST Sep 19,2011

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.

February 21, 2011


Filed under: Infrastructure,NULL,Oracle,Troubleshooting — Jonathan Lewis @ 7:15 pm BST Feb 21,2011

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;


1 row selected.

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

Table altered.

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


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’)).

January 2, 2011


Filed under: NULL — Jonathan Lewis @ 7:14 pm BST Jan 2,2011

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:

October 15, 2010

Good Nulls

Filed under: CBO,Function based indexes,Indexing,NULL,Tuning — Jonathan Lewis @ 6:17 pm BST Oct 15,2010

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.

June 1, 2009

Hints and Nulls

Filed under: CBO,Hints,Ignoring Hints,Indexing,NULL,Troubleshooting — Jonathan Lewis @ 7:16 pm BST Jun 1,2009

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"]

The Rubric Theme. Create a free website or blog at


Get every new post delivered to your Inbox.

Join 3,528 other followers