Calculate Statistics after insert in a trigger

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 Received on Fri Sep 29 2006 - 06:47:18 CDT