Lesson 2	Index-organized tables
Objective	Explain how index-organized tables differ from normal tables.

Index-organized Tables

In the beginning, the file was a simple way to store data on disk. As data was added to a file, the file grew larger. Retrieving data from a file was simple since the software read through the file until it found the right record.
With database management systems, the concept of an index was introduced. With an index, the database software could quickly locate a particular value, usually by going through a B*-tree structure. An index value would point to a row containing data in the database.
This type of retrieval was much faster and more consistent than retrieving data directly from a data file, but it always needed at least two logical input/output (I/O) operations–one to retrieve an index value and one to retrieve the data.

How IOT stores data

With an index-organized table, the data in the table is stored within the leaf nodes, or bottom nodes, of the B*-tree index. This structure allows the Oracle database to retrieve values directly from the index structure, without having to go to a separate storage area for the data in the database row.
The following SlideShow compares the way that data is retrieved from a standard table and with how it is retrieved from an index-organized table:

In a standard table and index, the index includes index nodes and a 'bottom' level of leaf nodes
To access data, you must go through the index to the leaf node and then to the row in the data table by using the ROWID.
In an index organized-table, the data is stored in the leaf nodes of the index itself
To access data in an index-organized table, you go through the index directly to the data in the leaf nodes

Standard Index Organized Tables
In the next lesson, you will learn the advantages and disadvantages of index-organized tables.

Overview of Indexes

An index is an optional structure, associated with a table or table cluster, that can sometimes speed data access. By creating an index on one or more columns of a table, you gain the ability in some cases to retrieve a small set of randomly distributed rows from the table. Indexes are one of many means of reducing disk I/O. If a heap-organized table has no indexes, then the database must perform a full table scan to find a value. For example, without an index, a query of location 2700 in the hr.departments table requires the database to search every row in every table block for this value. This approach does not scale well as data volumes increase. For an analogy, suppose an HR manager has a shelf of cardboard boxes. Folders containing employee information are inserted randomly in the boxes. The folder for employee Whalen (ID 200) is 10 folders up from the bottom of box 1, whereas the folder for King (ID 100) is at the bottom of box 3. To locate a folder, the manager looks at every folder in box 1 from bottom to top, and then moves from box to box until the folder is found. To speed access, the manager could create an index that sequentially lists every employee ID with its folder location:

ID 100: Box 3, position 1 (bottom)
ID 101: Box 7, position 8
ID 200: Box 1, position 10
.
.
.

Similarly, the manager could create separate indexes for employee last names, department IDs, and so on. In general, consider creating an index on a column in any of the following situations:

The indexed columns are queried frequently and return a small percentage of the total number of rows in the table.
A referential integrity constraint exists on the indexed column or columns. The index is a means to avoid a full table lock that would otherwise be required if you
update the parent table primary key, merge into the parent table, or delete from the parent table.
A unique key constraint will be placed on the table and you want to manually specify the index and all index options.

Indexing using Oracle Database