oracle - 如何判断通过重建索引将释放的表空间量

Question

如果重建表索引会释放超过 2GB 的表空间，我只想重建表索引。如何确定通过重建索引将释放的表空间量？

Answer 1

您可以使用DBMS_SPACE.CREATE_INDEX_COST估计已使用的空间量重建索引后。从 DBA_SEGMENTS.BYTES 中减去该值即可估算出节省的空间。

下面的示例显示 DBMS_SPACE 对重建异常糟糕的索引所节省的空间做出了相当准确的预测。套餐需要收集统计数据，因此您可能需要阅读有关从 this 收集统计数据所导致的潜在问题的信息。稍微相关的答案。

首先，创建一个表和示例数据，并收集统计数据。

drop table test1 purge;
create table test1(a number, b number, c number);

insert /*+ append */ into test1 select level, level, level
from dual connect by level <= 500000;
commit;

begin
    dbms_stats.gather_table_stats(user, 'TEST1');
end;
/

这表明 DBMS_SPACE 对新索引的成本进行了准确的预测。

declare
    v_used_bytes number;
    v_alloc_bytes number;
begin
    dbms_space.create_index_cost(
        ddl => 'create index test1_idx on test1(a, b, c)'
        ,used_bytes => v_used_bytes
        ,alloc_bytes => v_alloc_bytes
    );
    dbms_output.put_line('Esimated Bytes: '||
        trim(to_char(v_alloc_bytes,'999,999,999')));
end;
/

Esimated Bytes: 14,680,064


create index test1_idx on test1(a, b, c);
select trim(to_char(bytes, '999,999,999')) actual_bytes
    from dba_segments where segment_name = 'TEST1_IDX';

ACTUAL_BYTES
------------
15,728,640

现在模拟一个“坏”索引。一个常见的误解是索引不会自动重新使用空间。真正的问题是，在删除每个条目之前，索引不会重新声明叶 block 的空间。此示例删除 95% 的行，但空间量相同。

delete from test1 where mod(a, 20) <> 1;
commit;
select trim(to_char(bytes, '999,999,999')) actual_bytes
    from dba_segments where segment_name = 'TEST1_IDX';

ACTUAL_BYTES
------------
15,728,640

重新收集统计数据，现在估计值与重建后的实际大小非常相似。

begin
    dbms_stats.gather_table_stats(user, 'TEST1');
end;
/

declare
    v_used_bytes number;
    v_alloc_bytes number;
begin
    dbms_space.create_index_cost(
        ddl => 'create index test1_idx on test1(a, b, c)'
        ,used_bytes => v_used_bytes
        ,alloc_bytes => v_alloc_bytes
    );
    dbms_output.put_line('Esimated Bytes: '||
        trim(to_char(v_alloc_bytes,'999,999,999')));
end;
/

Esimated Bytes: 720,896


alter index test1_idx rebuild;
select trim(to_char(bytes, '999,999,999')) actual_bytes
    from dba_segments where segment_name = 'TEST1_IDX';

ACTUAL_BYTES
------------
851,968