Oracle 12c 分区表新特性 -- Partial Indexes/局部索引 -- cnDBA.cn

Oracle 12c 开始支持对分区表的特定分区创建本地和全局索引，即可以选择性的指定哪些分区上创建索引，哪些分区上不创建索引。
http://www.cndba.cn/dave/article/1852http://www.cndba.cn/dave/article/1852

http://www.cndba.cn/dave/article/1852http://www.cndba.cn/dave/article/1852

1 官方文档说明: Partial Indexes for Partitioned Tables

A partial index is an index that is correlated with the indexing properties of an associated partitioned table. The correlation enables you to specify which table partitions are indexed.

Partial indexes provide the following advantages:

Table partitions that are not indexed avoid consuming unnecessary index storage space.
Performance of loads and queries can improve.

Before Oracle Database 12c, an exchange partition operation required a physical update of an associated global index to retain it as usable. Starting with Oracle Database 12c, if the partitions involved in a partition maintenance operation are not part of a partial global index, then the index remains usable without requiring any global index maintenance.

If you index only some table partitions at index creation, and if you later index other partitions, then you can reduce the sort space required by index creation.

You can turn indexing on or off for the individual partitions of a table. A partial local index does not have usable index partitions for all table partitions that have indexing turned off. A global index, whether partitioned or not, excludes the data from all partitions that have indexing turned off. The database does not support partial indexes for indexes that enforce unique constraints.

http://www.cndba.cn/dave/article/1852

2 Local Index 操作示例

--查看数据库信息：
SQL> set lines 200
SQL> select * from v$version;

BANNER										     CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production		  0
PL/SQL Release 12.2.0.1.0 - Production							  0
CORE	12.2.0.1.0	Production								  0
TNS for Linux: Version 12.2.0.1.0 - Production						  0
NLSRTL Version 12.2.0.1.0 - Production							  0
SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 DAVE 			  READ WRITE NO

SQL> alter session set container=dave;
Session altered.
SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 3 DAVE 			  READ WRITE NO

--创建测试表：
SQL> CREATE TABLE dave
(
   id        NUMBER (5),
   name      VARCHAR (50),
   website   VARCHAR (50),
   addr      VARCHAR (50)
)
PARTITION BY RANGE
   (id)
   (
      PARTITION id_10 VALUES LESS THAN (10),
      PARTITION id_20 VALUES LESS THAN (20));  
Table created.
SQL> insert into dave values(1,'dave','www.cndba.cn','12c');
1 row created.
SQL>  insert into dave values(2,'dave','www.cndba.cn','12c');

1 row created.
SQL> insert into dave values(11,'dave','www.cndba.cn','12c');
1 row created.
SQL> insert into dave values(12,'dave','www.cndba.cn','12c');
1 row created.
SQL> commit;
Commit complete.

SQL> col name for a15
SQL> col website for a25
SQL> col addr for a15
SQL> select * from dave;

	ID NAME 	   WEBSITE		     ADDR
---------- --------------- ------------------------- ---------------
	 1 dave 	   www.cndba.cn 	     12c
	 2 dave 	   www.cndba.cn 	     12c
	11 dave 	   www.cndba.cn 	     12c
	12 dave 	   www.cndba.cn 	     12c

--创建本地索引,默认情况下会对每个分区创建索引：
SQL> create index idx_dave1 on dave(name) local;
Index created.
SQL> col index_name for a20
SQL> col partition_name for a20
SQL> select index_name, partition_name, segment_created from dba_ind_partitions where index_name = 'IDX_DAVE1';

INDEX_NAME	     PARTITION_NAME	  SEG
-------------------- -------------------- ---
IDX_DAVE1	     ID_10		  YES
IDX_DAVE1	     ID_20		  YES
SQL> col segment_name for a15
SQL> SELECT PARTITION_NAME,SEGMENT_NAME FROM USER_SEGMENTS WHERE SEGMENT_NAME='DAVE';

PARTITION_NAME	     SEGMENT_NAME
-------------------- ---------------
ID_10		     DAVE
ID_20		     DAVE

