截断后更新缓慢 [英] Slow Update after Truncate

问题描述

我有一个相对简单的更新语句:

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

它可以独立运行(大约1秒，可以记录约150.000条记录).

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.

在第二种情况下，我该如何提高性能? (我们使用的是Oracle数据库10g企业版10.2.0.3.0-64位.)

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

推荐答案

感谢您的投入，他们帮助我弄清楚了造成问题的原因:链式行！

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

• 在插入新行AnzDarl(以及许多其他列)之后为空
• 当列设置为1(或其他值)时，它们会占用更多空间

我能够使用以下SQL进行检查:

I was able to check this using the following SQL:

select chain_cnt 
from user_tables 
where table_name='SV_KONGINFO';

截断后，chain_cnt为0.运行更新后，chain_cnt急剧增加，等于受影响的行数.

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.

像这样增加PCT_FREE为我解决了性能问题:

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.

这篇关于截断后更新缓慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！