Re: Basic Trigger Question

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@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