Re: Disable trigger by partition

On Mar 12, 5:03 pm, DA Morgan <damor..._at_psoug.org> wrote:
> Kevin Kirkpatrick wrote:
> > On Mar 12, 1:58 pm, "Kevin Kirkpatrick" <kvnkrkpt..._at_gmail.com> wrote:
> >> Is there a way to disable a table trigger for a specific partition or
> >> write partition-level triggers?
>
> > Oops - Oracle 10gR2
>
> My initial response is why and no.
>
> Could you describe, in more detail, what you are thinking and why you
> would want to do this.
> --
> Daniel A. Morgan
> University of Washington
> damor..._at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org

Trapped with an existing OneTrueLookUp table. Table has an Update/ Insert/Delete trigger checks a master list to see what auditting requirements are in place for a given OTLU name. A few large lookups have no audit requirements (so trigger does nothing) and experience periodic refreshes with a mass-delete followed by a mass-insert- select, which is where they experience the biggest performance hit from the trigger.

In an effort to "minimize the evil" of the OTLU design, I'm partitioning the OTLU table by lookup name which, among many other benefits, is allowing me to replace the mass-delete of these refreshes with "TRUNCATE PARTITION". However, I'd still like to avoid the trigger overhead on the mass-insert-select... It seeems the following options are available (barring a major redesign that excludes the OTLU table entirely):

1. dump new data to file and do a direct-path load of the partition (I assume this can be done, but need to review sqlldr specs)
2. modify the trigger def. to add a WHEN clause: "WHEN (NEW.lookup_name not in ('large_lookup1', 'large_lookup2', ...)", then use insert /*+ append */ select... in the refresh process
3. schedule refresh during downtime, disable trigger for whole table, truncate partition, run insert-append-select, then re-enable trigger

I'm leaning towards #2 - it's less flexible for accomodating new "large" lookups, but it ensures that *all* operations against the large lookups will avoid the performance hit. However, if anyone sees anything I might be missing here, I'd be interested in seeing it. Received on Tue Mar 13 2007 - 11:50:39 CDT