Oracle DBA FAQ - Managing Oracle Table Indexes

Home >> FAQs/Tutorials >> Oracle DBA FAQ

By: FYIcenter.com

Part: 1 2 3 4

(Continued from previous part...)

How To Rename an Index?

Let's say you have an existing index, and you don't like its name anymore for some reason, you can rename it with the ALTER INDEX ... RENAME TO statement. Here is an example script on how to rename an index:

CREATE TABLE student (id NUMBER(5) PRIMARY KEY,
  first_name VARCHAR(80) NOT NULL,
  last_name VARCHAR(80) NOT NULL,
  birth_date DATE NOT NULL,
  social_number VARCHAR(80) UNIQUE NOT NULL);
Table created.
  
SELECT index_name, table_name, uniqueness 
  FROM USER_INDEXES WHERE table_name = 'STUDENT';
INDEX_NAME              TABLE_NAME            UNIQUENES
----------------------- --------------------- ---------
SYS_C004153             STUDENT               UNIQUE
SYS_C004154             STUDENT               UNIQUE

ALTER INDEX SYS_C004153 RENAME TO student_pk;
Statement processed.

SELECT index_name, table_name, uniqueness 
  FROM USER_INDEXES WHERE table_name = 'STUDENT';
INDEX_NAME              TABLE_NAME            UNIQUENES
----------------------- --------------------- ---------
STUDENT_PK              STUDENT               UNIQUE
SYS_C004154             STUDENT               UNIQUE

How To Drop an Index?

If you don't need an existing index any more, you should delete it with the DROP INDEX statement. Here is an example SQL script:

CREATE TABLE student (id NUMBER(5) PRIMARY KEY,
  first_name VARCHAR(80) NOT NULL,
  last_name VARCHAR(80) NOT NULL,
  birth_date DATE NOT NULL,
  social_number VARCHAR(80) UNIQUE NOT NULL);
Table created.

CREATE INDEX student_birth_date ON student(birth_date);
Index created.

SELECT index_name, table_name, uniqueness 
  FROM USER_INDEXES WHERE table_name = 'STUDENT';
INDEX_NAME              TABLE_NAME            UNIQUENES
----------------------- --------------------- ---------
SYS_C004129             STUDENT               UNIQUE
SYS_C004130             STUDENT               UNIQUE
STUDENT_BIRTH_DATE      STUDENT               NONUNIQUE 

DROP INDEX STUDENT_BIRTH_DATE;
Index dropped.

Can You Drop an Index Associated with a Unique or Primary Key Constraint?

You can not delete the index associated with a unique or primary key constraint. If you try, you will get an error like this: ORA-02429: cannot drop index used for enforcement of unique/primary key.

What Happens to Indexes If You Drop a Table?

If you drop a table, what happens to its indexes? The answer is that if a table is dropped, all its indexes will be dropped too. Try the following script to see yourself:

CREATE TABLE student (id NUMBER(5) PRIMARY KEY,
  first_name VARCHAR(80) NOT NULL,
  last_name VARCHAR(80) NOT NULL,
  birth_date DATE NOT NULL,
  social_number VARCHAR(80) UNIQUE NOT NULL);
Table created.

CREATE INDEX student_birth_date ON student(birth_date);
Index created.

SELECT index_name, table_name, uniqueness 
  FROM USER_INDEXES WHERE table_name = 'STUDENT';
INDEX_NAME              TABLE_NAME            UNIQUENES
----------------------- --------------------- ---------
SYS_C004141             STUDENT               UNIQUE
SYS_C004142             STUDENT               UNIQUE
STUDENT_BIRTH_DATE      STUDENT               NONUNIQUE 

DROP TABLE STUDENT;
Table dropped.

SELECT index_name, table_name, uniqueness 
  FROM USER_INDEXES WHERE table_name = 'STUDENT';
no data found

No records in USER_INDEXES view found for your name STUDENT, after you dropped STUDENT table.

(Continued on next part...)

Part: 1 2 3 4

More...