Slow Update after Truncate

Question

I've got 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 runs okay on its own (about 1 second for about 150.000 records).

However, if I truncate the table and then re-insert the records:

truncate table sv_konginfo;

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

the update statement runs very slow (more than a minute) working on exactly the same data.

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

Answer 1

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

• after the insert of the new rows AnzDarl (and a number of other columns) are null
• when the columns are set to 1 (or other values), they occupy some more space

I was able to check this using the following SQL:

select chain_cnt 
from user_tables 
where table_name='SV_KONGINFO';

After the Truncate, the chain_cnt was 0. After running the Update, the chain_cnt increased dramatically and was equal to the number of affected rows.

Increasing PCT_FREE like this solved the performance issue for me:

alter table sv_konginfo pctfree 40;

Thanks again for the input, they helped to rule out some potential issues until finally chained rows rose to the top of my mind.

Answer 2

My first guess would be an

ANALYZE TABLE sv_konginfo COMPUTE STATISTICS;

or using DBMS_STATS. Have a look at Managing Schema Objects.