IOT

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;

CREATE TABLE Employee_details(
Work_Date DATE,
Employee_id NUMBER,
CONSTRAINT pk_Emp_Details 
PRIMARY KEY(Work_Date, Employee_id))
ORGANIZATION INDEX;

ALTER INDEX index_name RENAME TO new_name;
ALTER INDEX "BIN$jzohHP0LRqusV3X3jtaDRQ==$0" RENAME To new_emp_name;

DROP TABLE table_name;
DROP Table Employee_details;