Createoffline, online, rebuildoffline, and online

Indexes can be created in four ways: createonline, createoffline, rebuildonline, and rebuildoffline. If these four methods are worth exploring in terms of performance, I think it is necessary. Let's take a look at the two ways to create indexes in createonline and createoffline: SQLselect * fromv $ vers

How to create indexes: create online, create offline, rebuild online, and rebuild offline. Are these four indexes worth exploring in terms of performance, ainemo personally think it is necessary. Let's take a look at two ways to create an index: create online and create offline: SQL select * from v $ vers

How to create indexes: create online, create offline, rebuild online, and rebuild offline. Are these four indexes worth exploring in terms of performance, ainemo personally think it is necessary.

Let's take a look at two ways to create an index: create online and 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

See event 10053 trace file
1) directly create an index using 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_cpus: 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 | 49 K | 245 K |
| 3 | table access full | T | 49 K | 245 K | 153 | 00:00:02 |
------------------------------------------ + ----------------------------------- +

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

-- The content of the same part is not repeatedly listed.

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_cpus: 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 | 49 K | 245 K |
| 3 | table access full | T | 49 K | 245 K | 153 | 00:00:02 |
------------------------------------------ + ----------------------------------- +

This method is used to create an index through table scan. This should be easy to understand, because there is no index at this time, you can only create an index through table scan and sorting.

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) trace information of Offline rebuild index
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_cpus: 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 | 49 K | 245 K |
| 3 | index fast full scan | IND_ID |
------------------------------------------ + ----------------------------------- +

4) trace information of Online rebuild
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_cpus: 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 | 49 K | 245 K |
| 3 | table access full | T | 49 K | 245 K | 153 | 00:00:02 |
------------------------------------------ + ----------------------------------- +

Here we can see that there are differences in the execution plan. offline rebuild index creates an index through the existing index fts and sorting, the online rebuild index creates an index through the existing table fts and sort sorting. We can see that the objects in the two rebuild methods are different, in the trace of 10053 offline rebuild index, the cbo analysis's optional execution plan does not have the index ffs method, but only the tablescan method, however, index fast full scan is listed below the execution plan. This is true, ainemo also looked for some information and did not find a reasonable explanation.

Let's see how oracle implements online dml in rebuild online.
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:

This "SYS ". "SYS_JOURNAL_56527" is a similar log table that records data changes during online rebuild. After the index is created, new records are updated to the new index through this table, this log table ensures that dml operations are not affected during online rebuild index. After the creation, oracle will update the log table record to the index and lock the table, in this case, the table dml operation is also blocked temporarily.

The above briefly analyzes the two ways to create indexes through online rebuild and offline rebuild. In this case, offline rebuild is directly created based on the existing indexes, the creation method is index fast full scan and then sort index create, while online index separately uses table access scan Based on the existing table segment and then sort index create, during this period, a similar SYS_JOURNAL_56527 log table is created to record the dml Operation Records of the table during the creation period. After the creation, the log table records are updated to the new index, and delete the old index.

In general, the offline rebuild method is faster than the online rebuild method. Because the old index can be directly used for reconstruction, and the index is generally smaller than the table, compared with table access scan, index fast full scan has a lower cost, while online rebuild does not affect online dml.

In this article, there are indeed discrepancies between the actual execution plan of the 10053 trace in the offline rebuild index and the available cbo execution plan. You are welcome to understand this question, ainemo personally thinks that there is a problem in the execution plan that can be selected. I am interested in switching to oracle 11g to see if this problem has been fixed.

This article from: http://www.dbaxiaoyu.com, original address: http://www.dbaxiaoyu.com/archives/2346, thanks to the original author to share.