首页 » ORACLE, ORACLE [C]系列 » Oracle 12c New Feature: Partition增强(三)Partial Indexes(部分索引)

Oracle 12c New Feature: Partition增强(三)Partial Indexes(部分索引)

在12c之前没办法在部分或指定的分区上创建索引,在12c 版本中引入了Partial Indexes(部分索引), 无论是global还是local都可以有选择性的对部分分区创建索引,这可能是在12c分区新特性中最出色的一个. 如一个分区表保留的周期久导致表段非常大, 且上面的索引之前必须是全部数据上的分区,那样索引也就占用了大量的空间,如果平时的数据检索只在最近的分区上使用索引, 那索引的数据空间就存在部分的浪费; 或者是在部分分区数据加载时为了加载速度临时禁用指定分区上的索引维护,同时不影响其它分区的查询,这种情况可能partial index就非常适合.

partial index有两个限制条件:
1, 表是分区表
该特性是分区表相关, 所以这个条件很正常, 当然也可以是sub partition级. 对于非分区表在部分数据上创建索引有其它方法也可以实现, 如利用index不会记录全null的KEY条目,可以创建一个函数索引,不需要的数据转换为null, 详细不在本文的描述范围.

2, 不能是唯一索引
主键,唯一约束,唯一索引 是依赖索引key的唯一性保证数据的唯一, 当然不能只在部分数据上索引保证所有数据唯一. 如果尝试出提示下面的错误:ORA-14226: unique index may not be PARTIAL

— demo —
# 创建分区表

SQL> create table ANBOB_T3
    (id number, name varchar2(30), years number)
    INDEXING OFF
    partition by range (years) (
    partition P2010 values less than (2011),
    partition P2011 values less than (2012) INDEXING OFF,
    partition P2012 values less than (2013) INDEXING OFF,
    partition P2013 values less than (2014),
    partition P2014 values less than (2015),
    partition P2015 values less than (2016) INDEXING ON,
    partition P2016 values less than (2017) INDEXING ON,
    partition Pmax values less than (maxvalue) INDEXING ON );

Table created.

SQL> insert into anbob_t3
  2  with c as(
  3  select rownum r from dual connect by rownum<=1e3
  4  ),
  5  t as (
  6  select rownum-1 xr from xmltable('1 to 7')
  7  )
  8  select r, 'anbob'||rownum, 2010+xr from c,t;
7000 rows created.

SQL> @tabpart12 anbob_t3
TABLE_OWNE TABLE_NAME     POS COM PARTITION_NAME   NUM_ROWS  HIGH_VALUE_RAW       COMPRESS INDE INMEMORY
---------- ------------ ----- --- -------------- ----------  -------------------- -------- ---- --------
ANBOB      ANBOB_T3         1 NO  P2010                1000  2011                 DISABLED OFF  DISABLED
ANBOB      ANBOB_T3         2 NO  P2011                1000  2012                 DISABLED OFF  DISABLED
ANBOB      ANBOB_T3         3 NO  P2012                1000  2013                 DISABLED OFF  DISABLED
ANBOB      ANBOB_T3         4 NO  P2013                1000  2014                 DISABLED OFF  DISABLED
ANBOB      ANBOB_T3         5 NO  P2014                1000  2015                 DISABLED OFF  DISABLED
ANBOB      ANBOB_T3         6 NO  P2015                1000  2016                 DISABLED ON   DISABLED
ANBOB      ANBOB_T3         7 NO  P2016                1000  2017                 DISABLED ON   DISABLED
ANBOB      ANBOB_T3         8 NO  PMAX                    0  MAXVALUE             DISABLED ON   DISABLED

NOTE:
Create table属性indexing的默认值是ON; partition 或sub partition如果没有指定indexing是继承上级属性.
上面创建了表,在表级indexing off, 只有P2015,P2016,MAXVALUE分区显示指定了indexing on.像p2010就是继承了表级的indexing属性.

tip:为了演示索引特性, 请忽略索引是否合理

# 创建global索引

SQL> create index idx_t3_id on anbob_t3(id);   -- no indexing
Index created

SQL> @ind12 idx_t3_id
Display indexes where table or index name matches idx_t3_id...
TABLE_OWNER   TABLE_NAME    INDEX_NAME   POS# COLUMN_NAME   DSC
------------- ------------- ------------ ---- ------------- ----
ANBOB         ANBOB_T3      IDX_T3_ID       1 ID

