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:

  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
ALTER TABLE table_name MOVE (ONLINE);

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:
ALTER TABLE IOTAB MOVE ONLINE TABLESPACE NEWTABSPACE

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