Re: triggers, sequence - need advice

Could you pls point me to a doc that says reverse-key index can be used by RBO. So far in my search I have not found any doc that says reverse-key index will/will not work with RBO. But I thought I read about it somewhere...quite some time ago.

thanks
Daud

"Jim Kennedy" <kennedy-down_with_spammers_at_no_spam.comcast.net> wrote in message news:<gdwWa.28729$Oz4.8852_at_rwcrnsc54>...
> 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 - 22:48:21 CDT