Re: Not null and index

"Charles Hooper" <hooperc2000_at_yahoo.com> wrote in message news:1190148492.574605.280970_at_g4g2000hsf.googlegroups.com...
> On Sep 18, 3:31 pm, "astalavista" <nob..._at_nowhere.com> wrote:
>> Hi,
>>
>> I have a table with a column with a constraint not null
>> and a index on this column
>>
>> there are stats ion the table
>>
>> when I do:
>>
>> select * from test where no is null
>>
>> the plan is using the index, why ?
>>
>> Thanks in advance ...
>>
>> 9.2.0.6
>
> Please provide a test case. This was performed on Oracle 10.2.0.2:
> CREATE TABLE T1 (
> C1 NUMBER(10) NOT NULL,
> C2 NUMBER(10));
>
> Table created.
>
> INSERT INTO T1
> SELECT
> ROWNUM,
> ROWNUM*2
> FROM
> DUAL
> CONNECT BY
> LEVEL<=100000;
>
> 100000 rows created.
>
> CREATE INDEX T1_IND1 ON T1(C1);
>
> Index created.
>
> EXEC
> DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE);
>
> We now have a table with a column that has a NOT NULL constraint, an
> index on that column, and up to date statistics on the table and
> index.
>
> Test run:
> SELECT /*+ GATHER_PLAN_STATISTICS */
> *
> FROM
> T1
> WHERE
> C1 IS NULL;
>
> no rows selected
>
> SELECT
> *
> FROM
> TABLE (DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
>
> ---------------------------------------------------------------------------
> | Id | Operation | Name | Starts | E-Rows | A-Rows | A-
> Time |
> ---------------------------------------------------------------------------
> |* 1 | FILTER | | 1 | | 0 |
> 00:00:00.01 |
> | 2 | TABLE ACCESS FULL| T1 | 0 | 100K| 0 |
> 00:00:00.01 |
> ---------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
> 1 - filter(NULL IS NOT NULL)
>
> As expected, the plam shows that the index was not used. Note the
> filter predicate.
>
> So, what if we force the use of the index through the use of a hint:
> SELECT /*+ GATHER_PLAN_STATISTICS INDEX(T1 T1_IND1) */
> *
> FROM
> T1
> WHERE
> C1 IS NULL;
>
> no rows selected
>
> SELECT
> *
> FROM
> TABLE (DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
>
> ----------------------------------------------------------------------------------------
> | Id | Operation | Name | Starts | E-Rows | A-
> Rows | A-Time |
> ----------------------------------------------------------------------------------------
> |* 1 | FILTER | | 1 |
> | 0 |00:00:00.01 |
> | 2 | TABLE ACCESS BY INDEX ROWID| T1 | 0 |
> 100K| 0 |00:00:00.01 |
> | 3 | INDEX FULL SCAN | T1_IND1 | 0 |
> 100K| 0 |00:00:00.01 |
> ----------------------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
> 1 - filter(NULL IS NOT NULL)
>
> Oracle used the index specified, yet NULL values are not indexed - and
> it apparently completed in the same amount of time as the full table
> scan.
>

Hi Charles

The point that appears to have missed regarding all this is that the column in question has been created with a *NOT NULL* constraint.

How many null values are going to be returned from a column that has an enabled, validated and non deferrable not null constraint ? The answer *must* be zero!!

Now Oracle is clever enough to realise this, although how clever depends on the Oracle version ...

With 9i, if the column has an index and if the not null constraint is enabled, validated and non deferrable, then Oracle will use this index to "fast-track" the point there can be no rows and quickly returns that fact via the index. If it doesn't have an index then it performs a redundant, unnecessary and potentially expensive full table scan to return now rows. If the state of the index is not as described as above, then there's a chance there could be null values and again performs a FTS.

I remember showing a quick demo of this a few years ago on AskTom (search for Foote at
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:8806498660292)

The OP is on 9.2.0.6 hence why the Index Access Path is being used.

However, with 10g, the CBO has got a little smarter. With a not null constraint (meeting the above conditions), again Oracle knows there can be no null columns and acts accordingly. Although the execution plan above suggests a FTS is being performed, in actual fact there are *no* LIOs being performed. Oracle basically says "wait a minute, why bother getting any blocks for this, there can be no rows" and simply returns the no rows message. The execution plan is simply a "default" path that isn't actually used.

With 10g, there is no need for the index, it's now redundant and the zero I/O path is determined regardless. This can be confirmed by looking at a trace of a session (like the demo above which is obviously on 10g) which says a FTS is being performed, but in actuality is kinda a big lie as no LIOs are accessed!!

As a final aside, note that it's very easy to index null values.

A table can have a column with many many rows but with only a handful of null values. The following SQL can return these handful of rows via an index:

SELECT * FROM really_big_table
WHERE col is NULL

as one would hope, without having to change the code at all (say to some nvl function-based search).

Happy to disclose how if anyone is interested and doesn't know how ...

Cheers

Richard Received on Wed Sep 19 2007 - 04:27:35 CDT