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

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.

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.

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:
  1. Modify the physical characteristics of the table or its overflow segment, such as PCTFREE, PCTUSED, MAXTRANS, or INITRANS
  2. Modify the storage characteristics of the table or its overflow segment, such as INITIAL, NEXT, MINEXTENTS, or MAXEXTENTS
  3. 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.