INDEX_OWNER   TABLE_NAME    INDEX_NAME   IDXTYPE    UNIQ STATUS   PART TEMP  H     LFBLKS    NDK   NUM_ROWS INDEXIN ORP
------------- ------------- ------------ ---------- ---- -------- ---- ---- -- ---------- ------ ---------- ------- ---
ANBOB         ANBOB_T3      IDX_T3_ID    NORMAL     NO   VALID    NO   N     2         19   1000       7000 FULL    NO

SQL> drop index idx_t3_id;
Index dropped.

SQL> create index idx_t3_id1 on anbob_t3(id) GLOBAL INDEXING FULL;
Index created.

SQL> @ind12 idx_t3_id1
Display indexes where table or index name matches idx_t3_id1...
TABLE_OWNER TABLE_NAME INDEX_NAME  POS# COLUMN_NAME   DSC
----------- ---------- ----------- ---- ------------- ----
ANBOB       ANBOB_T3   IDX_T3_ID1     1 ID


INDEX_OWNER TABLE_NAME INDEX_NAME  IDXTYPE    UNIQ STATUS   PART TEMP  H  LFBLKS   NDK   NUM_ROWS INDEXIN ORP
----------- ---------- ----------- ---------- ---- -------- ---- ---- -- ------- ----- ---------- ------- ---
ANBOB       ANBOB_T3   IDX_T3_ID1  NORMAL     NO   VALID    NO   N     2      19  1000       7000 FULL    NO

SQL> create index idx_t3_id2 on anbob_t3(id) GLOBAL INDEXING PARTIAL;
Index created.

SQL> @ind12 idx_t3_id2
Display indexes where table or index name matches idx_t3_id2...
TABLE_OWNER TABLE_NAME INDEX_NAME  POS# COLUMN_NAME    DSC
----------- ---------- ----------- ---- -------------- ----
ANBOB       ANBOB_T3   IDX_T3_ID2     1 ID

INDEX_OWNER TABLE_NAME INDEX_NAME  IDXTYPE    UNIQ STATUS   PART TEMP  H  LFBLKS   NDK   NUM_ROWS  INDEXIN ORP
----------- ---------- ----------- ---------- ---- -------- ---- ---- -- ------- ----- ----------  ------- ---
ANBOB       ANBOB_T3   IDX_T3_ID2  NORMAL     NO   VALID    NO   N     2       6  1000       2000  PARTIAL NO

NOTE:
Create Index 引入了INDEXING ON|OFF|PARTIAL|FULL值, INDEXING FULL是默认值. 上面创建了global index full和index partial 可以看到部分索引的index leaf block和number of rows都明显减少.

# 创建LOCAL索引

SQL> create index idx_t3_id3 on anbob_t3(id) local ; --no indexing
Index created.

SQL> @ind12 idx_t3_id3
Display indexes where table or index name matches idx_t3_id3...

TABLE_OWNER TABLE_NAME INDEX_NAME  POS# COLUMN_NAME   DSC
----------- ---------- ----------- ---- ------------- ----
ANBOB       ANBOB_T3   IDX_T3_ID3     1 ID

INDEX_OWNER TABLE_NAME INDEX_NAME  IDXTYPE    UNIQ STATUS   PART TEMP  H     LFBLKS  NDK   NUM_ROWS  INDEXIN ORP
----------- ---------- ----------- ---------- ---- -------- ---- ---- -- ---------- ---- ----------  ------- ---
ANBOB       ANBOB_T3   IDX_T3_ID3  NORMAL     NO   N/A      YES  N     2         21 1000       7000  FULL    NO

SQL> drop index idx_t3_id3;
Index dropped.

SQL> create index idx_t3_id3 on anbob_t3(id) local INDEXING PARTIAL;
Index created.

SQL> @ind12 idx_t3_id3
Display indexes where table or index name matches idx_t3_id3...
TABLE_OWNER TABLE_NAME  INDEX_NAME    POS# COLUMN_NAME   DSC
----------- ----------- ------------- ---- ------------- ----
ANBOB       ANBOB_T3    IDX_T3_ID3       1 ID

INDEX_OWNER TABLE_NAME  INDEX_NAME    IDXTYPE    UNIQ STATUS   PART TEMP  H LFBLKS  NDK   NUM_ROWS   INDEXIN ORP
----------- ----------- ------------- ---------- ---- -------- ---- ---- -- ------ ---- ---------- - ------- ---
ANBOB       ANBOB_T3    IDX_T3_ID3    NORMAL     NO   N/A      YES  N     2      6 1000       2000   PARTIAL NO

Note:
上面创建了local index full和index partial 可以看到部分索引的index leaf blocks和number of rows都明显减少.

# 测试分区维护对索引的影响

SQL> create index IDX_T3_NAME_G on anbob_t3(name) GLOBAL INDEXING PARTIAL;
Index created.

