Re: Calculate Statistics after insert in a trigger

In case you are on 10G with the automatic stats collection enabled you can use the command

dbms_stats.lock_table_stats(ownname, tablename ) to stop stats from being collected on this table

HTH
Scott Watson

"Mark D Powell" <Mark.Powell_at_eds.com> wrote in message news:1159535661.875615.57470_at_c28g2000cwb.googlegroups.com...
>
> Roberto Nenni wrote:
>> Hi all and sorry for my english
>>
>> I have a table usually empty (0 rows) and the statistics know that
>> In a job i insert into it many rows with an 'insert into table select
>> from
>> .....' for example 2000 rows
>> This table is after joined with many others tables to obtain a results
>> The optimizer, believing the table empty, produce an access plan that
>> doesn't work correctly (18 minutes)
>>
>> I try to do this:
>> insert into table select from...
>> exec dbms_stats.gather_table_stats(ownname=> NULL, tabname=> 'TABLE',
>> partname=> NULL); /*calculate statistics */
>> execute query that work fine (15 seconds)
>>
>> So i think: 'i put the calculation of statistic in a trigger':
>> CREATE OR REPLACE TRIGGER TABLE_STAT
>> after insert on TABLE
>> DECLARE PRAGMA AUTONOMOUS_TRANSACTION;
>> begin
>> dbms_stats.gather_table_stats(ownname=> NULL, tabname=> 'TABLE',
>> partname=> NULL);
>> end;
>>
>> but at this time the statistics are not correct because the commit is
>> not
>> issued and the table seems empty
>>
>> is it possible to invoke a trigger after the commit operation?
>>
>> any other ideas?
>>
>> tia
>> Roberto
>
> For tables with data pattens like you describe, 0 - X rows, analyze the
> table when it has X rows and the resulting plans should work just fine
> when the table is empty. Do not allow the table to be reanalyzed.
>
> HTH -- Mark D Powell --
>
Received on Fri Sep 29 2006 - 12:13:12 CDT