Re: constraint with reference to the same table

On Thu, 15 May 2003, Victor Yegorov wrote:

> * Rudi Starcevic <rudi(at)oasis(dot)net(dot)au> [15.05.2003 02:59]:
> > Hi,
> >
> > Can I confirm what this means then ..
> >
> > For large table's each column with ref. inegritry I should create an
> > index on those columns ?
>
> I think, that indicies are needed only at delete stage to decrease search
> time of possible referencing rows.
> Not only, of course, but when we speak about
> INSERT/UPDATE/DELETE data it is so.
>
> On the other side, indicies increases total query runtime, because for
> each row deleted/updated/inserted it'll be necessary to update each index.
>
> In my case, I at first drop "cyclic" constraints, do the job and then
> restore them.

That can be a win, but if you're actually dropping and adding the
constraint again it may not be on large tables since it'll still do a
whole bunch of index lookups to check the existing rows when the alter
table add constraint happens. Disabling triggers and re-enabling them is
faster but breaks the guarantee of the constraint.