Lesson 1

You can significantly improve the performance of SQL queries with indexes. There may even be times when an index can supply all the information you need in a query. An index-organized table might be able to give you even greater performance benefits than an index alone.

In this module, you will learn how to:

1. Describe an index-organized table and explain when to use it
2. Create an index-organized table
3. Rebuild an index-organized table with the MOVE parameter of the ALTER TABLE command
4. Create a secondary index on index-organized table
5. Understand the differences between index-organized and regular tables

Historical Note: Although index-organized tables were introduced in Oracle8, Oracle8i includes significant enhancements to the capabilities of these database objects.
In the next lesson, we will discuss what an index-organized table is.

An index-organized table keeps its data sorted according to the primary key column values for the table. An index-organized table stores its data as if the entire table was stored in an index.
Indexes serve two main purposes:

1. To enforce uniqueness: When a PRIMARY KEY or UNIQUE constraint is created, Oracle creates an index to enforce the uniqueness of the indexed columns.
2. To improve performance: When a query can use an index, the performance of the query may dramatically improve. See Chapter 46 for details on the conditions under which a query may use an index.

An index-organized table allows you to store its entire data in an index. A normal index only stores the indexed columns; an index-organized table stores all its columns in the index. To create an index-organized table, use the organization index clause of the create table command, as shown in the following example:

create table TROUBLE_IOT (
City VARCHAR2(13),
SampleDate DATE,
Noon NUMBER(4,1),
Midnight NUMBER(4,1),
Precipitation NUMBER,
constraint TROUBLE_IOT_PK PRIMARY KEY (City, SampleDate))
organization index;

To create TROUBLE as an index-organized table, you must create a PRIMARY KEY constraint on it. An index-organized table is appropriate if you will always be accessing the TROUBLE data by the City and SampleDate columns (in the where clauses of your queries). To minimize the amount of active management of the index required, you should use an index-organized table only if the table's data is very static. If the table's data changes frequently, you should use a regular table with indexes as appropriate.
In general, an index-organized table is most effective when the primary key constitutes a large part of the table's columns. If the table contains many frequently accessed columns that are not part of the primary key, the index-organized table will need to repeatedly access its overflow area. Despite this drawback, you may choose to use index-organized tables to take advantage of a key feature that is not available with standard tables: the ability to use the move online option of the alter table command. You can use that option to move a table from one tablespace to another while it is being accessed by insert, update, and delete operations. You cannot use the move online option for partitioned index-organized tables.