|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;
dbms_stats.gather_table_stats(ownname=> NULL, tabname=> 'TABLE',
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?