关于索引的createoffline、online和rebuildoffline、online创

关于索引的创建方式：create online、create offline、rebuild online、rebuild offline四种，而这四种在性能方面是否有值得摸索的地方了，小鱼个人觉得还是有必要的。 先来看看create online和create offline创建索引的两种方式： SQL select * from v$vers

关于索引的创建方式：create online、create offline、rebuild online、rebuild offline四种，而这四种在性能方面是否有值得摸索的地方了，小鱼个人觉得还是有必要的。

先来看看create online和create offline创建索引的两种方式：
SQL> select * from v$version where rownum<2;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

SQL> create table t as select * from dba_objects;

Table created.

SQL> oradebug event 10053 trace name context forever,level 1;
Statement processed.

SQL> create index ind_id on t(object_id);

Index created.

SQL> drop index ind_id;

Index dropped.

SQL> create index ind_id on t(object_id) online;

Index created.

SQL> oradebug event 10053 trace name context off;
Statement processed.
SQL> oradebug tracefile_name;
g:\oracle\product\10.2.0\admin\ora10g\udump\ora10g_ora_8328.trc

看看event 10053 trace file
1） 直接offline创建索引
Current SQL statement for this session:
create index ind_id on t(object_id)

SYSTEM STATISTICS INFORMATION
*****************************
Using NOWORKLOAD Stats
CPUSPEED: 1220 millions instruction/sec
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
IOSEEKTIM: 10 milliseconds (default is 10)
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: T Alias: T
#Rows: 50217 #Blks: 689 AvgRowLen: 93.00
SINGLE TABLE ACCESS PATH
-----------------------------------------
BEGIN Single Table Cardinality Estimation
-----------------------------------------
Table: T Alias: T
Card: Original: 50217 Rounded: 50217 Computed: 50217.00 Non Adjusted: 50217.00
-----------------------------------------
END Single Table Cardinality Estimation
-----------------------------------------
Access Path: TableScan
Cost: 153.06 Resp: 153.06 Degree: 0
Cost_io: 152.00 Cost_cpu: 15452242
Resp_io: 152.00 Resp_cpu: 15452242
Best:: AccessPath: TableScan
Cost: 153.06 Degree: 1 Resp: 153.06 Card: 50217.00 Bytes: 0

Final - All Rows Plan: Best join order: 1
Cost: 177.0789 Degree: 1 Card: 50217.0000 Bytes: 251085
Resc: 177.0789 Resc_io: 176.0000 Resc_cpu: 15794071
Resp: 177.0789 Resp_io: 176.0000 Resc_cpu: 15794071

Plan Table
============
------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------+-----------------------------------+
| 0 | CREATE INDEX STATEMENT | | | | 177 | |
| 1 | INDEX BUILD NON UNIQUE | IND_ID | | | | |
| 2 | SORT CREATE INDEX | | 49K | 245K | | |
| 3 | TABLE ACCESS FULL | T | 49K | 245K | 153 | 00:00:02 |
------------------------------------------+-----------------------------------+

2） Online创建索引：
Current SQL statement for this session:
create index ind_id on t(object_id) online

--相同部分内容不重复列出

SINGLE TABLE ACCESS PATH
-----------------------------------------
BEGIN Single Table Cardinality Estimation
-----------------------------------------
Table: T Alias: T
Card: Original: 50217 Rounded: 50217 Computed: 50217.00 Non Adjusted: 50217.00
-----------------------------------------
END Single Table Cardinality Estimation
-----------------------------------------
Access Path: TableScan
Cost: 153.06 Resp: 153.06 Degree: 0
Cost_io: 152.00 Cost_cpu: 15452242
Resp_io: 152.00 Resp_cpu: 15452242
Best:: AccessPath: TableScan
Cost: 153.06 Degree: 1 Resp: 153.06 Card: 50217.00 Bytes: 0

*********************************
Final - All Rows Plan: Best join order: 1
Cost: 153.0555 Degree: 1 Card: 50217.0000 Bytes: 251085
Resc: 153.0555 Resc_io: 152.0000 Resc_cpu: 15452242
Resp: 153.0555 Resp_io: 152.0000 Resc_cpu: 15452242

Plan Table
============
------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------+-----------------------------------+
| 0 | CREATE INDEX STATEMENT | | | | 153 | |
| 1 | INDEX BUILD NON UNIQUE | IND_ID | | | | |
| 2 | SORT CREATE INDEX | | 49K | 245K | | |
| 3 | TABLE ACCESS FULL | T | 49K | 245K | 153 | 00:00:02 |
------------------------------------------+-----------------------------------+

这个在创建方式上都是去通过表扫描来创建索引,这个应该是很好理解的，因为此时没有索引，只能通过表扫描然后排序创建索引。

SQL> oradebug setmypid
Statement processed.
SQL> oradebug event 10053 trace name context forever,level 12;
Statement processed.
SQL> alter index ind_id rebuild;

Index altered.

SQL> alter index ind_id rebuild online;

Index altered.

SQL> oradebug event 10053 trace name context off;
Statement processed.
SQL> oradebug tracefile_name;
g:\oracle\product\10.2.0\admin\ora10g\udump\ora10g_ora_9960.trc

3） Offline rebuild index的trace信息
SINGLE TABLE ACCESS PATH
-----------------------------------------
BEGIN Single Table Cardinality Estimation
-----------------------------------------
Table: T Alias: T
Card: Original: 50217 Rounded: 50217 Computed: 50217.00 Non Adjusted: 50217.00
-----------------------------------------
END Single Table Cardinality Estimation
-----------------------------------------
Access Path: TableScan
Cost: 153.06 Resp: 153.06 Degree: 0
Cost_io: 152.00 Cost_cpu: 15452242
Resp_io: 152.00 Resp_cpu: 15452242
Best:: AccessPath: TableScan
Cost: 153.06 Degree: 1 Resp: 153.06 Card: 50217.00 Bytes: 0

