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

Example of IOT Index

Rename IOT Index

Drop IOT Index

 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.

Leave a Reply

Your email address will not be published. Required fields are marked *