RE: # of Indexes

Rajesh,

Yep, I missed the forest for the trees ;)

• Anita --- Rajesh Dayal <Rajesh_at_ohitelecom.com> wrote:
  > 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..
  >
  > Rajesh
  >
  > -----Original Message-----
  > From: A. Bardeen [mailto:abardeen1_at_yahoo.com]
  > Sent: Tuesday, June 27, 2000 11:48 AM
  > To: ORACLE-L_at_fatcity.com; Rajesh Dayal
  > Subject: Re: # of Indexes
  >
  >
  > 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!?
  > Get Yahoo! Mail - Free email you can access from
  > anywhere!
  > http://mail.yahoo.com/