What is an IOT?
An IOT implies having your entire table indexed, within the same B-Tree structure. Should be used for larger scale tables or dimensions, highly used within the database.
Accessing the data is done via the primary key, much faster as the key and the data reside in the same structure. Also, lack of duplicaiton on key columns imply the total storage requirements are reduced.
To create an index organized table you must:
- Specify the primary key using a column or table constraint.
- Use the
In addition you can use
INCLUDING to define which non-key columns are stored with the key columns in the head piece, should overflow be necessary.
CREATE TABLE test_iot (id NUMBER(10) , description VARCHAR2(50) NOT NULL , comments varchar2(4000) , CONSTRAINT pk_test_tb PRIMARY KEY (id) ) ORGANIZATION INDEX TABLESPACE iot_tablespace INCLUDING description OVERFLOW TABLESPACE overflow_tablespace;
What to keep in mind when using IOT
Having a B-Tree index behaviour, the IOTs can become fragmented, or there might be other reasons you will need to rebuild the index. With no overflow it can be rebuild offline or online, while when it has overflow it can only be rebuit offline.
ALTER TABLE test_iot MOVE INITRANS 10; --online rebuild ALTER TABLE test_iot MOVE ONLINE INITRANS 10; --offline with OVERFLOW ALTER TABLE test_iot MOVE TABLESPACE iot_tablespace OVERFLOW TABLESPACE overflow_tablespace;
One thought on “Oracle Index Organized Table(IOT)”