在 Oracle SQL 数据库中删除后如何删除“浪费的行" [英] How to remove 'wasted rows' after delete in Oracle SQL database

问题描述

在 Oracle sql 数据库中，我们系统中的一个进程从表中删除(未截断)大约 200 万行.这导致了大量浪费的行"，导致在该表上运行的查询需要超过 9 个小时，通常在 5 分钟内结束.经过检查，我们发现实际总行数的大小约为 2600MB，而包括浪费的行"在内的整个表的大小为 3700MB.

In Oracle sql database, a process in our system deleted (not truncated) approx 2 million rows from a table. This resulted in a huge number of 'wasted rows' causing the queries running on that table to take more than 9 hours which usually get over in 5 minutes. Upon checking, we found that the size of total number of actual rows was of around 2600MB whereas the overall table including 'wasted rows' had a size of 3700MB.

请告诉我删除行然后摆脱浪费的行"的最佳方法是什么，这样我们就不必每次都重建表.

Please let me know what is the best way to delete rows and then get rid of 'wasted rows' so that we don't have to rebuild the table every time.

推荐答案

让我们用一个用一些数据创建的测试表来模拟你的情况

Let's simulate your case with a test table created with some data

create table tst as 
select 
rownum id, lpad('x',1000,'y') pad
from dual 
connect by level <= 100000;

该表由 15K 个块组成

The table consist of 15K blocks

select blocks from user_segments
where segment_name = 'TST';

    BLOCKS
----------
     15360 

如果我们删除所有行，表格大小保持不变

If we delete all rows, the table size remains the same

delete from tst;
commit;

select blocks from user_segments
where segment_name = 'TST';

    BLOCKS
----------
     15360 

重新组织表格后，表格大小会随着空闲空间的减少而减小.

After reorganising the table the table size goes down as the free space is removed.

alter table tst MOVE;  

select blocks from user_segments
where segment_name = 'TST';

    BLOCKS
----------
         8 

请注意，此步骤需要暂停应用程序，重组中不允许进行任何更改.

Note that this step requires a downtime of the application, no changes are allowed within the reorganisation.

从 Oracle 12.2 开始，您可以执行此步骤 在线

Starting with Oracle 12.2 you can do this step ONLINE

这篇关于在 Oracle SQL 数据库中删除后如何删除“浪费的行"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！