Database Reference
In-Depth Information
EODA@ORA12CR1> insert into t values ( 1, NULL );
1 row created.
EODA@ORA12CR1> insert into t values ( NULL, 1 );
1 row created.
EODA@ORA12CR1> insert into t values ( NULL, NULL );
1 row created.
EODA@ORA12CR1> analyze index t_idx validate structure;
Index analyzed.
EODA@ORA12CR1> select name, lf_rows from index_stats;
NAME LF_ROWS
------------------------------ ----------
T_IDX 3
The table has four rows, whereas the index only has three. The first three rows, where at least
one
of the index key
elements was
not
Null, are in the index. The last row with
(NULL, NULL)
is not in the index. One of the areas of confusion
is when the index is a unique index, as just shown. Consider the effect of the following three
INSERT
statements:
EODA@ORA12CR1> insert into t values ( NULL, NULL );
1 row created.
EODA@ORA12CR1> insert into t values ( NULL, 1 );
insert into t values ( NULL, 1 )
*
ERROR at line 1:
ORA-00001: unique constraint (EODA.T_IDX) violated
EODA@ORA12CR1> insert into t values ( 1, NULL );
insert into t values ( 1, NULL )
*
ERROR at line 1:
ORA-00001: unique constraint (EODA.T_IDX) violated
The new
(NULL, NULL)
row is not considered to be the same as the old row with
(NULL, NULL)
:
EODA@ORA12CR1> select x, y, count(*)
2 from t
3 group by x,y
4 having count(*) > 1;
X Y COUNT(*)
---------- ---------- ----------
2
