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

Accepted Answer

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.

Slow Update after Truncate

Solution

Problem