Re: Calculate Statistics after insert in a trigger

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 - 08:14:21 CDT