Tables are what, RDBMS is all about. Tables provide a higher level abstraction layer for the logical storage of data, so much so as reference to the tablename is all required for specifying the source (read location) of data in the non-procedural SQL language. We all commonly know about the structure of the table simply to consist of columns and rows. But this basic table may not serve the purpose of different access patterns and data handling needs from optimization point of view. Hence sophisticated commercial RDBMS like Oracle may have some more types of tables than the "Regular" or "Normal" table; which by the way is called as "Heap Organized Table". We are going to look into these many different types of tables used in Oracle as our reference database system. There are about the same types of tables also used in database systems from other vendors, but they may be named differently or may use slightly different terminology. The visitors are free to post the links or comments to enrich the article in that respect.
Here is the list of different types of tables used in Oracle -
- Heap Organized Tables
- Index Organized Tables
- Clustered Tables
- Temporary Tables
- External Tables
- Object Tables
- Nested Tables
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 -
- 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).