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.
Prior to the release of Oracle, the only way to rebuild an index-organized table was to follow a four-step process:
- Export the data from the index-organized table.
- Drop the index-organized table from the database.
- Re-create the index-organized table.
- 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:
You can also see, by the presence of the keyword
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
For instance, you could move an index-organized table called
to a new tablespace called
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.