刪除 500 万条数据会产生多少归档？-凯发app官方网站

大量的删除、更新通常会导致数据库块出现碎片的情况，影响性能和浪费空间。

oracle是这样设计的：
this is best illustrated with an example: consider a transaction that updates a million row table. this obviously visits a large number of database blocks to make the change to the data. when the user commits the transaction oracle does not go back and revisit these blocks to make the change permanent. it is left for the next transaction that visits any block affected by the update to 'tidy up' the block (hence the term 'delayed block cleanout')
更新百万行表的事务显然会访问大量数据库块以对数据进行更改。当用户提交事务时，oracle不会返回并重新访问这些块以使更改永久化。它被留给下一个交易，该交易访问受更新影响的任何区块以“整理”区块（因此术语“延迟区块清理”）。

说到底还是为了性能。

评估库里哪些表存在碎片情况：

col frag format 9999.99
col owner format a30
col table_name format a30
select *
from
(select a.owner,
a.table_name,
a.num_rows,
round(a.avg_row_len * a.num_rows/1024/1024) esti_size_mb,
round(sum(b.bytes/1024/1024)) real_size_mb,
(a.avg_row_len * a.num_rows) / sum(b.bytes) frag
from dba_tables a, dba_segments b
where a.table_name = b.segment_name
and a.owner= b.owner
and b.bytes/1024/1024>100
and a.owner in
(select username
from dba_users
where oracle_maintained='n')
group by a.owner,a.table_name,a.avg_row_len, a.num_rows
having a.avg_row_len * a.num_rows / sum(b.bytes) < 0.7
order by sum(b.bytes) desc)
where rownum <= 30;

对100m以上的表进行估算，准确的情况可以参考show_space结果，详见之前的
http://blog.chinaunix.net/uid-20687159-id-5848525.html

如果发现碎片化高的表，消除的方法可以考虑：
1. alter table xxx move; --索引需要重建
2. create table new as select * from old; --适合保留数据较少的情况
3. 在线重定义

阅读(317) | 评论(0) | 转发(0) |

上一篇：删除1000万条数据会产生多少归档？

下一篇：在线瞎定义

给主人留下些什么吧！~~

| | | | |

感谢所有关心和支持过chinaunix的朋友们