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:
- 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.