Index-Organized Tables -
Tables stored in an index structure are called as “Index-Organized Tables” (IOT). Unlike heap-organized tables, where data (rows) gets stored randomly at any available free space, the index-organized tables store the data sorted on the primary key. This organization does not make any change in the SQL though.
Organization of a table in the form of index however requires lot of work to manage and maintain (with a volume of transaction and resulting frequent re-organization) since B-Tree organization of index is rather a complex structure (in the form of an inverted tree). This one aspect therefor justifies why each table (anyway in RDBMS would have primary key) is not created as IOT. Heap tables on the other hand are easy to manage given a transaction volume even with associated indexes that might take more space than IOT (which may not have a separate index) but much less or close to no requirement for re-organization. In short, response to a transaction is much slower in IOT along with more resources consumption in reorganization as compared to heap table. So that is the reason for not creating every table as IOT.
The implementations that beg for an IOT are where the lookup is based mostly on primary key for single row at time (or sequential fetch for multiple rows) and the tables do not have much of transactions (particularly deletes and inserts) but only selects. For example – in a train reservation system there may be a lookup table with train number and train name and some other data like starting point and termination. The data hardly changes and the lookup is always based on train number which is unique (PK). Similarly zip code to state may also be a good candidate for IOT.
Creating Index-Organized Tables -
You may create an IOT using the following command structure -
Command Explanation -
- organization index tells database system (Oracle) to create this table not as normal heap organized but an Index Organized.
- pctthreshold specifies what % part of the row in IOT to be stored in “index block”. This block contains the part of the row to go with primary key column (which includes those columns which are accessed frequently in the query)
- overflow is the remaining part of the row which is made of columns which are not mentioned in the query frequently and to be treated as overflow and stored separately in specified separate tablespace (in overflow blocks).