重建索引时资源正忙

Question

有一个列为a的表T

CREATE TABLE T {
  id_t integer not null,
  text varchar2(100),
  a integer
} 
/

ALTER TABLE T ADD CONSTRAINT PK_T PRIMARY KEY (ID_T)
/

索引是这样创建的：

CREATE INDEX IDX_T$A ON T(a);

还有这样一个检查约束：

ALTER TABLE T ADD CONSTRAINT CHECK (a is null or a = 1);

T中的大多数记录都具有空值a，因此，如果索引处于一致状态并且其统计信息是最新的，则使用索引的查询速度会非常快。

但问题是，一些行的a值更改非常频繁(一些行的值为null，一些行的值为1)，我需要重新构建索引，比如说每小时一次。

但是，当作业执行此操作时，尝试重新构建索引时，通常会出现异常：

ORA-00054: resource busy and acquire with NOWAIT specified

有人能帮我解决这个问题吗？

Answer 1

大多数情况下不需要索引重建。当然，新创建的索引是有效的，并且它们的效率会随着时间的推移而降低。但这个过程会在一段时间后停止-它只是收敛到一定程度。

如果您确实需要优化索引，请尝试使用侵入性较小的DDL命令"ALTER INDEX SHRINK COMPACT“。

PS:我还建议您使用较小的块大小(4K或8K)作为表空间存储。

Answer 2

编辑:如果联机重建不可用，则可以在提交实体化视图时进行快速刷新，以存储行If或列A为1的行的主键。

首先看一下文档：

http://docs.oracle.com/cd/B28359_01/server.111/b28326/repmview.htm http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_6002.htm#SQLRF01302

特别要考虑这方面的资源需求:对主表的更改需要将更改向量写入物化视图日志，这实际上是对每个更改的附加插入。则必须将更改传播到具有附加查询的另一个表(物化视图存储表)。

Answer 3

Performance的重建

例如，快速浏览一下演示文稿Rebuilding the Truth就会发现，索引的行为方式并不像许多人认为的那样天真。

该演示文稿中的一个相关点是“完全删除的块被回收，通常不会有问题”。如果你的值完全改变了，那么你的索引不应该变得无限大。尽管您的索引以一种非典型的方式使用，但这种行为可能是一件好事。

这里有一个简单的例子。

--Create table, constraints, and index.
CREATE TABLE T
(
    id_t integer primary key,
    text varchar2(100),
    a integer check (a is null or a = 1)
);

CREATE INDEX IDX_T$A ON T(a);

--Insert 1M rows, with 100 "1"s.
insert into t
select level, level, case when mod(level, 10000) = 0 then 1 else null end
from dual connect by level <= 1000000;
commit;

--Initial sizes:
select segment_name, bytes/1024/1024 MB
from dba_segments
where segment_name in ('T', 'IDX_T$A');

SEGMENT_NAME    MB
T               19
IDX_T$A         0.0625

现在，将索引行完全打乱1000次。

--Move the 1s around 1000 times.  Takes about 6 minutes.
begin
    for i in 9000 .. 10000 loop
        update t
        set a = case when mod(id_t, i) = 0 then 1 else null end
        --Don't update if the vlaue is the same
        where nvl(a,-1) <> nvl(case when mod(id_t,i) = 0 then 1 else null end,-1);
        commit;
    end loop;
end;
/

索引段大小仍然是相同的。

--The the index size is the same.
select segment_name, bytes/1024/1024 MB
from dba_segments
where segment_name in ('T', 'IDX_T$A');

SEGMENT_NAME    MB
T               19
IDX_T$A         0.0625

统计的重建

如果总是有100行索引，那么行数、块数和差异度将保持不变。

如果100行从完全随机转变为彼此非常接近，那么聚类因子可能会发生显着变化。但即便如此，这也可能无关紧要。如果有数百万行，但只有100个索引，则无论集群因子如何，优化器的决策可能都是相同的。读取1个块(惊人的聚类因子)或读取100个块(最坏情况下的聚类因子)看起来仍然比执行数百万行的全表扫描要好得多。

但是统计数据很复杂，我肯定是把事情简单化了。如果您需要以特定的方式保存统计信息，则可能需要锁定它们。不幸的是，您不能只锁定一个索引，但您可以锁定表及其相关索引。

begin
    dbms_stats.lock_table_stats(ownname => user, tabname => 'T');
end;
/

无论如何都要重建

如果仍然需要重建，@Robe Eleckers重试的想法应该是可行的。尽管与异常相比，设置DDL_LOCK_TIMEOUT会更容易一些。

alter session set ddl_lock_timeout = 500;

会话仍然需要获得表上的独占锁，但这将使它更容易找到正确的机会窗口。