关于索引的create offline、online和rebuild offline、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
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, 感谢原作者分享。