Defining a Database Using SQL › Dropping a Default Index

Dropping a Default Index

You add or drop a default index by issuing an ALTER TABLE statement on the table whose default index you want to drop.

Things You Can Specify

• Use the ADD DEFAULT INDEX parameter to create a default index for the named table.

  Note: The table must not already have a default index associated with it.

• Use the CASCADE parameter to drop all indexes in which the named column is an indexed column following entities.

  Note: If CASCADE is not specified, the column must not participate in a referential constraint or index, or be named in a view.

Considerations

It may not always be appropriate to drop a default index.

Note: See the Database Design Guide document for complete information about retaining or dropping default indexes, and see the SQL Reference Guide for complete information on ALTER TABLE.

Example

In the following example, the default index is dropped from the EMPLOYEE table .

alter table prod.employee
      drop default index;