Indexes

As we already know, indexes are invisible for most database users, so in most cases they would not need to change indexes. Also, you can always drop and re-create an index rather than modify it. Out of our three database vendors only Oracle provides the ALTER INDEX statement to change the physical definition of an index. The DROP INDEX statement is used to remove indexes; it's available in all three databases.

Note 

As discussed in Chapter 4, indexes are not part of SQL99.

ALTER INDEX statement in Oracle 9i

As usual, we are not going to concentrate on details of the ALTER INDEX statement and will rather refer you to Oracle documentation for more information, and we'll illustrate only a couple of clauses that might be interesting for us in the course of this book:

ALTER INDEX <index_name> {[RENAME TO <new_name>] |  [REBUILD TABLESPACE <tablespace_name>] };

Renaming indexes

The RENAME clause can be useful if your indexes were created with system-generated names (that is, if you did not name them specifically on object creation). For instance, suppose you created table SALESMAN with the following statement:

CREATE TABLE salesman (    salesman_id_n         NUMBER          PRIMARY KEY,    salesman_code_s       VARCHAR2(2)     UNIQUE,    salesman_name_s       VARCHAR2(50)    NOT NULL,    salesman_status_s     CHAR(1)         DEFAULT 'Y',    CONSTRAINT chk_salesstatus CHECK              (salesman_status_s in ('N', 'Y')) );

Oracle will automatically create indexes on both the SALESMAN_ID_N and SALESMAN_CODE_S columns (remember, indexes are always created on primary key or unique columns), but the names will be rather nondescriptive; something like SYS_C003521 and SYS_C003522. If your database has some kind of naming conventions similar to those described in Appendix B (which is not a bad idea), you might want to change the names of the indexes to something more descriptive later:

ALTER INDEX SYS_C003521 RENAME TO IDX_SALESMAN_ID; 

ALTER INDEX SYS_C003522 RENAME TO IDX_SALESMAN_CODE;

Rebuilding indexes into a different tablespace

Another frequent mistake while creating Oracle indexes is to create them in a wrong tablespace. This happens even more often than with tables because Oracle does not provide a default index tablespace option. Specifically, regardless if a user has an assigned default data tablespace (DATA01 for example), all physical objects (including indexes) are created in this tablespace by default if otherwise was not specified. That is not always the desirable behavior, especially in a production environment where data and index tablespaces are usually located on separate physical devices (hard disks); creating indexes in wrong tablespaces can significantly degrade performance.

The ALTER INDEX command can be used to fix the problem:

ALTER INDEX IDX_SALESMAN_ID REBUILD TABLESPACE INDEX01;     ALTER INDEX IDX_SALESMAN_CODE REBUILD TABLESPACE INDEX01;

Note 

This example assumes the existence of the tablespace INDEX01.

DROP INDEX statement

Similar to the DROP TABLE statement, DROP INDEX releases the allocated space and removes the index definition from the database information schema. You cannot drop indexes created to implement PRIMARY KEY or UNIQUE constraints using DROP TABLE; ALTER TABLE ... DROP CONSTRAINT statement would have to be used instead.

Cross-References 

The database information schema is discussed in Chapter 13.

Oracle 9i

The syntax is

DROP INDEX [<qualifier>.]<index_name>;

The first statement returns an error because PK_ORDHDRPRIM is the primary key on table ORDER_HEADER, but the second one works just fine:

SQL> DROP INDEX pk_ordhdrprim; DROP INDEX pk_ordhdrprim            * ERROR at line 1: ORA-02429: cannot drop index used for enforcement of unique/primary key 

SQL> DROP INDEX idx_phone_cust;     Index dropped.

When an index is dropped, all objects dependent on the underlying table are invalidated.

DB2 UDB 8.1

Use the same syntax as in Oracle:

DROP INDEX [<qualifier>.]<index_name>

Assume you want to drop index IDX_PHONE_CUST on PHONE_CUSTID_FN column of the PHONE table:

DROP INDEX idx_phone_cust

DB2 invalidates packages that have dependency on the dropped index.

MS SQL Server

You have to specify both the table name in which the indexed column is located and the index name:

DROP INDEX <table_name>.<index_name> [,...]

For example

DROP INDEX phone.idx_phone_cust

Tip 

You can drop multiple indexes within one DROP INDEX statement. The names must be comma-separated.