SQL> @ind12 IDX_T3_NAME_G
Display indexes where table or index name matches IDX_T3_NAME_G...
TABLE_OWNER  TABLE_NAME  INDEX_NAME   POS# COLUMN_NAME     DSC
------------ ----------- ------------ ---- --------------- ----
ANBOB        ANBOB_T3    IDX_T3_NAME_G     1 NAME

INDEX_OWNER  TABLE_NAME  INDEX_NAME   IDXTYPE    UNIQ STATUS   PART TEMP  H     LFBLKS  VISIBILIT INDEXIN ORP
------------ ----------- ------------ ---------- ---- -------- ---- ---- -- ----------  --------- ------- ---
ANBOB        ANBOB_T3    IDX_T3_NAME_G  NORMAL     NO   VALID    NO   N     2          7  VISIBLE   PARTIAL NO
 
SQL> create index IDX_T3_NAME_L on anbob_t3(name) local INDEXING PARTIAL invisible;
Index created.

SQL> @ind12 IDX_T3_NAME_L
Display indexes where table or index name matches IDX_T3_NAME_L...
TABLE_OWNER TABLE_NAME   INDEX_NAME     POS# COLUMN_NAME     DSC
----------- ------------ -------------- ---- --------------- ----
ANBOB       ANBOB_T3     IDX_T3_NAME_L      1 NAME

INDEX_OWNER TABLE_NAME   INDEX_NAME     IDXTYPE    UNIQ STATUS   PART TEMP  H     LFBLKS  VISIBILIT INDEXIN ORP
----------- ------------ -------------- ---------- ---- -------- ---- ---- -- ----------  --------- ------- ---
ANBOB       ANBOB_T3     IDX_T3_NAME_L   NORMAL     NO   N/A      YES  N     2          6  INVISIBLE PARTIAL NO

Note:
我使用的ind12.sql源自dba_tab_partitions视图,该视图在12c增加了indexing字段,显示indexing属性.还有dba_part_tables视图也有def_indexing列,上面我创建了两个索引Global\Local在相同的name列上, 这同样又是12c的一个新特性, 可以创建多个索引在相同列.

SQL>  select table_name, def_indexing
    from user_part_tables
    where table_name='ANBOB_T3';

TABLE_NAME           DEF
-------------------- ---
ANBOB_T3             OFF

# 修改表级indexing属性
SQL> alter table ANBOB_T3 modify default attributes indexing on;
Table altered.

SQL>  select table_name, def_indexing
  2      from user_part_tables
  3      where table_name='ANBOB_T3';
TABLE_NAME           DEF
-------------------- ---
ANBOB_T3             ON

# 增加分区

SQL> alter table  ANBOB_T3 split partition PMAX  at(2018)   into (partition p2017,partition PMAX  )
  2  ;
Table altered.
	
SQL> insert into ANBOB_T3 values(1,'anbob',2017);
1 row created.

SQL> commit;
Commit complete.

SQL> @tabpart12 ANBOB_T3

TABLE_OWNE TABLE_NAME     POS COM PARTITION_NAME    NUM_ROWS  HIGH_VALUE_RAW  COMPRESS INDE INMEMORY
---------- ------------  ---- --- --------------- ----------  --------------- -------- ---- --------
ANBOB      ANBOB_T3         1 NO  P2010                 1000  2011            DISABLED OFF  DISABLED
ANBOB                       2 NO  P2011                 1000  2012            DISABLED OFF  DISABLED
ANBOB                       3 NO  P2012                 1000  2013            DISABLED OFF  DISABLED
ANBOB                       4 NO  P2013                 1000  2014            DISABLED OFF  DISABLED
ANBOB                       5 NO  P2014                 1000  2015            DISABLED OFF  DISABLED
ANBOB                       6 NO  P2015                 1000  2016            DISABLED ON   DISABLED
ANBOB                       7 NO  P2016                 1000  2017            DISABLED ON   DISABLED
ANBOB                       8 NO  P2017                    1  2018            DISABLED ON   DISABLED
ANBOB                       9 NO  PMAX                     0  MAXVALUE        DISABLED ON   DISABLED

9 rows selected.

# 修改已存在分区的indexing为off

SQL> alter table ANBOB_T3 modify partition P2016 indexing off;
Table altered.

SQL> @tabpart12 ANBOB_T3

