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.
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。