Re: triggers, sequence - need advice

reverse key index isn't relevant to cbo vs rbo.

Connor is correct. Put as much logic into packages and in the trigger just call the package procedure. Packages get compiled once. Triggers are not as efficient; parsed again and again as they are used. Jim

"Daud" <daud11_at_hotmail.com> wrote in message news:f0bf3cc3.0308010722.e814e41_at_posting.google.com...
> 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 - 11:05:32 CDT