(1) alter index rebuild
(2) alter index rebuild online
(3) alter index coalesce
(4) alter index shrink space
(5) alter index shrink space compact
(6) alter index shrink space cascade
这里简单做一下说明：
alter index rebuild
扫描现有索引块进行索引重建，会下降索引高水位，一般执行速度快，但会全程对表加锁，阻塞DML操作。
alter index rebuild online
扫描全表进行索引重建，会下降索引高水位，一般执行速度慢，只在开始和结束时对表加锁，执行中间不阻塞DML操作。
alter index coalesce
对索引块做合并操作，不会下降索引高水位，可随时中断。全程不阻塞DML操作。
alter index shrink space
收缩索引并降低高水位。在结束时对表加锁，执行中间不阻塞DML操作。
alter index shrink space compact
收缩索引不降低高水位。全程不阻塞DML操作。
alter index shrink space cascade
收缩索引同时收缩表，一般不怎么使用。在结束时对表加锁，执行中间不阻塞DML操作。
本次实验主要就上述1至5号方法进行实验，主要考察的是各索引整理方式对数据库资源和时间的需求。
测试考虑的方面：
（1） 数据库是否置为归档模式
（2） 索引状态（nologging还是logging）
（3） 索引整理时间
（4） 对undo、redo、temp的资源需求
测试用到的脚本：
脚本index_stats.sql（用于收集索引状态）：
set echo on
set linesize 300 pagesize 300
analyze index idx1_t validate structure;
select height,blocks,lf_blks,br_blks,br_rows,del_lf_rows,btree_space,used_space,pct_used from index_stats;
analyze index idx2_t validate structure;
select height,blocks,lf_blks,br_blks,br_rows,del_lf_rows,btree_space,used_space,pct_used from index_stats;
analyze index idx3_t validate structure;
select height,blocks,lf_blks,br_blks,br_rows,del_lf_rows,btree_space,used_space,pct_used from index_stats;
analyze index idx4_t validate structure;
select height,blocks,lf_blks,br_blks,br_rows,del_lf_rows,btree_space,used_space,pct_used from index_stats;
analyze index idx5_t validate structure;
select height,blocks,lf_blks,br_blks,br_rows,del_lf_rows,btree_space,used_space,pct_used from index_stats;
脚本1.sql：
set echo on
set timing on
set linesize 300 pagesize 300
select vs.name,to_char(ms.value) from v$mystat ms,v$statname vs where ms.statistic#=vs.statistic#
and vs.name in ('sorts (memory)','sorts (disk)','sorts (rows)','redo size','undo change vector size','consistent gets');
alter index idx1_t rebuild;
select vs.name,to_char(ms.value) from v$mystat ms,v$statname vs where ms.statistic#=vs.statistic#
and vs.name in ('sorts (memory)','sorts (disk)','sorts (rows)','redo size','undo change vector size','consistent gets');
脚本2.sql：
set echo on
set timing on
set linesize 300 pagesize 300
select vs.name,to_char(ms.value) from v$mystat ms,v$statname vs where ms.statistic#=vs.statistic#
and vs.name in ('sorts (memory)','sorts (disk)','sorts (rows)','redo size','undo change vector size','consistent gets');
alter index idx2_t rebuild online;
select vs.name,to_char(ms.value) from v$mystat ms,v$statname vs where ms.statistic#=vs.statistic#
and vs.name in ('sorts (memory)','sorts (disk)','sorts (rows)','redo size','undo change vector size','consistent gets');
脚本3.sql：
set echo on
set timing on
set linesize 300 pagesize 300
select vs.name,to_char(ms.value) from v$mystat ms,v$statname vs where ms.statistic#=vs.statistic#
and vs.name in ('sorts (memory)','sorts (disk)','sorts (rows)','redo size','undo change vector size','consistent gets');
alter index idx3_t coalesce;
select vs.name,to_char(ms.value) from v$mystat ms,v$statname vs where ms.statistic#=vs.statistic#
and vs.name in ('sorts (memory)','sorts (disk)','sorts (rows)','redo size','undo change vector size','consistent gets');
脚本4.sql：
set echo on
set timing on
set linesize 300 pagesize 300
select vs.name,to_char(ms.value) from v$mystat ms,v$statname vs where ms.statistic#=vs.statistic#
and vs.name in ('sorts (memory)','sorts (disk)','sorts (rows)','redo size','undo change vector size','consistent gets');
alter index idx4_t shrink space;
select vs.name,to_char(ms.value) from v$mystat ms,v$statname vs where ms.statistic#=vs.statistic#
and vs.name in ('sorts (memory)','sorts (disk)','sorts (rows)','redo size','undo change vector size','consistent gets');
脚本5.sql：
set echo on
set timing on
set linesize 300 pagesize 300
select vs.name,to_char(ms.value) from v$mystat ms,v$statname vs where ms.statistic#=vs.statistic#
and vs.name in ('sorts (memory)','sorts (disk)','sorts (rows)','redo size','undo change vector size','consistent gets');
alter index idx5_t shrink space compact;
select vs.name,to_char(ms.value) from v$mystat ms,v$statname vs where ms.statistic#=vs.statistic#
and vs.name in ('sorts (memory)','sorts (disk)','sorts (rows)','redo size','undo change vector size','consistent gets');
以下是在数据库归档模式下，索引状态创建为logging时的整个测试过程：
oracle@ibmvs_a@/oracle $ export ORACLE_SID=MS
oracle@ibmvs_a@/oracle $ sqlplus / as sysdba
SQL*Plus: Release10.2.0.3.0 - Production on Mon Sep 19 20:14:22 2011
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database10gEnterprise Edition Release10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database10gEnterpriseEdition Release10.2.0.3.0 - 64bi
PL/SQL Release10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for IBM/AIX RISC System/6000: Version10.2.0.3.0 - Productio
NLSRTL Version10.2.0.3.0 – Production
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /msarch
Oldest online log sequence 951
Next log sequence to archive 955
Current log sequence 955
SQL> conn test/test
Connected.
SQL>
SQL>
SQL>
SQL> create table t (num1 number,num2 number,num3 number,num4 number,num5 number,name varchar2(10));
Table created.
SQL> set timing on
SQL> begin
2 for i in 1 .. 30000000
3 loop
4 insert into t values(i,i,i,i,i,'aa');
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
Elapsed: 00:22:52.41
SQL> commit;
Commit complete.
Elapsed: 00:00:00.00
SQL> create index idx1_t on t(num1) logging;
Index created.
Elapsed: 00:01:59.04
SQL> create index idx2_t on t(num2) logging;
Index created.
Elapsed: 00:01:50.82
SQL> create index idx3_t on t(num3) logging;
Index created.
Elapsed: 00:01:47.24
SQL>
SQL> create index idx4_t on t(num4) logging;
Index created.
Elapsed: 00:01:49.92
SQL> create index idx5_t on t(num5) logging;
Index created.
Elapsed: 00:01:35.73
SQL> delete t where mod(num1,3)=1;
10000000 rows deleted.
Elapsed: 00:31:23.89
SQL> commit;
Commit complete.
Elapsed: 00:00:00.00
SQL> Disconnected from Oracle Database10gEnterprise Edition Release10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
oracle@ibmvs_a@/oracle $ cd index_test
oracle@ibmvs_a@/oracle/index_test $ ls -rlt
total 48
-rw-r--r-- 1 oracle dba 793 Sep 19 15:29 index_stats.sql
-rw-r--r-- 1 oracle dba 529 Sep 19 15:33 1.sql
-rw-r--r-- 1 oracle dba 536 Sep 19 15:33 2.sql
-rw-r--r-- 1 oracle dba 530 Sep 19 15:33 3.sql
-rw-r--r-- 1 oracle dba 534 Sep 19 15:34 4.sql
-rw-r--r-- 1 oracle dba 542 Sep 19 15:34 5.sql
oracle@ibmvs_a@/oracle/index_test $ export ORACLE_SID=MS
oracle@ibmvs_a@/oracle/index_test $ sqlplus test/test
SQL*Plus: Release10.2.0.3.0 - Production on Mon Sep 19 23:24:02 2011
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database10gEnterprise Edition Release10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> @index_stats.sql
SQL>
SQL> set linesize 300 pagesize 300
SQL>
SQL> analyze index idx1_t validate structure;
Index analyzed.
SQL>
SQL> select height,blocks,lf_blks,br_blks,br_rows,del_lf_rows,btree_space,used_space,pct_used from index_stats;
HEIGHT BLOCKS LF_BLKS BR_BLKS BR_ROWS DEL_LF_ROWS BTREE_SPACE USED_SPACE PCT_USED
---------- ---------- ---------- ---------- ---------- ----------- ----------- ---------- ----------
3 71680 70947 116 70946 0 568223460 340044598 60
SQL>
SQL> analyze index idx2_t validate structure;
Index analyzed.
SQL>
SQL> select height,blocks,lf_blks,br_blks,br_rows,del_lf_rows,btree_space,used_space,pct_used from index_stats;
HEIGHT BLOCKS LF_BLKS BR_BLKS BR_ROWS DEL_LF_ROWS BTREE_SPACE USED_SPACE PCT_USED
---------- ---------- ---------- ---------- ---------- ----------- ----------- ---------- ----------
3 71680 70947 116 70946 0 568223460 340044598 60
SQL>
SQL> analyze index idx3_t validate structure;
Index analyzed.
SQL>
SQL> select height,blocks,lf_blks,br_blks,br_rows,del_lf_rows,btree_space,used_space,pct_used from index_stats;
HEIGHT BLOCKS LF_BLKS BR_BLKS BR_ROWS DEL_LF_ROWS BTREE_SPACE USED_SPACE PCT_USED
---------- ---------- ---------- ---------- ---------- ----------- ----------- ---------- ----------
3 71680 70947 116 70946 0 568223460 340044598 60
SQL>
SQL> analyze index idx4_t validate structure;
Index analyzed.
SQL>
SQL> select height,blocks,lf_blks,br_blks,br_rows,del_lf_rows,btree_space,used_space,pct_used from index_stats;
HEIGHT BLOCKS LF_BLKS BR_BLKS BR_ROWS DEL_LF_ROWS BTREE_SPACE USED_SPACE PCT_USED
---------- ---------- ---------- ---------- ---------- ----------- ----------- ---------- ----------
3 71680 70947 116 70946 0 568223460 340044598 60
SQL>
SQL> analyze index idx5_t validate structure;
Index analyzed.
SQL>
SQL> select height,blocks,lf_blks,br_blks,br_rows,del_lf_rows,btree_space,used_space,pct_used from index_stats;
HEIGHT BLOCKS LF_BLKS BR_BLKS BR_ROWS DEL_LF_ROWS BTREE_SPACE USED_SPACE PCT_USED
---------- ---------- ---------- ---------- ---------- ----------- ----------- ---------- ----------
3 71680 70947 116 70946 0 568223460 340044598 60
SQL>
SQL>
SQL>
SQL>
SQL> Disconnected from Oracle Database10gEnterprise Edition Release10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
oracle@ibmvs_a@/oracle/index_test $
oracle@ibmvs_a@/oracle/index_test $
oracle@ibmvs_a@/oracle/index_test $
oracle@ibmvs_a@/oracle/index_test $
oracle@ibmvs_a@/oracle/index_test $ echo $ORACLE_SID
MS
oracle@ibmvs_a@/oracle/index_test $ sqlplus test/test
SQL*Plus: Release10.2.0.3.0 - Production on Mon Sep 19 23:27:47 2011
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database10gEnterprise Edition Release10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> @1.sql
SQL>
SQL> set timing on
SQL>
SQL> set linesize 300 pagesize 300
SQL>
SQL> select vs.name,to_char(ms.value) from v$mystat ms,v$statname vs where ms.statistic#=vs.statistic#
2 and vs.name in ('sorts (memory)','sorts (disk)','sorts (rows)','redo size','undo change vector size','consistent gets');
NAME TO_CHAR(MS.VALUE)
---------------------------------------------------------------- ----------------------------------------
consistent gets 19
redo size 0
undo change vector size 0
sorts (memory) 4
sorts (disk) 0
sorts (rows) 0
6 rows selected.
Elapsed: 00:00:00.00
SQL>
SQL> alter index idx1_t rebuild;
Index altered.
Elapsed: 00:01:11.76
SQL>
SQL> select vs.name,to_char(ms.value) from v$mystat ms,v$statname vs where ms.statistic#=vs.statistic#
2 and vs.name in ('sorts (memory)','sorts (disk)','sorts (rows)','redo size','undo change vector size','consistent gets');
NAME TO_CHAR(MS.VALUE)
---------------------------------------------------------------- ----------------------------------------
consistent gets 74429
redo size 390230240
undo change vector size 77808
sorts (memory) 18
sorts (disk) 1
sorts (rows) 20000014
6 rows selected.
Elapsed: 00:00:00.02
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> Disconnected from Oracle Database10gEnterprise Edition Release10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
oracle@ibmvs_a@/oracle/index_test $ sqlplus / as sysdba
SQL*Plus: Release10.2.0.3.0 - Production on Mon Sep 19 23:29:26 2011
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database10gEnterprise Edition Release10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> Disconnected from Oracle Database10gEnterprise Edition Release10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
oracle@ibmvs_a@/oracle/index_test $
oracle@ibmvs_a@/oracle/index_test $
oracle@ibmvs_a@/oracle/index_test $
oracle@ibmvs_a@/oracle/index_test $ sqlplus test/test
SQL*Plus: Release10.2.0.3.0 - Production on Mon Sep 19 23:29:32 2011
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database10gEnterprise Edition Release10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL>
SQL> @2.sql
SQL>
SQL> set timing on
SQL>
SQL> set linesize 300 pagesize 300
SQL>
SQL> select vs.name,to_char(ms.value) from v$mystat ms,v$statname vs where ms.statistic#=vs.statistic#
2 and vs.name in ('sorts (memory)','sorts (disk)','sorts (rows)','redo size','undo change vector size','consistent gets');
NAME TO_CHAR(MS.VALUE)
---------------------------------------------------------------- ----------------------------------------
consistent gets 22
redo size 0
undo change vector size 0
sorts (memory) 4
sorts (disk) 0
sorts (rows) 0
6 rows selected.
Elapsed: 00:00:00.00
SQL>
SQL> alter index idx2_t rebuild online;
Index altered.
Elapsed: 00:01:20.04
SQL>
SQL> select vs.name,to_char(ms.value) from v$mystat ms,v$statname vs where ms.statistic#=vs.statistic#
2 and vs.name in ('sorts (memory)','sorts (disk)','sorts (rows)','redo size','undo change vector size','consistent gets');
NAME TO_CHAR(MS.VALUE)
---------------------------------------------------------------- ----------------------------------------
consistent gets 335368
redo size 390276668
undo change vector size 90500
sorts (memory) 13
sorts (disk) 1
sorts (rows) 20000385
6 rows selected.
Elapsed: 00:00:00.01
SQL>
SQL>
SQL>
SQL> Disconnected from Oracle Database10gEnterprise Edition Release10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
oracle@ibmvs_a@/oracle/index_test $ sqlplus test/test
SQL*Plus: Release10.2.0.3.0 - Production on Mon Sep 19 23:32:14 2011
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database10gEnterprise Edition Release10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> @3.sql
SQL>
SQL> set timing on
SQL>
SQL> set linesize 300 pagesize 300
SQL>
SQL> select vs.name,to_char(ms.value) from v$mystat ms,v$statname vs where ms.statistic#=vs.statistic#
2 and vs.name in ('sorts (memory)','sorts (disk)','sorts (rows)','redo size','undo change vector size','consistent gets');
NAME TO_CHAR(MS.VALUE)
---------------------------------------------------------------- ----------------------------------------
consistent gets 18
redo size 0
undo change vector size 0
sorts (memory) 4
sorts (disk) 0
sorts (rows) 0
6 rows selected.
Elapsed: 00:00:00.01
SQL>
SQL> alter index idx3_t coalesce;
Index altered.
Elapsed: 00:00:53.15
SQL>
SQL> select vs.name,to_char(ms.value) from v$mystat ms,v$statname vs where ms.statistic#=vs.statistic#
2 and vs.name in ('sorts (memory)','sorts (disk)','sorts (rows)','redo size','undo change vector size','consistent gets');
NAME TO_CHAR(MS.VALUE)
---------------------------------------------------------------- ----------------------------------------
consistent gets 2073
redo size 968455380
undo change vector size 588396004
sorts (memory) 5
sorts (disk) 0
sorts (rows) 385
6 rows selected.
Elapsed: 00:00:00.00
SQL>
SQL>
SQL>
SQL> Disconnected from Oracle Database10gEnterprise Edition Release10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
oracle@ibmvs_a@/oracle/index_test $ sqlplus test/test
SQL*Plus: Release10.2.0.3.0 - Production on Mon Sep 19 23:34:56 2011
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database10gEnterprise Edition Release10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> @4.sql
SQL>
SQL> set timing on
SQL>
SQL> set linesize 300 pagesize 300
SQL>
SQL> select vs.name,to_char(ms.value) from v$mystat ms,v$statname vs where ms.statistic#=vs.statistic#
2 and vs.name in ('sorts (memory)','sorts (disk)','sorts (rows)','redo size','undo change vector size','consistent gets');
NAME TO_CHAR(MS.VALUE)
---------------------------------------------------------------- ----------------------------------------
consistent gets 18
redo size 0
undo change vector size 0
sorts (memory) 4
sorts (disk) 0
sorts (rows) 0
6 rows selected.
Elapsed: 00:00:00.01
SQL>
SQL> alter index idx4_t shrink space;
Index altered.
Elapsed: 00:02:50.80
SQL>
SQL> select vs.name,to_char(ms.value) from v$mystat ms,v$statname vs where ms.statistic#=vs.statistic#
2 and vs.name in ('sorts (memory)','sorts (disk)','sorts (rows)','redo size','undo change vector size','consistent gets');
NAME TO_CHAR(MS.VALUE)
---------------------------------------------------------------- ----------------------------------------
consistent gets 32813
redo size 1247775896
undo change vector size 726076036
sorts (memory) 10
sorts (disk) 0
sorts (rows) 408
6 rows selected.
Elapsed: 00:00:00.00
SQL>
SQL>
SQL>
SQL> Disconnected from Oracle Database10gEnterprise Edition Release10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
oracle@ibmvs_a@/oracle/index_test $ sqlplus test/test
SQL*Plus: Release10.2.0.3.0 - Production on Mon Sep 19 23:39:09 2011
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database10gEnterprise Edition Release10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> @5.sql
SQL>
SQL> set timing on
SQL>
SQL> set linesize 300 pagesize 300
SQL>
SQL> select vs.name,to_char(ms.value) from v$mystat ms,v$statname vs where ms.statistic#=vs.statistic#
2 and vs.name in ('sorts (memory)','sorts (disk)','sorts (rows)','redo size','undo change vector size','consistent gets');
NAME TO_CHAR(MS.VALUE)
---------------------------------------------------------------- ----------------------------------------
consistent gets 18
redo size 0
undo change vector size 0
sorts (memory) 4
sorts (disk) 0
sorts (rows) 0
6 rows selected.
Elapsed: 00:00:00.01
SQL>
SQL> alter index idx5_t shrink space compact;
Index altered.
Elapsed: 00:02:38.80
SQL>
SQL> select vs.name,to_char(ms.value) from v$mystat ms,v$statname vs where ms.statistic#=vs.statistic#
2 and vs.name in ('sorts (memory)','sorts (disk)','sorts (rows)','redo size','undo change vector size','consistent gets');
NAME TO_CHAR(MS.VALUE)
---------------------------------------------------------------- ----------------------------------------
consistent gets 32508
redo size 1246365028
undo change vector size 725950548
sorts (memory) 9
sorts (disk) 0
sorts (rows) 396
6 rows selected.
Elapsed: 00:00:00.00
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> Disconnected from Oracle Database10gEnterprise Edition Release10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
oracle@ibmvs_a@/oracle/index_test $ sqlplus test/test
SQL*Plus: Release10.2.0.3.0 - Production on Mon Sep 19 23:42:31 2011
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database10gEnterprise Edition Release10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> @index_stats.sql
SQL>
SQL> set linesize 300 pagesize 300
SQL>
SQL> analyze index idx1_t validate structure;
Index analyzed.
SQL>
SQL> select height,blocks,lf_blks,br_blks,br_rows,del_lf_rows,btree_space,used_space,pct_used from index_stats;
HEIGHT BLOCKS LF_BLKS BR_BLKS BR_ROWS DEL_LF_ROWS BTREE_SPACE USED_SPACE PCT_USED
---------- ---------- ---------- ---------- ---------- ----------- ----------- ---------- ----------
3 48128 47298 78 47297 0 378820992 339736992 90
SQL>
SQL> analyze index idx2_t validate structure;
Index analyzed.
SQL>
SQL> select height,blocks,lf_blks,br_blks,br_rows,del_lf_rows,btree_space,used_space,pct_used from index_stats;
HEIGHT BLOCKS LF_BLKS BR_BLKS BR_ROWS DEL_LF_ROWS BTREE_SPACE USED_SPACE PCT_USED
---------- ---------- ---------- ---------- ---------- ----------- ----------- ---------- ----------
3 48128 47298 78 47297 0 378820992 339736992 90
SQL>
SQL> analyze index idx3_t validate structure;
Index analyzed.
SQL>
SQL> select height,blocks,lf_blks,br_blks,br_rows,del_lf_rows,btree_space,used_space,pct_used from index_stats;
HEIGHT BLOCKS LF_BLKS BR_BLKS BR_ROWS DEL_LF_ROWS BTREE_SPACE USED_SPACE PCT_USED
---------- ---------- ---------- ---------- ---------- ----------- ----------- ---------- ----------
3 71680 47300 116 47299 0 379142048 339737486 90
SQL>
SQL> analyze index idx4_t validate structure;
Index analyzed.
SQL>
SQL> select height,blocks,lf_blks,br_blks,br_rows,del_lf_rows,btree_space,used_space,pct_used from index_stats;
HEIGHT BLOCKS LF_BLKS BR_BLKS BR_ROWS DEL_LF_ROWS BTREE_SPACE USED_SPACE PCT_USED
---------- ---------- ---------- ---------- ---------- ----------- ----------- ---------- ----------
3 47712 47300 116 47299 0 379142048 339737486 90
SQL>
SQL> analyze index idx5_t validate structure;
Index analyzed.
SQL>
SQL> select height,blocks,lf_blks,br_blks,br_rows,del_lf_rows,btree_space,used_space,pct_used from index_stats;
HEIGHT BLOCKS LF_BLKS BR_BLKS BR_ROWS DEL_LF_ROWS BTREE_SPACE USED_SPACE PCT_USED
---------- ---------- ---------- ---------- ---------- ----------- ----------- ---------- ----------
3 71680 47300 116 47299 0 379142048 339737486 90
SQL>
SQL>
其他几种组合的测试内容和步骤与上述内容雷同，篇幅关系不贴出来了。需要说明的是，数据库归档模式和非归档模式测试时，使用的是2个数据库实例，从测试结果来看时间上存在一定差距。以下是测试结果：
| statistics | db archive index logging | db archive index nologging | db noarchive index logging | db noarchive index nologging | |
| index rebuild | consistent gets redo size undo change vector size sorts (memory) sorts (disk) sorts (rows) time | 74429 390230240 77808 18 1 20000014 00:01:11.76 | 74372 774744 77248 9 1 20000388 00:00:59.22 | 72922 390261216 77240 9 1 20000385 00:01:38.43 | 72975 776580 77540 11 1 20000410 00:01:29.14 |
| index rebuild online | consistent gets redo size undo change vector size sorts (memory) sorts (disk) sorts (rows) time | 335368 390276668 90500 13 1 20000385 00:01:20.04 | 335597 812824 89924 11 1 20000385 00:01:11.69 | 333935 390290240 90288 19 1 20000385 00:01:23.83 | 333941 814044 89980 11 1 20000385 00:01:19.77 |
| index coalesce | consistent gets redo size undo change vector size sorts (memory) sorts (disk) sorts (rows) time | 2073 968455380 588396004 5 0 385 00:00:53.15 | 444 968883064 588211108 5 0 385 00:01:07.19 | 1310 968173112 588210748 5 0 385 00:02:36.13 | 1930 969140664 588663752 5 0 385 00:02:37.26 |
| index shrink space | consistent gets redo size undo change vector size sorts (memory) sorts (disk) sorts (rows) time | 32813 1247775896 726076036 10 0 408 00:02:50.80 | 33223 1247618840 726092996 6 0 385 00:03:15.52 | 32837 1247549832 726077936 8 0 385 00:06:04.90 | 32828 1247529588 726043360 10 0 408 00:05:55.62 |
| index shrink space compact | consistent gets redo size undo change vector size sorts (memory) sorts (disk) sorts (rows) time | 32508 1246365028 725950548 9 0 396 00:02:38.80 | 34255 1246400812 725932788 9 0 396 00:02:46.95 | 32126 1246073596 725889664 9 0 396 00:06:03.30 | 32258 1246045212 725866864 9 0 396 00:05:33.24 |
以下是测试前后索引统计信息（取自databasearchive，index logging方式测试结果）：
| 执行前索引blocks值 | 执行后索引blocks值 | |
| alter index rebuild | 71680 | 48128 |
| alter index rebuild online | 71680 | 48128 |
| alter index coalesce | 71680 | 71680 |
| alter index shrink space | 71680 | 47712 |
| alter index shrink space compact | 71680 | 71680 |
通过实验，总结一下：
(1) 数据库是否为归档模式，对实验结果无影响。
(2) 产生的redo：
索引为nologging，使用index rebuild (online)产生的redo最少，之后的关系是索引设置为logging时使用index rebuild (online)时产生的redo<index coalesce时产生的redo<index shrink space (compact)时产生的redo。
(3) 产生的undo：
使用index rebuild产生的undo最少，使用index rebuild online产生的undo较少，index coalesce时产生的undo较多,index shrink space (compact)时产生的undo最多。
(4) 产生的temp：
index rebuild和index rebuild online都需要进行排序，消耗较多temp，剩余3种方式基本不消耗temp资源。
(5) 执行时间：
index shrink space (compact)执行时间最长。其他几种方式执行时间差不多。
(6) 索引高水位：
alter index coalesce和alter index shrink space compact不会降低索引高水位。其他几种方式会降低索引高水位。
结论：
对于一个可停机进行索引整理的系统，我会考虑将索引设置为nologging状态，执行alter index rebuild操作。这样会消耗一定的temp和较少的redo和undo。操作后，建议进行一次全库备份。
如果对于一个7*24小时的OLTP系统，我会考虑使用alter index coalesce对索引进行合并，这会消耗较多的redo和undo（有时候也会成为比较严重的IO负担，从我的实际经验来看，coalesce消耗的undo会比索引本身大小还要多），不会消耗temp资源。好处是此操作可以在线进行，随时可中断，不会阻塞DML语句，且不会降低索引高度。而对于一个OLTP系统，大多数情况下我们不期望回缩索引高度。
扫一扫
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
