Re: triggers, sequence - need advice

Thank you for the input guys.

Question for Norman.
I have no problem pinning the triggers and I am just curious about the triggers being reloaded everytime they are fired. I thought if I dont pin them, they get reloaded only if they have been aged out.

Question for Connor.
I dont fully your statement below. I understand about soft and hard parses but not entirely sure abt your statement below. Could you pls explain a little bit more or point me to a good documentation.

>> Another option is to move the trigger code into PL/SQL program units so
>> that the underlying cursors are not soft parsed each time

You are right abt the problem with the index. I am not sure what I can do since I dont think I can use reverse-key index with RBO. We are still stuck with RBO because the app has not been rewritten to support CBO yet....slow :(( Let me know if this is not a true statement. And I cannot prepend it a with a randam char because I need (well not me, the job that process the rows) to process the rows in order.

thanks
Daud

Connor McDonald <connor_mcdonald_at_yahoo.com> wrote in message news:<3F2A3AE1.2E22_at_yahoo.com>...
> Daud wrote:
> >
> > We have an application which was running fine until our vendor install
> > a bunch of triggers on all tables to 'capture' data into an
> > intermediate table. From that table they have another process which
> > will read and xfer to another database (their own database...not
> > oracle) for reporting and other stuff. Now we are seeing some
> > performance problems with all these triggers around.
> > I would like to find out if there is anything that can be done to
> > improve (lets just say I simply can't remove all those triggers...but
> > I can rewrite to make them more efficient).
> > One each table, they have 3 triggers (after insert/update/delete) to
> > basically to the same thing....capture data and save in intermediate
> > table. Would I see any improvement by combining the three triggers
> > into just 1? When inserting data into the intermediate table they are
> > using an oracle sequence for sort of ordering (same sequence for
> > insert/update/delete from all tables). Should I change and use time
> > instead?
> > Anything else I can look into...perhaps what is the better way of
> > doing something like this?
> > Thanks!
>
> You could explore turning a row-at-a-time insert into an array insert
> with something like:
>
> Original code:
> before insert for each row
> insert into tab values (:new.x);
>
> New code:
> before insert for each row
> nested table type (idx) := :new.x;
>
> after insert statement level
> forall i in 1 .. nested table type.count
> insert into tab values (...)
>
> if the incoming SQL's are all row-at-a-time anyway then you wont see any
> gain.
>
> Another option is to move the trigger code into PL/SQL program units so
> that the underlying cursors are not soft parsed each time
>
> hth
> connor
Received on Fri Aug 01 2003 - 10:22:02 CDT