Reorganizing an Index-Organized Table in Oracle 13c

Question: What is the process for reorganizing an index-organized table? Index-organized tables (IOTs) in Oracle are tables stored in a B-tree index structure. They are primarily used when fast primary key access is required, and the table has a relatively stable set of columns. Over time, as with regular tables and indexes, IOTs can become fragmented due to DML operations. Reorganizing them can help reclaim space and improve performance.

1. Overview: Reorganizing an IOT involves rebuilding the underlying index structure. The process can be achieved using the `ALTER TABLE` statement with the `MOVE` option. Additionally, any associated overflow segments or LOB columns should be considered during the reorganization.
2. Steps to Reorganize an Index-Organized Table:
   1. Prepare for Reorganization:
      • Ensure you have a recent backup of the database.
      • Determine the size of the IOT and ensure sufficient space in the target tablespace.
      • Schedule the reorganization during a maintenance window or a period of low activity, as the process can be resource-intensive.
   2. Reorganize the IOT:

      ALTER TABLE your_iot_name 
      MOVE TABLESPACE target_tablespace_name;
      

      
      Replace `your_iot_name` with the name of your IOT and `target_tablespace_name` with the name of the target tablespace.
   3. Reorganize Overflow Segments (if applicable): If your IOT has an overflow segment, it should be moved to a new location or tablespace to ensure optimal performance.
      

      ALTER TABLE your_iot_name MOVE OVERFLOW TABLESPACE 
      target_overflow_tablespace_name;
      

      
      Replace `target_overflow_tablespace_name` with the name of the desired tablespace for the overflow segment.
   4. Reorganize LOB Columns (if applicable): If your IOT contains LOB columns, they should also be reorganized.
      

      ALTER TABLE your_iot_name MOVE LOB(lob_column_name) 
      STORE AS (TABLESPACE target_lob_tablespace_name);
      

      
      Replace `lob_column_name` with the name of the LOB column and `target_lob_tablespace_name` with the name of the desired tablespace for the LOB data.
   5. Rebuild Secondary Indexes: Secondary indexes on the IOT will become unusable after the reorganization. They need to be rebuilt.
      

      ALTER INDEX your_secondary_index_name 
      REBUILD TABLESPACE target_index_tablespace_name;
      

      
      Replace `your_secondary_index_name` with the name of your secondary index and `target_index_tablespace_name` with the name of the desired tablespace for the index.
   6. Gather Statistics: After reorganization, gather fresh statistics to ensure the optimizer has accurate information.
      

      EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => 'your_schema_name', tabname =>
      'your_iot_name', cascade => TRUE);
      

      
      Replace `your_schema_name` with the name of your schema and `your_iot_name` with the name of your IOT.

Reorganizing an Index-Organized Table in Oracle 13c is a systematic process that, when executed correctly, can lead to significant performance improvements and space savings. Regular monitoring and maintenance of IOTs are essential to ensure optimal database performance. Always ensure you have a backup before performing any reorganization tasks and test the process in a development or staging environment before applying it to a production database.

Similar courses on this website have demonstrated that there are instances when you should reorganize an index structure. If you are consistently deleting values from one side of the index while adding rows to the other side, the index may become unbalanced, with the leaf nodes on one side of the index containing an increasing amount of empty and wasted space. To restore balance to the index structure and remove these potential performance impediments, you must reorganize the index. Because an index-organized table is stored in the exact same index structure as the index for a standard table, the same requirement for reorganization applies to this type of table.
The process of reorganizing an index-organized table requires four steps, as shown in the following series of images.

Oracle (IOTs) Index-organized tables are a unique style of table structure that is stored in a B-tree index structure. Besides storing the primary key values of an Oracle indexed-organized tables row, each index entry in the B-tree also stores the non-key column values. Oracle Indexed-organized tables provide faster access to table rows by the primary key or any key that is a valid prefix of the primary key. Because the non-key columns of a row are present in the B-tree leaf block itself, there is no additional block access for index blocks. Applications manipulate, select, and update the Oracle index-organized tables just like ordinary tables and that is by means of SQL statements.

In the previous database release, you could partition an Oracle index-organized table by range or by a hash on column values. The partitioning columns had to form a subset of the primary key columns. Just like ordinary tables, local partitioned (prefixed and non-prefixed) indexes as well as global partitioned (prefixed) indexes were supported for partitioned Oracle index-organized tables. With the release of Oracle 10g, you can partition by the list method. IOT - Oracle Index-organized tables: These structures remove the primary key from the table and keep them solely in the index, saving disk space. Primarily used with high-updates tables, the Index Organized tables structure reduces table fragmentation. Accessing data by means of the primary key is quicker because the key and the data reside in the same structure, and there is no need to read an index then read the tables data in a separate structure. Lack of duplication of the key columns in an index and table mean the total storage requirements are reduced. Index-only tables have been around since Oracle8. If neither the Oracle HASH or Oracle INDEX ORGANIZED options are used with the CREATE TABLE command, then a table is created as a standard hash table. If the Oracle INDEX ORGANIZED option is specified, the table is created as a B-tree-organized table identical to a standard Oracle index created on similar columns. Furthermore, index-organized tables do not have rowids.
Oracle index-organized tables have the option of allowing overflow storage of values that exceed optimal index row size, as well as allowing compression to be used to reduce storage requirements. Overflow parameters can include columns and the percent threshold value to begin overflow. An index-organized table must have a primary key and are best suited for use with queries based on primary key values. Oracle index-organized tables can be partitioned in Oracle8i and in Oracle9i as long as they do not contain LOB or nested table types.

When you reload the data into the new index-organized table structure, you will automatically fill up all the leaf nodes, just as you would if you took the same set of actions against a normal index. With an index, you can simply use the REBUILD clause as part of the ALTER INDEX statement, but in Oracle8 and earlier, this type of option was not available to you. The next lesson is the module conclusion.