# create index .. online

## 前提

建立索引會產生排他鎖，造成寫資料鎖定，當建立索引的動作完成後，釋放鎖，資料才會繼續寫入。    
當我們要操作的表很大，建立索引需要很久，就會影響到生產，造成阻塞。
針對此問題，我們可以用online的方式，以非獨佔的方式建立和刪除索引。

### 試驗1 建立索引無online

開啟2個會話
```
--會話1，建立索引
create index i_SY_TRANSNO ON BASEUSER.TB_TRANS_RECORD(TRANS_NO) ;

--會話2，update操作
update BASEUSER.TB_TRANS_RECORD set TRANS_NO = '0000000005' where TRANS_NO = '0000000005';
```

檢視後臺有鎖

```
SQL> select * from v$lock where blocked = 1;

行號     ADDR                 TRX_ID               LTYPE  LMODE BLOCKED     TABLE_ID    ROW_IDX              TID                  IGN_FLAG    HLCK_EP    
---------- -------------------- -------------------- ------ ----- ----------- ----------- -------------------- -------------------- ----------- -----------
1          140111846187928      6092361              OBJECT IX    1           3497        0                    6092361              0           255

已用時間: 0.803(毫秒). 執行號:4905.
SQL> 
SQL> 
SQL> select  b.sess_id, b.SQL_TEXT, a.wait_for_id, wait_time ,b.*
from v$trxwait a 
left join v$sessions b on a.wait_for_id = b.TRX_ID;2   3

行號     SESS_ID              SQL_TEXT                                                           WAIT_FOR_ID          WAIT_TIME   SESS_ID              SESS_SEQ   
---------- -------------------- ------------------------------------------------------------------ -------------------- ----------- -------------------- -----------
           SQL_TEXT                                                           STATE  N_STMT      N_USED_STMT SEQ_NO      CURR_SCH USER_NAME TRX_ID               CREATE_TIME                CLNT_TYPE
           ------------------------------------------------------------------ ------ ----------- ----------- ----------- -------- --------- -------------------- -------------------------- ---------
           TIME_ZONE CHK_CONS CHK_IDENT RDONLY INS_NULL COMPILE_FLAG AUTO_CMT DDL_AUTOCMT RS_FOR_QRY CHK_NET ISO_LEVEL   CLNT_HOST       APPNAME     CLNT_IP           OSNAME     CONN_TYPE   
           --------- -------- --------- ------ -------- ------------ -------- ----------- ---------- ------- ----------- --------------- ----------- ----------------- ---------- ------------
           VPOOLADDR            RUN_STATUS MSG_STATUS LAST_RECV_TIME             LAST_SEND_TIME             DCP_FLAG THRD_ID     CONNECTED   PORT_TYPE   SRC_SITE    MAL_ID              
           -------------------- ---------- ---------- -------------------------- -------------------------- -------- ----------- ----------- ----------- ----------- --------------------
           CONCURRENT_FLAG CUR_LINENO  CUR_MTDNAME CUR_SQLSTR CLNT_VER
           --------------- ----------- ----------- ---------- --------
1          140093780136288      create index i_SY_TRANSNO ON BASEUSER.TB_TRANS_RECORD(TRANS_NO)  ; 6092360              19794       140093780136288      63
           create index i_SY_TRANSNO ON BASEUSER.TB_TRANS_RECORD(TRANS_NO)  ; ACTIVE 64          2           10          SYSDBA   SYSDBA    6092360              2022-02-09 10:23:05.000000 JDBC
           +08:00    N        N         N      Y        N            N        Y           N          N       1           DESKTOP-SMTBBG0 manager.exe ::ffff:9.7.130.17 Windows 10  HOMOGENEOUS
           140093780134384      RUNNING    RECIEVE    2022-02-09 10:35:00.000000 2022-02-09 10:34:04.000000 N        13520       1           0           65535       NULL
           0               1           NULL        NULL       8.1.2.18

```
普通建立索引的命令create index會先鎖表，然後再建立索引，如果表中資料量很大，會造成阻塞DML語句。

