以下列举oracle 10g下几种常用的在线索引整理方式：

(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: Release 10.2.0.3.0 - Production on Mon Sep 19 20:14:22 2011

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

SQL> select * from v$version;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi

PL/SQL Release 10.2.0.3.0 - Production

CORE 10.2.0.3.0 Production

TNS for IBM/AIX RISC System/6000: Version 10.2.0.3.0 - Productio

NLSRTL Version 10.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> 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 Database 10g Enterprise Edition Release 10.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: Release 10.2.0.3.0 - Production on Mon Sep 19 23:24:02 2011

Connected to:

Oracle Database 10g Enterprise Edition Release 10.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> Disconnected from Oracle Database 10g Enterprise Edition Release 10.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 $ echo $ORACLE_SID

MS

oracle@ibmvs_a@/oracle/index_test $ sqlplus test/test

SQL*Plus: Release 10.2.0.3.0 - Production on Mon Sep 19 23:27:47 2011

Connected to:

Oracle Database 10g Enterprise Edition Release 10.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> Disconnected from Oracle Database 10g Enterprise Edition Release 10.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: Release 10.2.0.3.0 - Production on Mon Sep 19 23:29:26 2011

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.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 $ sqlplus test/test

SQL*Plus: Release 10.2.0.3.0 - Production on Mon Sep 19 23:29:32 2011

Connected to:

Oracle Database 10g Enterprise Edition Release 10.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> Disconnected from Oracle Database 10g Enterprise Edition Release 10.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: Release 10.2.0.3.0 - Production on Mon Sep 19 23:32:14 2011

Connected to:

Oracle Database 10g Enterprise Edition Release 10.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> Disconnected from Oracle Database 10g Enterprise Edition Release 10.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: Release 10.2.0.3.0 - Production on Mon Sep 19 23:34:56 2011

Connected to:

Oracle Database 10g Enterprise Edition Release 10.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> Disconnected from Oracle Database 10g Enterprise Edition Release 10.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: Release 10.2.0.3.0 - Production on Mon Sep 19 23:39:09 2011

Connected to:

Oracle Database 10g Enterprise Edition Release 10.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> Disconnected from Oracle Database 10g Enterprise Edition Release 10.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: Release 10.2.0.3.0 - Production on Mon Sep 19 23:42:31 2011

Connected to:

Oracle Database 10g Enterprise Edition Release 10.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>

其他几种组合的测试内容和步骤与上述内容雷同，篇幅关系不贴出来了。需要说明的是，数据库归档模式和非归档模式测试时，使用的是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

以下是测试前后索引统计信息（取自database archive，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时产生的redo时产生的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系统，大多数情况下我们不期望回缩索引高度。

ITPub博客

Oracle10g下的索引整理方式测试

最新文章