Re: Optimizer execution-plan problem

From: Stefan Olausson <qhsstol_at_hotmail.com>
Date: 5 Nov 2001 06:13:34 -0800
Message-ID: <fda9c411.0111050613.4ac4b9ba@posting.google.com>

damien.salvador_at_via.ecp.fr (Damien Salvador) wrote in message news:<slrn9u2icq.aj0.damien.salvador_at_zen.via.ecp.fr>...
> On 1 Nov 2001 05:05:01 -0800, Stefan Olausson
> <qhsstol_at_hotmail.com> a écrit:
> >
> >But why doesnt it work the same with "WHERE a IS NULL" ?
> >Indexes cannot be used to find NULL-values or what ?
>
> Bingo ! :-)
>
> Null values are not indexed ... at least with regular indexes
>
> There is a case when Null can be indexed but I cannot seem to remember when
> :-/
> Someone out there ?

FYI: We now got this fully sorted out with Oracle support guys, so I thought I'd post an update...

The issue really wasnt quite as simple as "NULL-values are not indexed".

It "happens" to hold true for single-column indexes, but for multi-column indexes, the exact Oracle-behaviour as stated in the documentation is:

"Oracle does not index table rows in which all key columns are NULL"

Considering my test-case:

SQL> CREATE TABLE tmp_n(a NUMBER, b NUMBER);
SQL> CREATE INDEX tmp_n_ix ON tmp_n(a,b);
SQL> SELECT MIN(b) FROM tmp_n WHERE a IS NULL;

all the information to evaluate this SELECT-statement actually CAN be retrieved through the index (because the MIN()-function can be calculated by only looking at rows where the second column is non-NULL, and these rows ARE in the index).

So what's the problem with the optimizer, then?

Well, consider this statement instead:
SQL> SELECT count(*) FROM tmp_n WHERE a IS NULL;

Now, you can NOT use the index, because you would miss rows where BOTH columns are NULL (those are the ones NOT in the index).

Understandably, the optimizer isnt "smart" enough to realize the MIN()-function only depends on non-NULL values, therefore it evaluates the statement
SELECT MIN(b) FROM tmp_n WHERE a IS NULL along a similar execution plan, as the count(*)-stmt above -- i.e. it chooses the execution-plan looking at the WHERE-cause only.

So in the end, it turns out you can simply change the SELECT-statement in my test-case to: SELECT MIN(b) FROM tmp_n WHERE a IS NULL AND b IS NOT NULL; and the optimizer WILL go by the index,
even though NULL-values ARE involved in the a-column.

/Stefan Received on Mon Nov 05 2001 - 08:13:34 CST