No, it would fire 500k times. Which is why I said performance would suffer
greatly.
Jim
"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 Sat Sep 01 2001 - 10:40:50 CDT