TABLE_OWNE TABLE_NAME   POS COM PARTITION_NAME NUM_ROWS HIGH_VALUE_RAW       COMPRESS INDE INMEMORY
---------- ----------- ---- --- -------------- -------- -------------------- -------- ---- --------
ANBOB      ANBOB_T3       1 NO  P2010              1000 2011                 DISABLED OFF  DISABLED
ANBOB                     2 NO  P2011              1000 2012                 DISABLED OFF  DISABLED
ANBOB                     3 NO  P2012              1000 2013                 DISABLED OFF  DISABLED
ANBOB                     4 NO  P2013              1000 2014                 DISABLED OFF  DISABLED
ANBOB                     5 NO  P2014              1000 2015                 DISABLED OFF  DISABLED
ANBOB                     6 NO  P2015              1000 2016                 DISABLED ON   DISABLED
ANBOB                     7 NO  P2016              1000 2017                 DISABLED OFF  DISABLED
ANBOB                     8 NO  P2017                 1 2018                 DISABLED ON   DISABLED
ANBOB                     9 NO  PMAX                  0 MAXVALUE             DISABLED ON   DISABLED

col index_name for a20
SQL> col index_name for a20
SQL> select index_name,PARTITIONED,status,ORPHANED_ENTRIES,indexing 
   from user_indexes 
   where index_name in('IDX_T3_NAME_G','IDX_T3_NAME_L');

INDEX_NAME           PAR STATUS   ORP INDEXIN
-------------------- --- -------- --- -------
IDX_T3_NAME_L         YES N/A      NO  PARTIAL
IDX_T3_NAME_G         NO  VALID    YES PARTIAL

2 rows selected.

Note:
禁用分区的indexing, 其它分区或global索引不会失效, 默认12c 的global 索引会使用异步维护, 这个在第一篇日志中有记录,ORPHANED_ENTRIES列是否包含游离KEY? 如果不手动清理, 默认使用的是SYS.PMO_DEFERRED_GIDX_MAINT_JOB 作业调度每晚2点去执行.

# 修改已存在分区的indexing为ON

SQL> select index_name, partition_name, status
   from user_ind_partitions
   where index_name='IDX_T3_NAME_L'
   order by partition_position;

INDEX_NAME           PARTITION_NAME       STATUS
-------------------- -------------------- --------
IDX_T3_NAME_L         P2010                UNUSABLE
IDX_T3_NAME_L         P2011                UNUSABLE
IDX_T3_NAME_L         P2012                UNUSABLE
IDX_T3_NAME_L         P2013                UNUSABLE
IDX_T3_NAME_L         P2014                UNUSABLE
IDX_T3_NAME_L         P2015                USABLE
IDX_T3_NAME_L         P2016                UNUSABLE
IDX_T3_NAME_L         P2017                USABLE
IDX_T3_NAME_L         PMAX                 USABLE

SQL> alter table ANBOB_T3 modify partition P2013 indexing on;
Table altered.

SQL> select index_name, partition_name, status
        from user_ind_partitions
        where index_name='IDX_T3_NAME_L'
        order by partition_position;

INDEX_NAME           PARTITION_NAME       STATUS
-------------------- -------------------- --------
IDX_T3_NAME_L         P2010                UNUSABLE
IDX_T3_NAME_L         P2011                UNUSABLE
IDX_T3_NAME_L         P2012                UNUSABLE
IDX_T3_NAME_L         P2013                USABLE     ####
IDX_T3_NAME_L         P2014                UNUSABLE
IDX_T3_NAME_L         P2015                USABLE
IDX_T3_NAME_L         P2016                UNUSABLE
IDX_T3_NAME_L         P2017                USABLE
IDX_T3_NAME_L         PMAX                 USABLE

9 rows selected.

Note:
修改分区indexing on, 相应的索引条目也会自动创建.

# partial indexing 中索引相关的执行计划

SQL> select index_name, partition_name, status
  2          from user_ind_partitions
  3          where index_name='IDX_T3_ID3'
  4          order by partition_position;

INDEX_NAME           PARTITION_NAME       STATUS
-------------------- -------------------- --------
IDX_T3_ID3           P2010                UNUSABLE
IDX_T3_ID3           P2011                UNUSABLE
IDX_T3_ID3           P2012                UNUSABLE
IDX_T3_ID3           P2013                USABLE
IDX_T3_ID3           P2014                UNUSABLE
IDX_T3_ID3           P2015                USABLE
IDX_T3_ID3           P2016                UNUSABLE
IDX_T3_ID3           P2017                USABLE
IDX_T3_ID3           PMAX                 USABLE

# 分区上索引打开时
SQL> select * from anbob_t3 where id=1 and years=2013;

        ID NAME                                YEARS
---------- ------------------------------ ----------
         1 anbob4                               2013

