SQL> select count(*) from test_idx;
COUNT(*)
----------
19087751
SQL> select segment_name,segment_type,bytes/1024/1024 as MB from user_segments where segment_name='TEST_IDX';
SEGMENT_NAME SEGMENT_TYPE MB
------------------------------ ------------------ ----------
TEST_IDX TABLE 2176
SQL> select username,default_tablespace from dba_users where username='SCOTT';
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
SCOTT USERS
SQL> select index_name,tablespace_name from user_indexes where index_name='TEST_IDX_IDX1';
INDEX_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TEST_IDX_IDX1 USERS
SQL> SQL> SQL> SQL> select * from test_idx where object_name='I_USER1' AND object_id=46;
263 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4118933773
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 263 | 54441 | 267 (0)| 00:00:04 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_IDX | 263 | 54441 | 267 (0)| 00:00:04 |
|* 2 | INDEX RANGE SCAN | TEST_IDX_IDX1 | 263 | | 5 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=46 AND "OBJECT_NAME"='I_USER1')
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
11 recursive calls
0 db block gets
517 consistent gets
260 physical reads
9304 redo size
24860 bytes sent via SQL*Net to client
606 bytes received via SQL*Net from client
19 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
263 rows processed
rebuild 索引；
SQL> select * from v$mystat where rownum<2;
SID STATISTIC# VALUE
---------- ---------- ----------
34 0 0
SQL>
alter index TEST_IDX_IDX1 rebuild tablespace example;
SQL> select * from v$mystat where rownum<2;
SID STATISTIC# VALUE
---------- ---------- ----------
58 0 0
SQL> insert into test_idx(object_name) values('USERS');
此时SESSION 被堵塞
SQL> select * from v$lock where type in ('TM','TX');
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
0056BAB0 0056BAE0 34 TM 74914 0 4 0 210 1
0056BAB0 0056BAE0 58 TM 74914 0 0 3 107 0
0056BAB0 0056BAE0 59 TM 5003 0 3 0 379 0
2C361C24 2C361C64 59 TX 131081 1331 6 0 378 0
SQL> select object_name from dba_objects where object_id=74914;
OBJECT_NAME
-------------------------------------------------------------------
TEST_IDX
索引仍旧能正常使用:
SQL>
SQL> select * from test_idx where object_name='I_USER1' AND object_id=46;
263 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4118933773
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 263 | 54441 | 267 (0)| 00:00:04 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_IDX | 263 | 54441 | 267 (0)| 00:00:04 |
|* 2 | INDEX RANGE SCAN | TEST_IDX_IDX1 | 263 | | 5 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=46 AND "OBJECT_NAME"='I_USER1')
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
185 recursive calls
0 db block gets
414 consistent gets
0 physical reads
0 redo size
24860 bytes sent via SQL*Net to client
606 bytes received via SQL*Net from client
19 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
263 rows processed
那么插入insert into test_idx(object_name,object_id) values('USERS',100);
也被堵塞
插入insert into test_idx(owner) values('USERS');
也被堵住
原因重建索引的时候会对表加LMODE=4的锁,会给表加上4 - share (S) 会阻止其他SESSION 对表的一切DML操作。
在线重建呢？
SQL> select * from v$mystat where rownum<2;
SID STATISTIC# VALUE
---------- ---------- ----------
55 0 0
SQL> alter index TEST_IDX_IDX1 rebuild tablespace USERS online;
SQL> select * from v$lock where type in ('TM','TX');
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
00A94A48 00A94A78 55 TM 74914 0 2 0 9 0
00A94A48 00A94A78 55 TM 74918 0 4 0 4 0
2B834F74 2B834FB4 55 TX 65552 997 6 0 4 0
SQL> select object_name from dba_objects where object_Id in (74914,74918);
OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
TEST_IDX
SYS_JOURNAL_74915
把LMODE=4加在了SYS_JOURNAL_74915上
rebulid online的是受会产生一个SYS_JOURNAL_74915的IOT类型的系统临时表,所有rebuild online的索引变化都记录在这个表中,当新的索引创建完后,把这个表的记录
维护到索引中
此时DML有影响吗？
SQL> insert into test_idx(object_name,object_id) values('USERS',100);
1 row created.
SQL> commit;
Commit complete.
此时DML没有影响
SQL> select * from test_idx where object_name='CZCB' and object_id=9999;
no rows selected
SQL>
insert into test_idx(object_name,object_id) values('CZCB','9999');SQL>
1 row created.
SQL> commit;
Commit complete.
SQL> set linesize 200
SQL> set pagesixze 200
SP2-0158: unknown SET option "pagesixze"
SQL> set pagesize 200
SQL> set autot trace *
SQL> select * from test_idx where object_name='CZCB' and object_id=9999;
Execution Plan
----------------------------------------------------------
Plan hash value: 4118933773
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 207 | 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_IDX | 1 | 207 | 5 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST_IDX_IDX1 | 1 | | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=9999 AND "OBJECT_NAME"='CZCB')
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
109 consistent gets
0 physical reads
80 redo size
1329 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
之前object_name='CZCB' and object_id=9999;记录不存在索引中,为什么新插入的还能走索引呢？
重建索引完成后:
SQL> SQL> SQL> SQL> SQL> SQL> select object_name from dba_objects where object_Id in (74914,74918);
OBJECT_NAME
--------------------------------------------------------------------------------
TEST_IDX
SQL> set linesize 200
SQL> set pagesize 200
SQL> select object_name,object_id from test_idx where object_name='AOBAMCCCBBBBCA' and object_id=8888;
no rows selected
SQL> insert into test_idx(object_name,object_id) values('AOBAMCCCBBBBCA','8888');
1 row created.
SQL> commit;
Commit complete.
SQL> select object_name,object_id from test_idx where object_name='AOBAMCCCBBBBCA' and object_id=8888;
OBJECT_NAME OBJECT_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------
AOBAMCCCBBBBCA 8888
SQL> set linesize 200
SQL> set pagesixe 200
SP2-0158: unknown SET option "pagesixe"
SQL> set pagesize 200
SQL> set autot trace
SQL> select object_name,object_id from test_idx where object_name='AOBAMCCCBBBBCA' and object_id=8888;
Execution Plan
----------------------------------------------------------
Plan hash value: 644446973
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 79 | 4 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| TEST_IDX_IDX1 | 1 | 79 | 4 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_ID"=8888 AND "OBJECT_NAME"='AOBAMCCCBBBBCA')
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
504 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
在线重建索引过程中,插入新的数据(之前表和索引中不存在的),都可以直接从索引返回,Oracle你也太牛逼了。
在线重建索引期间索引仍旧可用,不影响DML操作