Re: Basic Trigger Question

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 Sat Sep 01 2001 - 05:48:57 CDT