Re: # of Indexes

From: A. Bardeen <abardeen1_at_yahoo.com>
Date: Tue, 27 Jun 2000 00:48:28 -0700 (PDT)
Message-Id: <10541.110504@fatcity.com>

Rajesh,

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.

HTH,

• Anita
  • Rajesh Dayal <Rajesh_at_ohitelecom.com> wrote:
    > Hi All,
    > I have to move all indexes of a schema from one
    > Tablespace to another
    > Tablespace (currently Tables as well as Indexes are
    > in the same TS ;-((.
    >
    >
    > But when I query DBA_OBJECTS/DBA_INDEXES for that
    > schema the no of
    > indexes come to be 179. When I export the objects of
    > that schema and
    > create the indexfile (by importing ) then no of
    > Indexes is coming to be
    > 89 only. Could someone point out that why is this
    > difference? Is this
    > because of System created indexes for unique/primery
    > keys ? If yes then
    > is there any way out to move those indexes to diff
    > Tablespace ?
    >
    > Thanks for help....
    > Rajesh
    > --
    > Author: Rajesh Dayal
    > INET: Rajesh_at_ohitelecom.com
    >
    > Fat City Network Services -- (858) 538-5051 FAX:
    > (858) 538-5051
    > San Diego, California -- Public Internet
    > access / Mailing Lists
    >

    ---

    
    > To REMOVE yourself from this mailing list, send an
    > E-Mail message
    > to: ListGuru_at_fatcity.com (note EXACT spelling of
    > 'ListGuru') and in
    > the message BODY, include a line containing: UNSUB
    > ORACLE-L
    > (or the name of mailing list you want to be removed
    > from). You may
    > also send the HELP command for other information
    > (like subscribing).
    

    ---

Do You Yahoo!? Received on Tue Jun 27 2000 - 02:48:28 CDT