怎么样在线创建索引或者重构索引/表

xsj011

在很多情况下，需要在线创建索引或者重组索引以及重组表，重新创建索引的主要原因是因为新的业务的发展的需要，而重组表与索引往往是因为索引的偏移膨胀或者是数据删除引起的稀疏状态，以及表数据删除引起的表数据的稀疏分布，这些情况下需要重组。
当然，如果需要创建的索引很小，或者是需要重组的索引与表都很小，创建与重组过程在几秒之内，这些都可以直接做而不需要讨论。实际情况是，业务很繁重以及表与索引都很大。这些情况下我们需要注意些什么呢？
1、重新创建新索引
首先，评估该索引的需要程度，如果不是特别紧急的大索引，最好在维护时间操作，然后评估该索引是否会对现有的语句造成负面影响，如导致以前的语句错误的走到这个新索引上（在日期打头的索引上，很容易出现这样的问题）。
根据索引大小以及需要在上面创建的表业务是否繁忙，如果业务繁忙，尽量选择业务不繁忙的时间，如凌晨2点-6点进行操作。创建索引之前评估索引的大小以及索引所在表空间剩余空间的大小。


代码:--------------------------------------------------------------------------------
SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",SPACE-NVL(FREE_SPACE,0) "USED_SPACE(M)",
ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2) "USED_RATE(%)",FREE_SPACE "FREE_SPACE(M)"
FROM
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
.--------------------------------------------------------------------------------

除此之外，还要评估临时表空间大小是否足够，用于创建索引时的排序操作。然后检查系统的负载压力状态。最后，确定要创建以后，采用online模式创建。

代码:--------------------------------------------------------------------------------
create index IND_BMW_USERS_FULLNAME_REGDATE on BMW_USERS (FULLNAME, USER_REGDATE)
tablespace TBS_INDEX2 online;
.--------------------------------------------------------------------------------

然后马上检查系统应用，如果发现有错误走到该索引的语句，并且有严重影响的，可能需要立即删除该索引或者约束。

代码:--------------------------------------------------------------------------------
alter table BMW_USERS drop constraint UK_BMW_USERS_USERID cascade;
drop index IND_BMW_USERS_FULLNAME_REGDATE;
.--------------------------------------------------------------------------------


通过观察，如果确定没有什么问题的，可以马上分析该索引。

代码:--------------------------------------------------------------------------------
begin
dbms_stats.gather_index_stats(ownname     => 'TAOBAO',
                    indname         => 'UK_BMW_USERS_USERID',
                    estimate_percent => 10
                    );
end;
.--------------------------------------------------------------------------------

不过，在一些情况下，为了防止创建完索引，执行计划立即出错的情况，我们可以把统计语句放到创建索引中，如：

代码:--------------------------------------------------------------------------------
create index IND_BMW_USERS_FULLNAME_REGDATE on BMW_USERS (FULLNAME, USER_REGDATE)
tablespace TBS_INDEX2 online [compute statistics];
.--------------------------------------------------------------------------------

在一些情况下，需要改造一个索引，如添加一个字段到索引或者从索引中删除一个字段，这个时候也需要重新创建索引，但是需要严格按照如下顺序来操作
a、 创建新的替代索引（如加字段或者减字段后的索引）
b、 分析该索引，并测试没有问题
c、 删除原来的索引

2、重组索引或者是表
如果索引或者是表因为更新太频繁或者是删除数据过多，可能引起段的数据稀疏分布，造成大量的空间浪费，并且严重影响表或者是索引的扫描速度。这样的情况下，我们需要对该索引或者是表进行重组。
重组之前，同样需要确认索引与表所在的表空间是否有足够的空间，如果是索引重组，还需要确认是否有足够的临时表空间用于排序。同样，如果索引或者表很大，而且使用比较频繁，请确认在业务不繁忙的时候操作。

代码:--------------------------------------------------------------------------------
alter index UK_BMW_USERS_USERID rebuild [tablespace tbs_index2] online;
alter table auction_winners move [tablespace bmw_auction];
.--------------------------------------------------------------------------------

注意：如果对表进行了move，那么该表所有的索引都将失效，必须重新rebuild。所以业务繁重的表不适合在线move。

如果确认完成，对索引也需要完成上面的分析工作，如果是表，采用如下脚本分析：

代码:--------------------------------------------------------------------------------
begin
dbms_stats.gather_table_stats(ownname     => 'TAOBAO',
                    tabname         => 'AUCTION_WINNERS',
                    estimate_percent => 10,
                    method_opt     => 'FOR ALL INDEXED COLUMNS'
                    );
end;
.--------------------------------------------------------------------------------

如果没有必要online模式，如维护时候，只需要去掉online关键字即可。
关于对分区表的move与分区索引的rebuild可以参考“怎么样管理分区表与分区索引”
关于对lob字段的move，可以参考“怎么样管理LOB字段”
表的重整，还可以参考表的在线重定义

murongfeng

呵...又让我学会了一些东本啊