Slow update after truncation

Question

I have a relatively simple update statement:

update sv_konginfo ki
set AnzDarl = 1 
where kong_nr in ( 
    select kong_nr
    from sv_darlehen
    group by kong_nr
    having count (*) = 1);

which works fine (about 1 second for about 150,000 records).

However, if I truncate the table and then reinsert the records:

truncate table sv_konginfo;

insert into sv_konginfo (kong_nr)
select distinct kong_nr
from sv_darlehen;

update instruction works very slowly (more than a minute), working with exactly the same data.

What can I do to improve performance in the second scenario? (We are using Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit.)

Answer 1

Thanks for the contribution, they helped me figure out what caused the problem: Chained Rows!

• after inserting new rows AnzDarl (and a number of other columns) are null
• 1 ( ),

, SQL:

select chain_cnt 
from user_tables 
where table_name='SV_KONGINFO';

Truncate chain_cnt 0. Update chain_cnt .

PCT_FREE, , :

alter table sv_konginfo pctfree 40;

, , .

Answer 2

ANALYZE TABLE sv_konginfo COMPUTE STATISTICS;

DBMS_STATS. .