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:
Describe an index-organized table and explain when to use it
Create an index-organized table
- Rebuild an index-organized table with the
MOVE parameter of the
ALTER TABLE command
- Create a secondary index on index-organized table
Understand the differences between index-organized and regular tables
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.
To create TROUBLE as an index-organized table, you must create a PRIMARY KEY constraint
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
create table TROUBLE_IOT (
constraint TROUBLE_IOT_PK PRIMARY KEY (City, SampleDate))
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.