Oracle database: indexed table with null values ​​(in the main key column with multiple columns)

Question

How to set a column as "" (empty string equivalent to NULL in Oracle) when this column is part of a primary key with multiple columns? This is motivation ...

CREATE TABLE entities ( column1 VARCHAR2(10) , column2 VARCHAR2(10) , body VARCHAR2(4000) , CONSTRAINT pk_entities -- can't do this, because sometimes PRIMARY KEY ( column1, column2 ) -- col2 is the empty string (NULL). ) ORGANIZATION INDEX ... 

I usually use a “real” primary key as a meaningless sequential identifier (see this question ), and then put a unique constraint on my data columns, like this ...

 CREATE TABLE entities ( , id NUMBER PRIMARY KEY , column1 VARCHAR2(10) , column2 VARCHAR2(10) , body VARCHAR2(4000) , CONSTRAINT unq_entities UNIQUE ( column1, column2 ) ) ORGANIZATION INDEX ... 

However, this is a large indexed table (IOT), so the primary key should be in the data columns (in the IOT, the data in the index) or ... What should I do?

Thanks! ♥

Answer 1

Unfortunately, this is a "feature" inherent in Oracle. Unlike other DBMSs, it does not support a string value of zero length and insists on converting it to zero.

You can create another column as a flag to represent a row of zero length, and then save spaces instead of the column itself. I do not think there is a perfect workaround. It's a little surprising that Oracle has not fixed this strange limitation - especially given the huge improvements they made in other ways to conform to the SQL standard.

Answer 2

replace the empty string with a dummy string and check it later ... depends on how the beating table is used.

Answer 3

Why do you need a table for IOT? UNIQUE INDEX in column 1, column2 with a heap table is likely to be almost as efficient for retrieving data.

IOTs (similar to a table with a clustered index) are the exception, not the norm in Oracle.