Calculate Statistics after insert in a trigger

Giganews Newsgroups
Subject: Calculate Statistics after insert in a trigger
Posted by:  Roberto Nenni (rnenni.questo_…@toglierequiAccaErre.it)
Date: Fri, 29 Sep 2006

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

Replies