Performance Tuning  «Prev  Next»

Lesson 4Rebuilding an index-organized table
Objective Rebuild an index-organized table with the MOVE parameter of the ALTER TABLE command.

Rebuilding an Index-organized Table

There are reasons why you might occasionally want to rebuild an index: The index may have become imbalanced or sparsely populated due to the way that entries have been added and deleted. Because an index-organized table has the same structure as a standard B*-tree index, those same reasons might cause you to want to rebuild an index-organized table.

Rebuilding an index-organized table

Prior to the release of Oracle, the only way to rebuild an index-organized table was to follow a four-step process:
  1. Export the data from the index-organized table.
  2. Drop the index-organized table from the database.
  3. Re-create the index-organized table.
  4. Import the data into the new version of the index-organized table.
With Oracle, you can rebuild an index-organized table with a simple command, as shown in the following Diagram:

Index Organized Tables
  1. Required keywords
  2. The name of the index-organized table
  3. Optional keyword to denote whether you want the index-organized table to remain online during the rebuild

Creating Secondary Index on Index Organized Table
You can also see, by the presence of the keyword ONLINE in the ToolTip, that you can rebuild an index-organized table while the table remains online, just as you can rebuild an index with the table online. You can also modify some of the attributes of the index-organized table, such as its location, as part of the MOVE operation.
For instance, you could move an index-organized table called IOTAB to a new tablespace called NEWTABSPACE with the following SQL command:

In the next lesson, you will learn how to add an index to an index-organized table.

Index Organized Table Exercise

Click the Exercise link below to practice creating an index-organized table.
Index Organized Table - Exercise