--创建局部索引：这里有2中方法，一是在建表的时候指定哪些分区使用局部索引，二是使用alter 语法修改：
--使用alter 修改：
SQL>  alter table dave modify partition id_20 indexing off;
 alter table dave modify partition id_20 indexing off
*
ERROR at line 1:
ORA-14809: schema does not support INDEXING option

SQL> !oerr ora 14809
14809, 00000, "schema does not support %s"
// *Cause:  An ALTER TABLE MOVE PARTITION ONLINE or an ALTER TABLE MODIFY
//          PARTITION INDEXING statement was issued on a table owned by SYS,
//          SYSTEM, PUBLIC, OUTLN, or XDB.
// *Action: Do not use the ONLINE keyword or the INDEXING clause.

这里看到sys用户是不支持。 所以我们新建用户在测试：
SQL> create user dave identified by cndba;
User created.

SQL> grant connect,resource,dba to dave;
Grant succeeded.

--这里需要注意：连接pdb必须通过service name 进行，直接连会报ORA-01017的错误：
SQL> conn dave/cndba;
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> conn dave/cndba@dave
Connected.

SQL> CREATE TABLE dave2
(
   id        NUMBER (5),
   name      VARCHAR (50),
   website   VARCHAR (50),
   addr      VARCHAR (50)
)
PARTITION BY RANGE (id)
   (PARTITION id_10 VALUES LESS THAN (10) indexing off,
      PARTITION id_20 VALUES LESS THAN (20) indexing on) ;
Table created.

SQL> alter table dave2 modify partition id_20 indexing off;
Table altered.

--注意这里必须是：local indexing partial，否则就是普通的本地索引而非局部索引：
SQL> create index idx_dave2 on dave2(name) local indexing partial;
Index created.

SQL> COL INDEX_NAME FOR A10
SQL> COL PARTITION_NAME FOR A15
SQL> select index_name, partition_name,STATUS from user_ind_partitions  where index_name = 'IDX_DAVE2';
INDEX_NAME PARTITION_NAME  STATUS
---------- --------------- --------
IDX_DAVE2  ID_10	   UNUSABLE
IDX_DAVE2  ID_20	   UNUSABLE


对于本地索引，标记为INDEXING OFF的分区索引状态将显示为UNUSABLE， 标记为INDEXING ON的分区索引状态显示为USABLE。可以通过DBA|USER_PART_TABLES, DBA|USER_TAB_PARTITIONS, 和DBA|USER_TAB_SUBPARTITIONS 视图的INDEXING列查看索引的状态。

SQL> select partition_name,indexing from user_tab_partitions where table_name='DAVE2';
PARTITION_NAME	INDE
--------------- ----
ID_10		OFF
ID_20		OFF

SQL> alter table dave2 modify partition id_20 indexing on;

Table altered.

SQL> select partition_name,indexing from user_tab_partitions where table_name='DAVE2';

PARTITION_NAME	INDE
--------------- ----
ID_10		OFF
ID_20		ON

SQL> insert into dave2 select * from sys.dave;
4 rows created.

SQL> commit;
Commit complete.

SQL> col segment_name for a15
SQL> select partition_name,segment_name,blocks from user_segments where segment_name='DAVE2';

PARTITION_NAME	SEGMENT_NAME	    BLOCKS
--------------- --------------- ----------
ID_10		DAVE2		      1024
ID_20		DAVE2		      1024

--验证：这里只有分区为INDEXING ON的才分配的segment，其他分区未创建：
SQL> select partition_name,segment_name from user_segments where segment_name='IDX_DAVE2';

PARTITION_NAME	SEGMENT_NAME
--------------- ---------------
ID_20		IDX_DAVE2

3 Global Index 操作示例

SQL> create index ind_g_dave2 on dave2(addr) global indexing partial;

Index created.
SQL> select index_name,indexing from dba_indexes where index_name='IND_G_DAVE2';

INDEX_NAME	     INDEXIN
-------------------- -------
IND_G_DAVE2	     PARTIAL

