Re: Not null and index

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Tue, 18 Sep 2007 13:48:12 -0700
Message-ID: <1190148492.574605.280970@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'));

|* 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.

Repeat the above on your system. Does Oracle use the index even when not hinted?

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Tue Sep 18 2007 - 15:48:12 CDT