If you have "FOR EACH ROW" clause in your trigger then the trigger is fired
for each row affected by
any SQL command. You may omit this clause, so that the trigger was fired
once per SQL command.
Another option is to disable the trigger while it is undesirable.
"Buck Turgidson" <noway_at_nohow.com> wrote in message
news:t23k7.214162$J37.54126297_at_typhoon.southeast.rr.com...
> If 500k rows were inserted in one transaction, the table would be
> analyzed 500k times? I was hoping that a trigger would do it once,
> upon completion of the transaction.
>
> I'll take your advice and modify the code, but academically, I am
> wondering if a trigger could fire just once. Trigges and PL/SQL
> aren't my strong suit.
>
>
>
>
> "Thomas Kyte" <tkyte_at_us.oracle.com> wrote in message
> news:9mpbvr01qpr_at_drn.newsguy.com...
> > In article <zpVj7.211822$J37.53545204_at_typhoon.southeast.rr.com>,
> "Buck says...
> > >
> > >The way this table is used is a batch job will do a mass insert of
> > >data into it, then the same job reads the table during next
> processing
> > >phase. Then it is deleted upon completion of the batch job. So,
> it
> > >isn't updated ad-hoc. There would really be only one FTS.
> > >
> > >There are performance problems with the SQL, so I was hoping the
> > >optimizer would make better decisions if it was analyzed after the
> > >table was populated.
> > >
> > >The only other option is to modify the code, and add a step to do
> so.
> > >
> > >
> >
> > well, unless you did the job in one "really big insert" -- i mean a
> single
> > insert, the analyze would run over and over and over again.
> >
> > The best solution would be to modify the mass insert program to
> simply execute
> > "analyze table" after it loaded it. Triggers are great for business
> rules,
> > complex default values and so on. To implement program logic, not
> so good.
> >
> >
> >
> > >
> > >"Jim Kennedy" <kennedy-family_at_home.com> wrote in message
> > >news:3OQj7.444139$p33.8508173_at_news1.sttls1.wa.home.com...
> > >> This will hurt your scalability greatly and not get you any
> > >performance
> > >> boost! What you are doing is saying anytime I have an insert or
> > >delete
> > >> please do a full table scan. (or close enough) OUCH.
> > >>
> > >> Instead you could run a job nightly that would do this (dbms_job)
> or
> > >there
> > >> is a way to have Oracle maintain these stats. I would have to do
> > >some
> > >> digging in the documentation. Perhaps look there. Probably
> under
> > >> Administration.
> > >>
> > >> Jim
> > >> "Buck Turgidson" <jc_va_at_hotmail.com> wrote in message
> > >> news:f98999c8.0108310939.2a6b416a_at_posting.google.com...
> > >> > This is probably a piece of cake for most people, but I am
> having
> > >> > trouble getting a simple trigger to work. I want to analyze a
> > >table
> > >> > that is populated and then subsequently used in the same batch
> > >> > process.
> > >> >
> > >> > I want to analyze it after an insert or delete, but am getting
> the
> > >> > following error. I would appreciate someone showing me the
> errors
> > >of
> > >> > my ways.
> > >> >
> > >> >
> > >> >
> > >> > SQL> CREATE OR REPLACE TRIGGER TR_MYTABLE
> > >> > 2 AFTER INSERT OR DELETE ON MYTABLE
> > >> > 3 BEGIN
> > >> > 4 ANALYZE TABLE MYTABLE ESTIMATE STATISTICS SAMPLE 30
> > >PERCENT;
> > >> > 5 END;
> > >> > 6 /
> > >> >
> > >> > Warning: Trigger created with compilation errors.
> > >> >
> > >> > SQL> SHOW ERRORS
> > >> > Errors for TRIGGER TR_MYTABLE:
> > >> >
> > >> > LINE/COL ERROR
> > >>
> >
> >> -------- ----------------------------------------------------------
> -
> > >------
> > >> > 2/11 PLS-00103: Encountered the symbol "TABLE" when
> expecting
> > >one
> > >> > of
> > >> > the following:
> > >> > := . ( @ % ;
> > >>
> > >>
> > >
> > >
> >
> > --
> > Thomas Kyte (tkyte_at_us.oracle.com)
> http://asktom.oracle.com/
> > Expert one on one Oracle, programming techniques and solutions for
> Oracle.
> > http://www.amazon.com/exec/obidos/ASIN/1861004826/
> > Opinions are mine and do not necessarily reflect those of Oracle
> Corp
> >
>
>
Received on Sun Sep 02 2001 - 04:16:09 CDT
