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

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 --

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@eds.com> wrote in message
news:1159535661.875615.57470@c28g2000cwb.googlegro ups.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 --
>