Plan Table
============
------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------+-----------------------------------+
| 0 | CREATE INDEX STATEMENT | | | | 153 | |
| 1 | INDEX BUILD NON UNIQUE | IND_ID | | | | |
| 2 | SORT CREATE INDEX | | 49K | 245K | | |
| 3 | INDEX FAST FULL SCAN | IND_ID | | | | |
------------------------------------------+-----------------------------------+

4） Online rebuild 的trace信息
SINGLE TABLE ACCESS PATH
-----------------------------------------
BEGIN Single Table Cardinality Estimation
-----------------------------------------
Table: T Alias: T
Card: Original: 50217 Rounded: 50217 Computed: 50217.00 Non Adjusted: 50217.00
-----------------------------------------
END Single Table Cardinality Estimation
-----------------------------------------
Access Path: TableScan
Cost: 153.06 Resp: 153.06 Degree: 0
Cost_io: 152.00 Cost_cpu: 15452242
Resp_io: 152.00 Resp_cpu: 15452242
Best:: AccessPath: TableScan
Cost: 153.06 Degree: 1 Resp: 153.06 Card: 50217.00 Bytes: 0

Current SQL statement for this session:
create index ind_id on t(object_id) online

Plan Table
============
------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------+-----------------------------------+
| 0 | CREATE INDEX STATEMENT | | | | 153 | |
| 1 | INDEX BUILD NON UNIQUE | IND_ID | | | | |
| 2 | SORT CREATE INDEX | | 49K | 245K | | |
| 3 | TABLE ACCESS FULL | T | 49K | 245K | 153 | 00:00:02 |
------------------------------------------+-----------------------------------+

这里看出执行计划是存在差异的，offline rebuild index是通过现有的索引fts、排序来创建索引，而online rebuild index则是通过现有的表fts、sort排序来创建索引，可以看出两种rebuild方式的对象是不一样的，而且细心的话我们发觉10053 offline rebuild index的trace中，cbo分析的可选择的执行计划中没有index ffs的方式，只有tablescan的方式，但是执行计划下面却是列出了index fast full scan，这个确实小鱼也找过一些资料，没有发觉合理的解释。

看看rebuild online时oracle具体是如何实现在线dml的
PARSING IN CURSOR #2 len=33 dep=0 uid=0 oct=9 lid=0 tim=29773760836 hv=1974521930 ad='5d5072c8'
alter index ind_id rebuild online
END OF STMT
PARSE #2:c=156001,e=314135,p=13,cr=342,cu=0,mis=1,r=0,dep=0,og=1,tim=29773760831
BINDS #2:
=====================
PARSING IN CURSOR #5 len=41 dep=2 uid=0 oct=3 lid=0 tim=29773761671 hv=1572239410 ad='5da531a8'
select ts#,online$ from ts$ where name=:1
END OF STMT
PARSE #5:c=0,e=184,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,tim=29773761667
BINDS #5:
kkscoacd
Bind#0
oacdty=01 mxl=32(06) mxlc=00 mal=00 scl=00 pre=00
oacflg=20 fl2=0000 frm=01 csi=852 siz=32 off=0
kxsbbbfp=0c009d28 bln=32 avl=06 flg=05
value="SYSTEM"
EXEC #5:c=0,e=842,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,tim=29773762622
FETCH #5:c=0,e=25,p=0,cr=2,cu=0,mis=0,r=1,dep=2,og=4,tim=29773762677
=====================
PARSING IN CURSOR #3 len=158 dep=1 uid=0 oct=1 lid=0 tim=29773762842 hv=722598008 ad='5d506d28'
create table "SYS"."SYS_JOURNAL_56527" (C0 NUMBER, opcode char(1), partno number, rid rowid, primary key( C0 , rid )) organization index TABLESPACE "SYSTEM"
END OF STMT
PARSE #3:c=0,e=1689,p=0,cr=2,cu=0,mis=1,r=0,dep=1,og=4,tim=29773762838
BINDS #3:

这个"SYS"."SYS_JOURNAL_56527"是一个类似的日志表,记录online rebuild期间数据的改变,当索引创建完毕后,会把新的记录通过这个表更新到新的索引中,也正是因为这个日志表保证了在online rebuild index时不影响dml操作，在创建完毕后oracle会把这个日志表记录更新到索引时候会对表加锁，此时也会短暂的阻止表dml操作。

上面简单的分析了online rebuild和offline rebuild创建索引的两种方式，其中offline rebuild是直接根据现有的索引来创建的，创建方式是index fast full scan然后sort index create，而online index是单独根据现有的表段来table access scan然后sort index create，并在此期间创建一个类似的SYS_JOURNAL_56527日志表来记录创建期间表的dml操作记录，在创建完毕后将日志表的记录更新到新的索引中，并删除原来的旧的索引。

一般而言offline rebuild的方式要比online rebuild快一些，由于可以直接利用旧的索引来重建，而且索引一般是比表小的，index fast full scan相比也要比table access scan扫描成本低一些，而online rebuild最吸引用户的地方就是不影响在线的dml了。

文章中对于offline rebuild index中的10053 trace的实际的执行计划和cbo可选择执行计划确实是存在出入的，这个疑点大家有理解的也欢迎解惑，小鱼个人觉得是可选择执行计划中出现了问题,改天有兴趣换到oracle 11g中来看看是否修正了这个问题。

本文出自：http://www.dbaxiaoyu.com, 原文地址：http://www.dbaxiaoyu.com/archives/2346, 感谢原作者分享。