11G在线重建索引

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操作