Re: Proposal: TRUNCATE TABLE table RESTRICT

Don Baccus wrote:
>
> At 08:08 PM 6/10/00 +0200, Peter Eisentraut wrote:
> >Tatsuo Ishii writes:
> >
> >> That would be better. I am just wondering how the checkings hurt the
> >> speed of TRUNCATE (if TRUNCATE is that slow, why we need it:-).

The major performance difference between TRUNCATE and DELETE is
realized at VACUUM time.

> >
> >You can make any code arbitrarily fast if it doesn't have to behave
> >correctly. :-)
>
> Checking for existence or absence of triggers will be fast.
>
> Jan suggested aborting TRUNCATE if any (user or system) triggers
> are on the table. If I understood his message correctly, that is.
>
> Oracle only aborts for foreign keys, executing TRUNCATE and ignoring
> user triggers if they exist.
>
> Any thoughts?

I agree with this.

>
> Rather than abort TRUNCATE due to the mere existence of a referential
> integrity trigger on the table, we could be a bit more sophisicated
> and only abort if an RI trigger exists where the referring table is
> non-empty. If the referring table's empty, no foreign keys will be
> stored in it and you can safely TRUNCATE.

Sorry to ask for another favor, but what does Oracle do here? If
a referring table has 1,000,000 rows in it which have been
deleted but not vacuumed, what would the performance implications
be?

Just curious,

Mike Mascari