Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: # of Indexes

RE: # of Indexes

From: Rajesh Dayal <>
Date: Tue, 27 Jun 2000 16:20:28 +0400
Message-Id: <>

Hi Anita,

   Thanks for the additional info. I tested the things in a test env. Actually system generated indexes don't appear in Indexfile and only user created indexes appear. So the simplest solution to the problem is to create a batch script which looks like as follow and run that.

alter indexes foo rebuild tablespace bar ;

Thanks to all who replied..  


-----Original Message-----
From: A. Bardeen [] Sent: Tuesday, June 27, 2000 11:48 AM
To:; Rajesh Dayal
Subject: Re: # of Indexes


You don't mention which version of Oracle you're running, but changes were made to the way PK/unique constraint indexes are created in O8. Although the scenario for this would actually be slightly different (creating the constraint after the existing index), I suspect you're encountering it anyway.

In O8 if an existing index can be used to satisfy a PK or unique constraint, then an additional index for the constraint will NOT be created. This only affects tables with composite indexes.

For example if you have an existing index on columns A,B, then if you create a PK or unique constraint on A or B,A Oracle will use the existing index on A,B to enforce the constraint.

This is not a bug and is documented in the SQL Administrator's manual:

Chapter 17: General Management of Schema Objects

Section: Managing Constraints That Have Associated Indexes

When you create a UNIQUE or PRIMARY key, Oracle checks to see if an existing index can be used to enforce uniqueness for the constraint. If there is no such index, Oracle creates one.

When constraints associated with unique indexes are dropped or disabled, the index is dropped. Oracle can use non-unique indexes to enforce UNIQUE and PRIMARY key constraints. If you allow Oracle to create a UNIQUE index automatically, and constraints associated with UNIQUE index are dropped or disabled, then the index is dropped.  

The workaround is to create the constraints, so their accompanying indexes are created, BEFORE the additional indexes.


Received on Tue Jun 27 2000 - 07:20:28 CDT

Original text of this message