How do you move an index organized table?

The following index needs to be moved from tablespace DATA1 to tablespace DATA2:

select unique segment_name,segment_type,tablespace_name 
from dba_segments 
where owner='SCOTT'
and tablespace_name = 'DATA1'
;

Result:

SEGMENT_NAME	SEGMENT_TYPE	TABLESPACE_NAME
COUNTRY_ID_PK	INDEX	DATA1

Turns out, the index belongs to an IOT:

select i.owner,i.table_name,i.tablespace_name,i.status, t.iot_type
from dba_indexes i join dba_tables t
on (i.table_name = t.table_name)
and t.table_name ='COUNTRIES'
and t.owner=i.owner
where i.index_name='COUNTRY_ID_PK'
and i.owner='SCOTT';

Result in:

OWNER	TABLE_NAME	TABLESPACE_NAME	STATUS	IOT_TYPE
SCOTT	COUNTRIES	DATA1	VALID	IOT

If you try to move the index COUNTR_ID_PK with the usual "alter index ... rebuild" clause, it will fail with

ORA-28650: Primary index on an IOT cannot be rebuilt

Instead, move the table. Since it's an IOT, the index will be moved automatically with it:

alter table SCOTT.COUNTRIES move tablespace DATA2 ONLINE;

Verify that the IOT was moved by executing the same query as previously:

OWNER	TABLE_NAME	TABLESPACE_NAME	STATUS	IOT_TYPE
SCOTT	COUNTRIES	DATA2	VALID	IOT

What are Index-organized tables and how are they created?

An example of creating an IOT table from my own experience:

CREATE TABLE DATE_TO_SEQNUM (
    DATO DATE, 
    SEQNUM NUMBER(19) NOT NULL,
CONSTRAINT PK_DTS_IOT PRIMARY KEY (DATO,SEQNUM)
)
ORGANIZATION INDEX
TABLESPACE USERS;

If you expect a lot of repeating entries in the IOT, you can use index compression, like this:

CREATE TABLE DATE_TO_SEQNUM (
    DATO DATE, 
    SEQNUM NUMBER(19) NOT NULL,
CONSTRAINT PK_DTS_IOT PRIMARY KEY (DATO,SEQNUM)
)
ORGANIZATION INDEX
TABLESPACE USERS 
COMPRESS;

In my case, the IOT was really this simple. There are some other important directives when creating an IOT, too, which may be applicable in other cases. They are the use of OVERFLOW partition, the INCLUDING keyword, and the PCTTHRESHOLD-clause. I will come back to them when I have had a chance to put them into practice. In the mean time, read about them here.

Here are some statements from the Oracle documentation which defines what an IOT is, and the potential benefits the can provide:

* Index-organized tables are tables stored in an index structure.

* In an index-organized table, rows are stored in an index defined on the primary key for the table. Each index entry in the B-tree also stores the non-key column values. Thus, the index is the data, and the data is the index.

* Index-organized tables provide faster access to table rows by primary key or a valid prefix of the key. The presence of non-key columns of a row in the leaf block avoids an additional data block I/O.

* Index-organized tables are useful when related pieces of data must be stored together or data must be physically stored in a specific order.


Sources: Oracle Database 12.2 documentation