Re: Basic Trigger Question

From: Buck Turgidson <noway_at_nohow.com>
Date: Fri, 31 Aug 2001 23:50:55 GMT
Message-ID: <zpVj7.211822$J37.53545204@typhoon.southeast.rr.com>

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.

"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:
> > := . ( @ % ;
>
>
Received on Fri Aug 31 2001 - 18:50:55 CDT