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