今天在网上看了一篇关于12C新特性-自动维护全局索引的帖子，经测试，貌似根本不是那么回事呀。如果对分区表进行分区DDL操作，如果不加update index字句，全局索引就会失效，下面先看一下是11.2.0.4.0版本的测试，操作系统OEL 5.7。
创建测试用户。
SQL> create user dbdream identified by dbdream;
User created.
SQL> grant dba to dbdream;
Grant succeeded.
SQL> conn dbdream/dbdream
Connected.
创建测试表及测试数据。
SQL> create table t_p (id number,name varchar2(20),t_date date)
2 partition by range(t_date)
3 interval (numtodsinterval(1,'day'))
4 (partition p0 values less than (to_date('2014-09-01','yyyy-mm-dd')))
5 ;
Table created.
SQL> INSERT INTO T_P VALUES(1,'DBA',SYSDATE-1);
1 row created.
SQL> insert into t_p values(2,'STREAM',SYSDATE);
1 row created.
SQL> insert into t_p values(3,'DBDREAM',SYSDATE+1);
1 row created.
SQL> insert into t_p values(4,'STREAMSONG',SYSDATE+2);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> select * from t_p;
ID NAME T_DATE
---------- -------------------- ---------
1 DBA 31-AUG-14
2 STREAM 01-SEP-14
3 DBDREAM 02-SEP-14
4 STREAMSONG 03-SEP-14
SQL> SELECT TABLE_NAME,PARTITION_NAME FROM USER_TAB_PARTITIONS;
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
T_P P0
T_P SYS_P41
T_P SYS_P42
T_P SYS_P43
可见目前数据存在4个分区内，每个分区一条记录，下面创建一个全局索引。
SQL> create index ind_t_p on t_p(id) global;
Index created.
下面truncate调一个分区。
SQL> alter table t_p truncate partition p0;
Table truncated.
这时候在通过索引查询数据，就会报错，因为全局索引失效了。
SQL> select /*+ index (t ind_t_p) */ * from t_p t where id=1;
select /*+ index (t ind_t_p) */ * from t_p t where id=1
*
ERROR at line 1:
ORA-01502: index 'DBDREAM.IND_T_P' or partition of such index is in unusable
state
这没有问题，下面看看12C有什么改善。实验环境12.0.1.2.0，OEL 5.7。
测试过程和11g一模一样，创建测试用户。
SQL> conn sys/oracle@localhost/stream as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> show con_name
CON_NAME
------------------------------
STREAM
SQL> create user dbdream identified by dbdream;
User created.
SQL> grant dba to dbdream;
Grant succeeded.
SQL> conn dbdream/dbdream@localhost/stream
Connected.
SQL> show user
USER is "DBDREAM"
SQL> show con_name
CON_NAME
------------------------------
STREAM
创建测试表及测试数据。
SQL> create table t_p (id number,name varchar2(20),t_date date)
2 partition by range(t_date)
3 interval (numtodsinterval(1,'day'))
4 (partition p0 values less than (to_date('2014-09-01','yyyy-mm-dd')))
5 ;
Table created.
SQL> INSERT INTO T_P VALUES(1,'DBA',SYSDATE-1);
1 row created.
SQL> insert into t_p values(2,'STREAM',SYSDATE);
1 row created.
SQL> insert into t_p values(3,'DBDREAM',SYSDATE+1);
1 row created.
SQL> insert into t_p values(4,'STREAMSONG',SYSDATE+2);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> select * from t_p;
ID NAME T_DATE
---------- -------------------- ------------
1 DBA 31-AUG-14
2 STREAM 01-SEP-14
3 DBDREAM 02-SEP-14
4 STREAMSONG 03-SEP-14
SQL> SELECT TABLE_NAME,PARTITION_NAME FROM USER_TAB_PARTITIONS;
TABLE_NAME PARTITION_NAME
---------- ---------------
T_P P0
T_P SYS_P281
T_P SYS_P282
T_P SYS_P283
创建全局索引。
SQL> create index ind_t_p on t_p(id) global;
Index created.
truncate掉一个分区。
SQL> alter table t_p truncate partition p0;
Table truncated.
如果12C版本会自动维护分区索引的话，使用索引查询应该不会有问题，可是经过本人测试，问题出现了。
SQL> select /*+ index (t ind_t_p) */ * from t_p t where id=1;
select /*+ index (t ind_t_p) */ * from t_p t where id=1
*
ERROR at line 1:
ORA-01502: index 'DBDREAM.IND_T_P' or partition of such index is in unusable
state
不知道是我测试的问题还是12C的这个新特性在R2版本才开始推出，R1版本还不支持呢？欢迎大家纠正。
今天通读官档相关的内容，发现根本就不是这么回事，被误导了，官档写的很明确，当drop和truncate分区时，一定要加update index字句，或者rebuild索引，否则全局索引将不可用。
By default, many table maintenance operations on partitioned tables invalidate (mark UNUSABLE) the corresponding indexes or index partitions. You must then rebuild the entire index or, for a global index, each of its partitions. The database lets you override this default behavior if you specify UPDATE INDEXES in your ALTER TABLE statement for the maintenance operation. Specifying this clause tells the database to update the indexes at the time it executes the maintenance operation DDL statement. This provides the following benefits:
ORACLE的意思应该是说当truncate分区是指定update indexes字句的时候，不会立即重建索引，而是只修改索引的元数据，将被删掉的数据对应的索引打个标记，使用索引的时候不访问这一部分，不知道我理解的是否正确。
The partition maintenance operations DROP PARTITION and TRUNCATE PARTITION are optimized by making the index maintenance for metadata only.
ORACLE通过PMO_DEFERRED_GIDX_MAINT_JOB作业每晚2点再更新这些索引，ORACLE号称这样的做的好处是将更新索引这样的大操作放到非业务高峰区去做，而不是业务高峰区的时候做。下面来验证一下是不是这么回事。
创建测试表。
SQL> create table t_p(id number,text varchar2(20))
2 partition by hash(id)
3 (partition p1,
4 partition p2);
Table created.
插入20万行记录。
SQL> begin
2 for i in 1..200000 loop
3 insert into t_p values(i,'XXXXXXXXXXXXXXXXXXXX');
4 END LOOP;
5 COMMIT;
6 END;
7 /
PL/SQL procedure successfully completed.
SQL> select count(*) from t_p partition(p1);
COUNT(*)
----------
100142
SQL> select count(*) from t_p partition(p2);
COUNT(*)
----------
99858
数据还算分布均匀，创建全局索引。
SQL> create index ind_t_p on t_p(id) global;
Index created.
记录索引及表大小。
SQL> select segment_name,partition_name,bytes/1024/1024 from user_segments;
SEGMENT_NAME PARTITION_NAME BYTES/1024/1024
-------------------- -------------------- ---------------
IND_T_P 5
T_P P1 8
T_P P2 8
truncate第一个分区，并加上update indexes字句。
SQL> alter table t_p truncate partition p1 update indexes;
Table truncated.
此时，索引可用，再查看索引和表的大小。
SQL> select status from user_indexes;
STATUS
--------
VALID
SQL> select /*+ index (t ind_t_p) */ * from t_p t where id=2;
ID NAME T_DATE
---------- -------------------- ------------
2 STREAM 01-SEP-14
SQL> select segment_name,partition_name,bytes/1024/1024 from user_segments;
SEGMENT_NAME PARTITION_NAME BYTES/1024/1024
-------------------- -------------------- ---------------
IND_T_P 5
T_P P1 8
T_P P2 8
可见，索引大小没有变化，P1分区的段大小也没有变化，重建索引，再次查看。
SQL> alter index ind_t_p rebuild;
Index altered.
SQL> select segment_name,partition_name,bytes/1024/1024 from user_segments;
SEGMENT_NAME PARTITION_NAME BYTES/1024/1024
-------------------- -------------------- ---------------
IND_T_P 3
T_P P1 8
T_P P2 8
可见，索引大小变成了3M，这说明在truncate分区时，如果加上update indexes字句，在12C版本，ORACLE并不会立即更新索引，而是只修改了索引元数据，这样做的好处是不需要等待索引更新完成，很快就会truncate结束。ORACLE调用PMO_DEFERRED_GIDX_MAINT_JOB作业每晚2点再更新这些索引。
至于P1分区被truncate后为啥大小没变还是8M，是由于隐含参数_partition_large_extents所控制的，这个参数在11g版本引入，新建的分区默认会初始化8M的空间，在12C版本，这个参数默认也是启用的。
SQL> select
2 x.ksppinm name,
3 y.ksppstvl value,
4 y.ksppstdf isdefault
5 from
6 sys.x$ksppi x,
7 sys.x$ksppcv y
8 where
9 x.inst_id = userenv('Instance') and
10 y.inst_id = userenv('Instance') and
11 x.indx = y.indx and
12 x.ksppinm ='_partition_large_extents'
13 order by
14 translate(x.ksppinm, ' _', ' ')
15 /
NAME VALUE ISDEFAULT
------------------------- ---------- ---------
_partition_large_extents TRUE TRUE
下面可以验证下分区表的分区默认是否分配8M的空间，建一个空的分区表。
SQL> create table t_p_1(id number,text varchar2(20))
2 partition by hash(id)
3 (partition p1,
4 partition p2);
Table created.
由于12C也沿用了11g的延迟段创建特性，现在查看不到这个新建分区表的段信息，向分区表插入两条数据后回滚，就会分配段信息，查看段大小。
SQL> begin
2 for i in 1..2 loop
3 insert into t_p_1 values(i,'XXXXXXX');
4 end loop;
5 rollback;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> select segment_name,partition_name,bytes/1024/1024 from user_segments;
SEGMENT_NA PARTITION_NAME BYTES/1024/1024
---------- -------------------- ---------------
IND_T_P 3
T_P P1 8
T_P P2 8
T_P_1 P1 8
T_P_1 P2 8
初始段大小就是8M，本实验数据量还是太小，每个分区的数据都没超过8M，所以才会遇到这么奇葩的现象。
975
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
