Databases Reference
In-Depth Information
Rebuilding an Index-Organized Table
An index-organized table is a B-tree index. It can become fragmented over time and can incur the same
issues as a normal B-tree index: expanded index depth over time, an unbalanced tree, and sparse blocks,
to name a few. Therefore, you can rebuild an index-organized table as you would a normal B-tree index.
The obvious difference is that because it is regarded as a table, you rebuild an IOT with the
ALTER TABLE
command. See the following example:
SQL> ALTER TABLE employees_iot MOVE;
Table altered.
If you want to move the IOT to a different tablespace, simply specify the tablespace within the
ALTER
TABLE
clause, as shown in the following examples:
SQL> ALTER TABLE employees_iot MOVE TABLESPACE emp_s;
Table altered.
When an IOT is rebuilt, the overflow segment is not rebuilt by default. Since similar fragmentation
issues can occur with the overflow segment, it's a good idea to always rebuild the overflow segment
whenever you rebuild the IOT itself. See the following examples:
SQL> ALTER TABLE employees_iot MOVE overflow;
SQL> ALTER TABLE employees_iot MOVE tablespace emp_s
2 overflow tablespace overflow_s;
Table altered.
You can also rebuild an IOT with the
ONLINE
clause, meaning the existing structure can be accessed
during the rebuild operation.
22:39:15 SQL> alter table employees_iot move tablespace users online;
Table altered.
An index organized table can be partitioned just as any other heap-organized table can be
partitioned. If you are rebuilding a partitioned IOT, you can't rebuild it in one step—that is, the entire
table—or you will receive the following error:
SQL> ALTER TABLE employees_iot MOVE;
ALTER TABLE employees_iot MOVE
*
ERROR at line 1:
ORA-28660: Partitioned Index-Organized table may not be MOVEd as a whole
If you wish to rebuild an entire partitioned IOT, you must do it one partition at a time. You will need
to get the partition names from the index itself using the
USER_IND_PARTITIONS
view (or, of course,
