Database Reference
In-Depth Information
This seems impossible; our unique key isn't unique if we consider all Null entries. The fact is that, in Oracle,
(NULL, NULL)
is not the same as
(NULL, NULL)
when considering uniqueness—the SQL standard mandates this.
(NULL,NULL)
and
(NULL,NULL)
are considered the same with regard to aggregation, however. The two are unique for
comparisons but are the same as far as the
GROUP BY
clause is concerned. That is something to consider: each
unique
constraint should have at least one
NOT NULL
column to be truly unique.
The question that comes up with regard to indexes and Null values is, “Why isn't my query using the index?” The
query in question is something like the following:
select * from T where x is null;
This query cannot use the index we just created—the row
(NULL, NULL)
simply is not in the index, hence the use
of the index would return the wrong answer. Only if at least
one
of the columns is defined as
NOT NULL
can the query
use an index. For example, the following shows Oracle will use an index for an
X IS NULL
predicate if there is an index
with
X
on the leading edge and at least one other column in the index is defined as
NOT NULL
in the base table:
EODA@ORA12CR1> create table t ( x int, y int NOT NULL );
Table created.
EODA@ORA12CR1> create unique index t_idx on t(x,y);
Index created.
EODA@ORA12CR1> insert into t values ( 1, 1 );
1 row created.
EODA@ORA12CR1> insert into t values ( NULL, 1 );
1 row created.
EODA@ORA12CR1> begin
2 dbms_stats.gather_table_stats(user,'T');
3 end;
4 /
PL/SQL procedure successfully completed.
When we go to query that table this time, we'll discover this:
EODA@ORA12CR1> set autotrace on
EODA@ORA12CR1> select * from t where x is null;
X Y
---------- ----------
1
Execution Plan
...
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| T_IDX | 1 | 5 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------
