How to alter the primary key of an index-organized table

oracle

We started with a naive primary key:

SQL> CREATE TABLE books(
        title VARCHAR2(10),
        CONSTRAINT pk_title PRIMARY KEY(title))
      ORGANIZATION INDEX;

Table created.

Then realized it is not unique. Say, a book can be re-released under the same title:

SQL> ALTER TABLE books ADD(release_date DATE NOT NULL);

Table altered.

Now to insert a re-released book, we have to relax the uniqueness on title somehow.

But I cannot drop and recreate the PK due to the table being index organized:

SQL> ALTER TABLE books DROP PRIMARY KEY;
ALTER TABLE books DROP PRIMARY KEY
*
ERROR at line 1:
ORA-25188: cannot drop/disable/defer the primary key constraint for
index-organized tables or sorted hash cluster

I cannot manage to extend the existing key in-place either:

SQL> CREATE UNIQUE INDEX pk_title_date ON books(title, release_date);

Index created.

SQL> ALTER TABLE books MODIFY PRIMARY KEY USING INDEX pk_title_date;
ALTER TABLE books MODIFY PRIMARY KEY USING INDEX pk_title_date
*
ERROR at line 1:
ORA-14196: Specified index cannot be used to enforce the constraint.

What are my options ? Is it possible without moving data or recreating the table (the actual table is huge) ?

Best Answer

An index-organized table (IOT) is just that; an index with no "real" table. All data besides the primary key are just "tacked on" to the primary key, so for example if you have an IOT with 6 columns, 2 of which make up the primary key, behind the scenes you just have an index with 6 columns, of which the first 2 columns make it unique.

So, sorry, only way to fix it is to recreate the table; to improve the rebuild time temporarily disable logging. You cannot use an append hint on inserts to an IOT table to force a direct path insert: 

SQL> CREATE TABLE books_new (
    title VARCHAR2(10),
    release_date DATE NOT NULL,
    CONSTRAINT pk_title PRIMARY KEY(title,release_date))
  ORGANIZATION INDEX NOLOGGING;

SQL> INSERT INTO books_new 
     (SELECT title, '01-Jan-1980' FROM books);
SQL> ALTER TABLE books_new LOGGING;
SQL> ALTER TABLE books RENAME TO books_old;
SQL> ALTER TABLE books_new RENAME TO books;
Related Question