ORACLE 12C新特性-自动维护全局索引--更新版

发表于 2014-9-1 18:17:30

马上注册，结交更多数据大咖，获取更多知识干货，轻松玩转大数据

您需要登录才可以下载或查看，没有帐号？立即注册

x

本帖最后由 dbdream 于 2014-9-2 19:14 编辑

今天在网上看了一篇关于12C新特性-自动维护全局索引的帖子，经测试，貌似根本不是那么回事呀。如果对分区表进行分区DDL操作，如果不加update index字句，全局索引就会失效，下面先看一下是11.2.0.4.0版本的测试，操作系统OEL 5.7。

创建测试用户。

[AppleScript] 纯文本查看 复制代码

SQL> create user dbdream identified by dbdream;

User created.

SQL> grant dba to dbdream;

Grant succeeded.

SQL> conn dbdream/dbdream
Connected.

创建测试表及测试数据。

[AppleScript] 纯文本查看 复制代码

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个分区内，每个分区一条记录，下面创建一个全局索引。

[AppleScript] 纯文本查看 复制代码

SQL> create index ind_t_p on t_p(id) global;

Index created.

下面truncate调一个分区。

[AppleScript] 纯文本查看 复制代码

SQL> alter table t_p truncate partition p0;

Table truncated.

这时候在通过索引查询数据，就会报错，因为全局索引失效了。

[AppleScript] 纯文本查看 复制代码

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一模一样，创建测试用户。

[AppleScript] 纯文本查看 复制代码

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

创建测试表及测试数据。

[AppleScript] 纯文本查看 复制代码

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

创建全局索引。

[AppleScript] 纯文本查看 复制代码

SQL> create index ind_t_p on t_p(id) global;

Index created.

truncate掉一个分区。

[AppleScript] 纯文本查看 复制代码

SQL> alter table t_p truncate partition p0;

Table truncated.

如果12C版本会自动维护分区索引的话，使用索引查询应该不会有问题，可是经过本人测试，问题出现了。

[AppleScript] 纯文本查看 复制代码

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索引，否则全局索引将不可用。

[AppleScript] 纯文本查看 复制代码

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字句的时候，不会立即重建索引，而是只修改索引的元数据，将被删掉的数据对应的索引打个标记，使用索引的时候不访问这一部分，不知道我理解的是否正确。

[AppleScript] 纯文本查看 复制代码

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号称这样的做的好处是将更新索引这样的大操作放到非业务高峰区去做，而不是业务高峰区的时候做。下面来验证一下是不是这么回事。

创建测试表。

[AppleScript] 纯文本查看 复制代码

SQL> create table t_p(id number,text varchar2(20))
  2  partition by hash(id)
  3  (partition p1, 
  4  partition p2);

Table created.

插入20万行记录。

[AppleScript] 纯文本查看 复制代码

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

数据还算分布均匀，创建全局索引。

[AppleScript] 纯文本查看 复制代码

SQL> create index ind_t_p on t_p(id) global;

Index created.

记录索引及表大小。

[AppleScript] 纯文本查看 复制代码

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第一个分区，并加上updateindexes字句。

[AppleScript] 纯文本查看 复制代码

SQL> alter table t_p truncate partition p1 update indexes;

Table truncated.

此时，索引可用，再查看索引和表的大小。

[AppleScript] 纯文本查看 复制代码

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分区的段大小也没有变化，重建索引，再次查看。

[AppleScript] 纯文本查看 复制代码

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版本，这个参数默认也是启用的。

[AppleScript] 纯文本查看 复制代码

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的空间，建一个空的分区表。

[AppleScript] 纯文本查看 复制代码

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的延迟段创建特性，现在查看不到这个新建分区表的段信息，向分区表插入两条数据后回滚，就会分配段信息，查看段大小。

[AppleScript] 纯文本查看 复制代码

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，所以才会遇到这么奇葩的现象。

来自群组: Oracle DBA交流

发表于 2014-9-2 19:14:56

实验才是最好的老师呀，ORACLE的官档想看明白在说什么真难！

发表于 2014-9-4 18:03:25

唉，都是只看不回复的主，就没人提点建议啥的吗?

帐号		自动登录	找回密码
密码			立即注册

ORACLE 12C新特性-自动维护全局索引--更新版

马上注册，结交更多数据大咖，获取更多知识干货，轻松玩转大数据

站长推荐 /1