这边可以通过执行计划进行验证：
先分别往2个分区里插入一条数据：
SQL> insert into dave2 values(12,'dave','www.cndba.cn','oracle12c');

1 row created.

SQL> insert into dave2 values(8,'dave','www.cndba.cn','oracle11g');
1 row created.

SQL> commit;
Commit complete.

--查询范围内有索引，有局部索引的走局部索引，没有局部索引的走全表扫描

SQL> set autotrace on explain;
SQL> select * from dave2 where addr='oracle12c';

	ID NAME 		WEBSITE 		  ADDR
---------- -------------------- ------------------------- --------------------------------------------------
	12 dave 		www.cndba.cn		  oracle12c


Execution Plan
----------------------------------------------------------
Plan hash value: 837012547

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation				     | Name	   | Rows  | Bytes | Cost (%CPU)| Time	   | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT			     |		   |	 1 |	94 |   275   (0)| 00:00:01 |	   |	   |
|   1 |  VIEW					     | VW_TE_2	   |	 2 |   188 |   275   (0)| 00:00:01 |	   |	   |
|   2 |   UNION-ALL				     |		   |	   |	   |		|	   |	   |	   |
|*  3 |    TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| DAVE2	   |	 1 |	94 |	 1   (0)| 00:00:01 |	 2 |	 2 |
|*  4 |     INDEX RANGE SCAN			     | IND_G_DAVE2 |	 1 |	   |	 1   (0)| 00:00:01 |	   |	   |
|   5 |    PARTITION RANGE SINGLE		     |		   |	 1 |	94 |   274   (0)| 00:00:01 |	 1 |	 1 |
|*  6 |     TABLE ACCESS FULL			     | DAVE2	   |	 1 |	94 |   274   (0)| 00:00:01 |	 1 |	 1 |
----------------------------------------------------------------------------------------------------------------------------

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

   3 - filter("DAVE2"."ID">=10 AND "DAVE2"."ID"<20)
   4 - access("ADDR"='oracle12c')
   6 - filter("ADDR"='oracle12c')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

--查询范围内有索引，使用局部索引：

SQL> select * from dave2 where addr='oracle11g' and id>10;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 2113055654

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation				   | Name	 | Rows  | Bytes | Cost (%CPU)| Time	 | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT			   |		 |     1 |    94 |     1   (0)| 00:00:01 |	 |	 |
|*  1 |  TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| DAVE2	 |     1 |    94 |     1   (0)| 00:00:01 |     2 |     2 |
|*  2 |   INDEX RANGE SCAN			   | IND_G_DAVE2 |     1 |	 |     1   (0)| 00:00:01 |	 |	 |
--------------------------------------------------------------------------------------------------------------------------

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

   1 - filter("ID">10)
   2 - access("ADDR"='oracle11g')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

--查询范围内无索引，只用全表扫描：

SQL> select * from dave2 where addr='oracle11g' and id<10;

	ID NAME 		WEBSITE 		  ADDR
---------- -------------------- ------------------------- --------------------------------------------------
	 8 dave 		www.cndba.cn		  oracle11g
	 8 dave 		www.cndba.cn		  oracle11g


Execution Plan
----------------------------------------------------------
Plan hash value: 1722186136

------------------------------------------------------------------------------------------------
| Id  | Operation	       | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |       |     1 |    94 |   274	 (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|       |     1 |    94 |   274	 (0)| 00:00:01 |     1 |     1 |
|*  2 |   TABLE ACCESS FULL    | DAVE2 |     1 |    94 |   274	 (0)| 00:00:01 |     1 |     1 |
------------------------------------------------------------------------------------------------

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

   2 - filter("ADDR"='oracle11g')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

SQL>

4 总结

1. 不能对唯一索引创建局部索引。

2. Global partial Index只包含标记为INDEXING ON的分区中的数据，其他分区的数据不包含在全局索引中。

3. 查询条件内有索引，会直接使用索引(无论是Local index还是Global index)。

4. 查询条件内有索引，有局部索引的走局部索引，没有局部索引的走全表扫描。

Oracle 12c Partial Indexes

签到成功

CNDBA社区