Lesson 1

So far in this course, you have learned how to use both partitioning and clusters to optimize the physical retrieval of data in your Oracle database from disk. Index-organized tables is a feature that was introduced with Oracle8, and these database structures can improve performance when used with appropriate types of data.
In this module, you will not only learn about index-organized tables, but you learn to:

1. Explain how index-organized tables differ from normal tables
2. Know when to use an index-organized table
3. Create an index-organized table
4. Create secondary indexes on an index-organized table
5. Handle large rows in an index-organized table
6. Delete and modify index-organized tables
7. Reorganize an index-organized table

Like clusters, index-organized tables are not appropriate for all types of data, although in suitable situations, they too canprovide a significant performance increase.
In the next lesson, you will learn how index-organized tables differ from normal tables.

In general, rows within a regular table are unordered. Although the Oracle DBMS offers many different ways to physically organize tables on disk

1. heap tables,
2. index clusters,
3. hash clusters,
4. index-organized tables, and
5. sorted hash clusters

you should never expect the rows to be physically stored in a certain order. Even if a particular order exists today, there is no guarantee that it will be the same tomorrow. This is a fundamental property of relational databases.
See Rule Number 8.
Suppose the EMPLOYEES table contains 50,000 rows (instead of the 14 rows we have), and suppose you want to know which employees have a name starting with the letter 'Q'. Normally, the Oracle DBMS can use only one method to produce the results for this query: by accessing all 50,000 rows (with a full table scan) and checking the name for each of those rows.
This could take quite some time, and perhaps there would be no employees at all with such a name. An index on employee names would be very useful in this situation. When you create an index, the Oracle DBMS creates, and starts to maintain, a separate database object containing a sorted list of column values (or column combination values) with row identifiers referring to the corresponding rows in the table. To further optimize access, indexes are internally organized in a tree structure.
If there were such an index on employee names, the optimizer could decide to abandon the full table scan approach and perform an index search instead. The index offers a very efficient access path to all names, returning all row identifiers of employees with a name starting with a Q. This probably would result in a huge performance improvement, because there are only a few database blocks to be visited to produce the query result.