Oracle/DB2 null key index

关注

原创最新推荐文章于 2026-09-03 11:14:26 发布 1.3k 阅读 AI 写同款· GEO 优化 ›

大数据-玩转数据-Oracle系统知识小结一、说明 Oracle数据库是甲骨文的核心产品，Oracle 的操作都遵循sql标准，所以各个版本在基本操作方面都变化不大。Oracle划分在大型关系数据库范畴，比较Mysql或Nosql数据库来说，还是缺少些扩容的灵活性，但传统金融、通信、电力、航空、保险、大型企业用得比较多。二、背景知识、数据建模和Linux 1.关系数据库关系型数据库，是指采用了关系模型来组织数据的数据库，其以行和列的形式存储数据，以便于用户理解，关系型数据库这一系列的行和列被称为表，一组表组成了数据库。用户通过查询来检索数据库中阅读详情

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

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.

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, 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

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.

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.

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.

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,C,1)

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

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,C,1) # on Oracle

收录于

DB2

Oracle

Database

本内容遵循 CC 4.0 BY-SA 版权协议

DataX DB2Writer深度解析：原理、避坑与生产级调优 DB2Writer是DataX框架中专用于向IBM DB2写入数据的离线同步插件，其本质并非通用迁移工具，而是基于JDBC封装的、深度适配DB2特性的生产级写入组件。它通过内置连接池、SQLCODE智能重试、MERGE语义实现、LOB流式处理及字符集透传等机制，将DB2的锁机制、日志约束、元数据依赖等底层复杂性封装为可配置能力。技术价值在于降低ETL开发对DB2内核细节的耦合度，提升全量/增量同步任务的稳定性与断点续传可靠性。典型应用于金融、电信等行业中Oracle/Hive→DB2的报表底表构建、核心系统阅读详情