根據TRX_ID檢視後臺操作
```
--
SQL> select SESS_ID,TRX_ID,SQL_TEXT,STATE from v$sessions where state <> 'IDLE';

行號     SESS_ID              TRX_ID               SQL_TEXT                                                                                   STATE 
---------- -------------------- -------------------- ------------------------------------------------------------------------------------------ ------
1          140093713027424      6092355              select SESS_ID,TRX_ID,SQL_TEXT,STATE from v$sessions where state <> 'IDLE';                ACTIVE
2          140093780136288      6092360              create index i_SY_TRANSNO ON BASEUSER.TB_TRANS_RECORD(TRANS_NO)  ;                         ACTIVE
3          140093578809696      6092361              update BASEUSER.TB_TRANS_RECORD set TRANS_NO = '0000000005' where TRANS_NO = '0000000005'; ACTIVE

--檢視鎖資訊
SQL> select a.*, b.object_name  
from v$lock a, dba_objects b 
where a.table_id=b.object_id(+)
and a.trx_id = '6092360';2   3   4

行號     ADDR                 TRX_ID               LTYPE  LMODE BLOCKED     TABLE_ID    ROW_IDX              TID                  IGN_FLAG    HLCK_EP     OBJECT_NAME    
---------- -------------------- -------------------- ------ ----- ----------- ----------- -------------------- -------------------- ----------- ----------- ---------------
5          140109980205400      6092360              OBJECT IS    0           3497        0                    6092359              1           255         TB_TRANS_RECORD
6          140109980205688      6092360              OBJECT S     0           3497        0                    6092360              2           255         TB_TRANS_RECORD
7          140109980205544      6092360              OBJECT IX    0           3497        0                    6092360              0           255         TB_TRANS_RECORD

```
後臺只有TB_TRANS_RECORD表的相關資訊

### 試驗2 建立索引有online

開啟2個會話
```
--會話1，建立索引
create index i_SY_TRANSNO ON BASEUSER.TB_TRANS_RECORD(TRANS_NO) online;

--會話2，update操作
update BASEUSER.TB_TRANS_RECORD set TRANS_NO = '0000000005' where TRANS_NO = '0000000005';
```

檢視後臺沒有鎖

```
SQL> select * from v$lock where blocked = 1;
未選定行

SQL> select  b.sess_id, b.SQL_TEXT, a.wait_for_id, wait_time ,b.*
from v$trxwait a 
left join v$sessions b on a.wait_for_id = b.TRX_ID;
未選定行
```

根據TRX_ID檢視後臺操作
```
--
SQL> select SESS_ID,TRX_ID,SQL_TEXT,STATE from v$sessions where state <> 'IDLE';

行號     SESS_ID              TRX_ID               SQL_TEXT                                                                                   STATE 
---------- -------------------- -------------------- ------------------------------------------------------------------------------------------ ------
1          140094048571744      6092352              create index i_SY_TRANSNO ON BASEUSER.TB_TRANS_RECORD(TRANS_NO) ONLINE ;                   ACTIVE
2          140093847245152      6092354              update BASEUSER.TB_TRANS_RECORD set TRANS_NO = '0000000005' where TRANS_NO = '0000000005'; ACTIVE
3          140093713027424      6092355              select SESS_ID,TRX_ID,SQL_TEXT,STATE from v$sessions where state <> 'IDLE';                ACTIVE

--檢視鎖資訊
select a.*, b.object_name  
from v$lock a, dba_objects b 
where a.table_id=b.object_id(+)
and a.trx_id = '6092352';

行號     ADDR                 TRX_ID               LTYPE  LMODE BLOCKED     TABLE_ID    ROW_IDX              TID                  IGN_FLAG    HLCK_EP     OBJECT_NAME                   
---------- -------------------- -------------------- ------ ----- ----------- ----------- -------------------- -------------------- ----------- ----------- ------------------------------
6          140110551168184      6092352              OBJECT IS    0           4542        0                    6092351              1           255         ASYN$INDEX_TABLE_4542_FOR_3497
7          140110551168616      6092352              OBJECT IS    0           3497        0                    6092351              1           255         TB_TRANS_RECORD
8          140110551168760      6092352              OBJECT IX    0           3497        0                    6092352              0           255         TB_TRANS_RECORD
```
可以看到建立索引的事務6092352，建立索引中會產生ASYN$INDEX_TABLE_4542_FOR_3497的臨時表，對錶共享鎖，可以對資料進行修改。

總結：  
1.create index 會阻塞其它會話修改索引欄位，直到索引建立結束；  
2.create index online 允許其它會話修改索引欄位，但如果修改索引欄位的會話沒有commit或是rollbak，則索引建立會被阻塞；  
3.online建立索引時會臨時建立一個臨時表，索引建立結束後刪除臨時表

DM 武漢達夢資料庫股份有限公司
24小時免費服務熱線：400 991 6599
達夢技術社群：https://eco.dameng.com

create index .. online

相關文章