Re: Constraints and indexes

Note in-line

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
____Belgium__November (EOUG event - "Troubleshooting")
____UK_______December (UKOUG conference - "CBO")


Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___November


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"Luc Gyselinck" <Luc.Gyselinck_at_nospampandora.be> wrote in message
news:ChZkb.93491$1d3.4332642_at_phobos.telenet-ops.be...



>

> What if the first camp does an export of the database, followed by

an import


> into another? Import creates the indexes first and then the

constraints,


> wright?

>


I think this has varied with version of Oracle.
Current (v9.2) strategy on a full exp/imp is to create
the constraints disabled, create the indexes, enable
the constraints.  Which means that any suitable index
can be used to enforce the constraint, but a new index
will be created to enforce the constraint if there is no
suitable index in existence at that time.

This does mean that on an exp/imp cycle, an index
that you created for an explicit performance reason
can become the index used to support a constraint,
and the previously existing index that originally was
created 'by' the constraint need never be recreated.
This can caused problems - if not confusion.

Personally, I like to create the indexes, and then
create the constraints - in part because an index
created 'by' a constraint has a flag set in the ind$
table that causes the treedump event to crash.
More importantly, though, if a constraint could be
supported by two indexes, then one of them is
likely to be redundant or at least sub-optimal.