Oracle/DB2 null key index

This issue is coming from when migrate DB2 unique index to Oracle.

@H_502_11@Concept Definition

- KEY of an index: A "KEY" is the set of columns making up an index. That is it may be one column or multiple columns.

- NULL KEY of an index: a "NULL KEY" that is a key where all columns are NULL.

@H_502_11@DB2 only one "NULL KEY" may exist in a unique index.

When table and index are created as

CREATE TABLE TAB (A DECIMAL(6) not null,B CHAR(10),C DECIMAL(6),PRIMARY KEY(A));

CREATE UNIQUE INDEX IDX ON TAB(B,C);

Following sql will fail on 2nd statement:

INSERT INTO TAB VALUES(1,NULL,NULL);
INSERT INTO TAB VALUES(2,NULL);

This is the error message:

DB21034E  The command was processed as an sql statement because it was not a valid Command Line Processor command.
During sql processing it returned:sql0803N
One or more values in the INSERT statement,UPDATE statement,or foreign key update caused by a DELETE statement
are not valid because the primary key,unique constraint or unique index identified by "IDX" 
constrains table "0000000201" from having duplicate values for the index key.  sqlSTATE=23505

@H_502_11@Oracle multiple NULL keys may exist in a unique index.

The same operation above can be executed on Oracle without any failure.

This is because Oracle does not index NULL KEYS; whatever unique or not,an Oracle index does not include rows if all indexed columns are null.
This cause a result is that UNIQUE index in Oracle simply does not know about NULL keys. It's literallxy blind to the multiple NULL KEYS issue.

@H_502_11@DB2 "unique where not null" index

UNIQUE WHERE NOT NULL is supported in DB2 for z/OS,which allows for duplicate null values to exist on a unique constraint.

So if we create index using:

CREATE UNIQUE WHERE NOT NULL INDEX IDX ON TAB(B,C)

Then we can get same result as Oracle,i.e,; both insert statements can be executed successfully.

@H_502_11@Oracle support non-duplicated null index

Solution: to add a constant value field into index,so that the index key could not be null.

For example we create above index using:

CREATE UNIQUE INDEX IDX ON TAB(B,C,1)

The constant '1' does not means the 1st column,it is a constant; so the index key must not be null because the 3rd column is a constant 1.

@H_502_11@The solution for migrating UNIQUE index

If all indexed columns are null-able,a constant 1 should be added into Oracle index definition.

For example DB2 index definition

CREATE UNIQUE INDEX IDX ON TAB(B,C)

if both column B and C are null-able,this index should be redefined in Oracle as:

CREATE UNIQUE INDEX IDX ON TAB(B,1)

And if there is either column is "not null",it's not necessary to add the constant 1.

@H_502_11@In Summary

CREATE UNIQUE WHERE NOT NULL INDEX IDX ON TAB(B,C) # on DB2

=equal to=

CREATE UNIQUE INDEX IDX ON TAB(B,C) # on Oracle

CREATE UNIQUE INDEX IDX ON TAB(B,C) # on DB2

=equal to=

CREATE UNIQUE INDEX IDX ON TAB(B,1) # on Oracle

Oracle/DB2 null key index

猜你在找的Oracle相关文章