IOT Index
IOT stands for Index Organised Table in oracle database.The idea behind an IOT is simple. You hold all the data for the table in the ordered structure of an index.
Why do we use IOT?
- An IOT has the structure of an index and stores all the columns of the row, accesses via primary key conditions are faster as they don’t need to access the table to get additional column values.
- IOT has the structure of an index and is thus sorted in the order of the primary key, accesses of a range of primary key values are also faster.
- The index and the table are in the same segment, less storage space is needed.
- In addition, as rows are stored in the primary key order, you can further reduce space with key compression.
- All indexes on an IOT use logical rowids, they will not become unusable if the table is reorganized.
Syntax to Create, Rename, Drop B-tree Index
CREATE TABLE<table_name>( <column_name> <data type and precision>, <column_name> <date type and precision>, CONSTRAINT<constraint_name> PRIMARY KEY(<primary key constraint columns>)) ORGANIZATION INDEX;
Example of IOT Index
CREATE TABLE Employee_details(
Work_Date DATE,
Employee_id NUMBER,
CONSTRAINT pk_Emp_Details
PRIMARY KEY(Work_Date, Employee_id))
ORGANIZATION INDEX;
Rename IOT Index
ALTER INDEX index_name RENAME TO new_name;
ALTER INDEX "BIN$jzohHP0LRqusV3X3jtaDRQ==$0" RENAME To new_emp_name;
Drop IOT Index
DROP TABLE table_name; DROP Table Employee_details;
Why do we not use IOT?
- The IOT has to be indexed on the primary key. There is no option to create an IOT based on other indexes. As such you have to either be accessing the table via the primary key to get the benefit – or you have to be a little cunning.
- The index is going to be larger than it was and very often larger than the original table. This can slow down range scans or full scans of the index and a “full table scan” will now be a full index scan on this large object, so that can also negatively impact performance. However, if a range scan would then have resulted in access to the table to get extra columns, the IOT gives a similar benefit in reducing IO to that for single row lookup.
- It does consider ROWID. IOT stores the data in B-tree index and sorts the data on primary key whenever we insert, update the record.