1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1113911786
-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |            |     1 |    18 |     2   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE                    |            |     1 |    18 |     2   (0)| 00:00:01 |     4 |     4 |
|*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| ANBOB_T3   |     1 |    18 |     2   (0)| 00:00:01 |     4 |     4 |
|*  3 |    INDEX RANGE SCAN                        | IDX_T3_ID3 |     1 |       |     1   (0)| 00:00:01 |     4 |     4 |
-------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("YEARS"=2013)
   3 - access("ID"=1)

#分区上索引关闭时
SQL>  select * from anbob_t3 where id=1 and years=2014;

        ID NAME                                YEARS
---------- ------------------------------ ----------
         1 anbob5                               2014

1 row selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2459417556
---------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |     1 |    18 |   206   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|          |     1 |    18 |   206   (0)| 00:00:01 |     5 |     5 |
|*  2 |   TABLE ACCESS FULL    | ANBOB_T3 |     1 |    18 |   206   (0)| 00:00:01 |     5 |     5 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("ID"=1 AND "YEARS"=2014)

#分区范围中索引混合时(有开有关)

SQL> select * from anbob_t3 where id=1 and years between 2013 and 2015;

        ID NAME                                YEARS
---------- ------------------------------ ----------
         1 anbob4                               2013
         1 anbob6                               2015
         1 anbob5                               2014
3 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3512407975

----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                      | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                               |                 |     4 |    72 |   210   (0)| 00:00:01 |       |       |
|   1 |  VIEW                                          | VW_TE_2         |     3 |   129 |   210   (0)| 00:00:01 |       |       |
|   2 |   UNION-ALL                                    |                 |       |       |            |          |       |       |
|   3 |    VIEW                                        | VW_ORE_7726E89B |     2 |    86 |     4   (0)| 00:00:01 |       |       |
|   4 |     UNION-ALL                                  |                 |       |       |            |          |       |       |
|   5 |      PARTITION RANGE SINGLE                    |                 |     1 |    18 |     2   (0)| 00:00:01 |     4 |     4 |
|*  6 |       TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| ANBOB_T3        |     1 |    18 |     2   (0)| 00:00:01 |     4 |     4 |
|*  7 |        INDEX RANGE SCAN                        | IDX_T3_ID3      |     1 |       |     1   (0)| 00:00:01 |     4 |     4 |
|   8 |      PARTITION RANGE SINGLE                    |                 |     1 |    18 |     2   (0)| 00:00:01 |     6 |     6 |
|*  9 |       TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| ANBOB_T3        |     1 |    18 |     2   (0)| 00:00:01 |     6 |     6 |
|* 10 |        INDEX RANGE SCAN                        | IDX_T3_ID3      |     1 |       |     1   (0)| 00:00:01 |     6 |     6 |
|  11 |    PARTITION RANGE SINGLE                      |                 |     1 |    18 |   206   (0)| 00:00:01 |     5 |     5 |
|* 12 |     TABLE ACCESS FULL                          | ANBOB_T3        |     1 |    18 |   206   (0)| 00:00:01 |     5 |     5 |
----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - filter(("ANBOB_T3"."YEARS"=2015) AND "ANBOB_T3"."YEARS"<=2015) 7 - access("ID"=1) 9 - filter(("ANBOB_T3"."YEARS">=2015 OR "ANBOB_T3"."YEARS"<2014) AND "ANBOB_T3"."YEARS"<=2015 AND (LNNVL(TBL$OR$IDX$PART$NUM("ANBOB_T3",0,0,65535,ROWID)>=4) OR LNNVL(TBL$OR$IDX$PART$NUM("ANBOB_T3",0,0,65535,ROWID)<=4)
              OR LNNVL("ANBOB_T3"."YEARS"<=2015) OR LNNVL("ANBOB_T3"."YEARS">=2013) OR LNNVL("ANBOB_T3"."YEARS"<2014)) AND (LNNVL(TBL$OR$IDX$PART$NUM("ANBOB_T3",0,0,65535,ROWID)>=4) OR LNNVL(TBL$OR$IDX$PART$NUM("ANBOB_T3",0,0,65535,ROWID)<=4)
              OR LNNVL("ANBOB_T3"."YEARS"<=2015) OR LNNVL("ANBOB_T3"."YEARS">=2013) OR LNNVL("ANBOB_T3"."YEARS">=2015) OR
              LNNVL("ANBOB_T3"."YEARS"<2016)))
  10 - access("ID"=1)
  12 - filter("ID"=1)

Note:
简的总结是分区上有索引用索引;无索引全表扫; 跨多分区中indexing有开有关时有用索引和全表的子集union all.

打赏

, ,

对不起,这篇文章暂时关闭评论。