Index Organized  «Prev  Next»

Lesson 6 Deleting and altering an index-organized table
Objective Delete an index-organized table.

Deleting and altering an index-organized Table

Modifying an existing Index-Organized Table (IOT) in Oracle 19c requires careful planning and execution to ensure that the changes do not adversely affect the table's performance or data integrity. As an Oracle DBA, follow these steps to modify an existing IOT:
  1. Evaluate the Modification Requirements:
    • Determine the specific changes needed for the IOT, such as adding or dropping columns, changing column data types, modifying the primary key, or adjusting physical and storage attributes. Understanding the impact of these modifications on application performance and data retrieval is crucial.
  2. Backup the Table:
    • Before making any changes, ensure that you have a recent backup of the table and its associated data. This precaution allows for data recovery in case of an error during the modification process.
  3. Determine the Modification Strategy:
    • Depending on the nature of the changes, decide whether you can modify the IOT directly using the `ALTER TABLE` statement, or if you need to create a new table with the desired changes and then migrate the data.
  4. Use `ALTER TABLE` for Simple Modifications:
    • For straightforward changes such as adding a new column, you can use the `ALTER TABLE` statement directly. For example, to add a new column: 
      ALTER TABLE table_name 
ADD (new_column_name column_type);
    • To modify existing column definitions or to drop a column, use the corresponding `ALTER TABLE` options.
  5. Handling Complex Modifications:
    • If the changes involve modifying the primary key or significantly altering the table structure, it may be necessary to create a new IOT with the desired structure and then migrate the data. This approach involves several steps:
      1. Create a new IOT with the required modifications.
      2. Use `INSERT INTO new_table SELECT * FROM old_table;` to migrate the data, ensuring that the data is transformed or cast as needed to fit the new structure.
      3. Verify the data integrity and completeness in the new table.
      4. Rename or drop the old table.
      5. Rename the new table to the original table name, if required.
  6. Rebuild the Indexes (if necessary):
    • After modifying the table, you may need to rebuild the primary key index or any secondary indexes to optimize performance. Use the `ALTER INDEX index_name REBUILD;` statement to rebuild an index.
  7. Update Statistics:
    • Once the modifications are complete, update the statistics for the modified table to ensure that the Oracle optimizer has current information for query optimization. Use the `DBMS_STATS.GATHER_TABLE_STATS` procedure to gather statistics.
  8. Test the Modifications:
    • Perform thorough testing to ensure that the modifications have been applied as intended and that the table continues to perform well under typical workloads. This testing should include both data integrity checks and performance benchmarking.
  9. Document the Changes:
    • Document the modifications made to the IOT, including the rationale for the changes, the steps taken to implement the changes, and any impacts observed on performance or functionality. This documentation is valuable for future reference and for other team members who may work with the table.

By following these steps, you can effectively modify an existing Index-Organized Table in Oracle 19c, ensuring that the table continues to meet the evolving needs of your applications while maintaining optimal performance and data integrity.


No one database administrator can foresee every contingency in the future, so there may come times when you either have to delete an index-organized table or have to alter the characteristics of the table. For instance, you may decide that you want to add an overflow segment to an existing index-organized table.
  1. Deleting an index-organized table:
    You delete an index-organized table in the same way that you delete a normal table: 
    DROP TABLE tablename

    You use the standard command, where tablename is the name of the index-organized table you wish to delete. This command will delete the table structure and all the data within the table.
  2. Altering an index-organized table:
    You have a few options for modifying an existing index-organized table. The syntax for changing an existing index-organized table is 
    ALTER TABLE tablename option;

    where tablename is the name of an index-organized table and option is used to complete one of the following:
    • Modify the physical characteristics of the table or its overflow segment, such as PCTFREE, PCTUSED, MAXTRANS, or INITRANS
    • Modify the storage characteristics of the table or its overflow segment, such as INITIAL, NEXT, MINEXTENTS, or MAXEXTENTS
    • Add an overflow segment with the syntax ADD OVERFLOW

    For the first two options, you can specify the action for the overflow segment by including the keyword OVERFLOW between the tablename and the option phrase. Unlike with a standard table, you cannot add columns to an existing index-organized table.
    In the next lesson, you will learn about reorganizing index-organized tables.

SEMrush Software