12c之前沒辦法在部分或指定的分區上建立索引,12c 版本中引入了Partial Indexes(部分索引), 無論是global還是local都可以有選擇性的對部分分區建立索引。分區上有索引用索引，無索引全表掃，跨多分區中indexing有開有關時有用索引和全表的子集union all。
partial index有兩個限制條件:
1、 表是分區表
2、 不能是唯一索引
3、 建表的時候分區指定 INDEXING OFF or ON 或者 alter table 修改
一：indexing 探讨。
-- 建立實驗表
CREATE TABLE p_andy
(ID number(10), NAME varchar2(30))
INDEXING OFF
PARTITION BY RANGE (id)
(PARTITION p1 VALUES LESS THAN (200) INDEXING OFF,
PARTITION p2 VALUES LESS THAN (400),
PARTITION p3 VALUES LESS THAN (600),
PARTITION p4 VALUES LESS THAN (800) INDEXING ON,
partition p5 values less than (maxvalue)
);
Table created.
-- 插入資料
begin
for i in 1 .. 1000 loop
insert into p_andy values(i,'andyi');
end loop ;
commit;
end;
/
PL/SQL procedure successfully completed.
-- 檢視現在表的分區：
SQL>
col table_name for a25
col partition_name for a25
col HIGH_VALUE for a10
select table_name,partition_name,PARTITION_POSITION,tablespace_name,HIGH_VALUE,indexing from user_tab_partitions where table_name='P_ANDY';
TABLE_NAME PARTITION_NAME PARTITION_POSITION TABLESPACE_NAME HIGH_VALUE INDE
------------------------- ------------------------- ------------------ ------------------------------ ---------- ----
P_ANDY P1 1 USERS 200 OFF
P_ANDY P2 2 USERS 400 OFF
P_ANDY P3 3 USERS 600 OFF
P_ANDY P4 4 USERS 800 ON
P_ANDY P5 5 USERS MAXVALUE OFF
說明：Create table屬性indexing的預設值是ON; partition 或sub partition如果沒有指定indexing是繼承上級屬性。
--建立global索引
create index idx_pAndy_id on p_andy(id);
Index created.
-- 檢視索引狀态
col index_name for a25
select table_name,index_name,partitioned,blevel,leaf_blocks,indexing from user_Indexes where index_name ='IDX_PANDY_ID';
TABLE_NAME INDEX_NAME PAR BLEVEL LEAF_BLOCKS INDEXIN
------------------------- ------------------------- --- ---------- ----------- -------
P_ANDY IDX_PANDY_ID NO 1 3 FULL
SQL> drop index IDX_PANDY_ID;
Index dropped.
SQL> create index idx_pAndy_id01 on p_andy(id) GLOBAL INDEXING FULL;
select table_name,index_name,partitioned,blevel,leaf_blocks,indexing from user_Indexes where index_name ='IDX_PANDY_ID01';
P_ANDY IDX_PANDY_ID01 NO 1 3 FULL
SQL> drop index IDX_PANDY_ID01;
SQL> create index idx_pAndy_id02 on p_andy(id) GLOBAL INDEXING PARTIAL;
select table_name,index_name,partitioned,blevel,leaf_blocks,indexing from user_Indexes where index_name ='IDX_PANDY_ID02';
P_ANDY IDX_PANDY_ID02 NO 0 1 PARTIAL
SQL> drop index IDX_PANDY_ID02;
說明：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_pAndy_id03 on p_andy(id) local;
select table_name,index_name,partitioned,blevel,leaf_blocks,indexing from user_Indexes where index_name ='IDX_PANDY_ID03';
P_ANDY IDX_PANDY_ID03 YES 0 5 FULL
SQL> drop index idx_pAndy_id03;
SQL> create index idx_pAndy_id03 on p_andy(id) local INDEXING PARTIAL;
P_ANDY IDX_PANDY_ID03 YES 0 1 PARTIAL
說明：建立了local index full和index partial 可以看到部分索引的index leaf blocks和number of rows都明顯減少.
二： 部分索引執行計劃探讨
測試：索引相關的執行計劃
-- 檢視表索引
col column_name for a30
col index_name for a20
SELECT index_name, column_name, descend FROM user_ind_columns WHERE table_name = 'P_ANDY';
INDEX_NAME COLUMN_NAME DESC
-------------------- ------------------------------ ----
IDX_PANDY_ID_L ID ASC
IDX_PANDY_ID_G ID ASC
-- 删除所有索引，為了友善下面建立部分索引。
SQL> drop index IDX_PANDY_ID_L ;
SQL> drop index IDX_PANDY_ID_G;
P_ANDY P3 3 USERS 600 ON
P_ANDY P4 4 USERS 800 OFF
-- 建立全局索引
SQL> create index idx_pAndy_id_G on p_andy(id) GLOBAL INDEXING PARTIAL;
-- 驗證是不是部分全局索引
SQL> select index_name,indexing from dba_indexes where index_name='IDX_PANDY_ID_G';
INDEX_NAME INDEXIN
-------------------- -------
IDX_PANDY_ID_G PARTIAL > 說明是部分全局索引
select table_name,index_name,status,blevel,leaf_blocks from user_Indexes where index_name ='IDX_PANDY_ID_G';
TABLE_NAME INDEX_NAME STATUS BLEVEL LEAF_BLOCKS
------------------------- ------------------------- -------- ---------- -----------
P_ANDY IDX_PANDY_ID_G VALID 0 1
-- 改變分區 indexing 屬性
SQL> alter table P_ANDY modify partition P3 indexing off;
Table altered.
SQL> alter table P_ANDY modify partition P4 indexing on;
SQL> select table_name,index_name,status,blevel,leaf_blocks from user_Indexes where index_name ='IDX_PANDY_ID_G';
SQL> select table_name,partition_name,PARTITION_POSITION,tablespace_name,HIGH_VALUE,indexing from user_tab_partitions where table_name='P_ANDY';
TABLE_NAME PARTITION_NAME PARTITION_POSITION TABLESPACE_NAME HIGH_VALUE INDE
------------------------- --------------- ------------------ ------------------------------ ---------- ----
P_ANDY P1 1 USERS 200 OFF
P_ANDY P2 2 USERS 400 OFF
P_ANDY P3 3 USERS 600 OFF
P_ANDY P4 4 USERS 800 ON
P_ANDY P5 5 USERS MAXVALUE OFF
-- 建立local 索引
SQL> create index idx_pAndy_id_L on p_andy(id) local INDEXING PARTIAL;
ORA-01408: such column list already indexed
SQL> create index idx_pAndy_id_L on p_andy(id) local INDEXING PARTIAL invisible;
說明：同一字段上不能建立兩個相同種類的索引，這裡把第二次建立的索引設定為 invisible 。
COL INDEX_NAME FOR A25
COL PARTITION_NAME FOR A15
select index_name, partition_name,STATUS from user_ind_partitions where index_name = 'IDX_PANDY_ID_L';
INDEX_NAME PARTITION_NAME STATUS
------------------------- --------------- --------
IDX_PANDY_ID_L P1 UNUSABLE
IDX_PANDY_ID_L P2 UNUSABLE
IDX_PANDY_ID_L P3 UNUSABLE
IDX_PANDY_ID_L P4 USABLE
IDX_PANDY_ID_L P5 UNUSABLE
說明：local index 标記為INDEXING OFF的分區索引狀态将顯示為UNUSABLE， 标記為INDEXING ON的分區索引狀态顯示為USABLE。
SQL> set autotrace only on explain;
SQL> select * from p_andy where id>550;
Execution Plan
----------------------------------------------------------
Plan hash value: 4213581160
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 450 | 4500 | 549 (0)| 00:00:01 | | |
| 1 | VIEW | VW_TE_2 | 453 | 13590 | 549 (0)| 00:00:01 | | |
| 2 | UNION-ALL | | | | | | | |
| 3 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| P_ANDY | 200 | 2000 | 2 (0)| 00:00:01 | 4 | 4 |
|* 4 | INDEX RANGE SCAN | IDX_PANDY_ID_G | 40 | | 1 (0)| 00:00:01 | | |
| 5 | PARTITION RANGE OR | | 253 | 2530 | 547 (0)| 00:00:01 |KEY(OR)|KEY(OR)|
|* 6 | TABLE ACCESS FULL | P_ANDY | 253 | 2530 | 547 (0)| 00:00:01 |KEY(OR)|KEY(OR)|
SQL> select * from p_andy where id=550;
Plan hash value: 2878316774
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop
| 0 | SELECT STATEMENT | | 1 | 10 | 274 (0)| 00:00:01 | |
| 1 | PARTITION RANGE SINGLE| | 1 | 10 | 274 (0)| 00:00:01 | 3 | 3
|* 2 | TABLE ACCESS FULL | P_ANDY | 1 | 10 | 274 (0)| 00:00:01 | 3 | 3
SQL> alter index IDX_PANDY_ID_G invisible;
Index altered.
SQL> alter index IDX_PANDY_ID_L visible;
SQL> select * from p_andy where id=770;
Plan hash value: 3981876911
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop
| 0 | SELECT STATEMENT | | 1 | 10 | 2 (0)| 00:00:01 | |
| 1 | PARTITION RANGE SINGLE | | 1 | 10 | 2 (0)| 00:00:01 | 4 | 4
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| P_ANDY | 1 | 10 | 2 (0)| 00:00:01 | 4 | 4
|* 3 | INDEX RANGE SCAN | IDX_PANDY_ID_L | 1 | | 1 (0)| 00:00:01 | 4 | 4
SQL> select * from p_andy where id>770;
| 0 | SELECT STATEMENT | | 230 | 2300 | 276 (0)| 00:00:01 | | |
| 1 | VIEW | VW_TE_2 | 230 | 6900 | 276 (0)| 00:00:01 | | |
| 3 | PARTITION RANGE SINGLE | | 29 | 290 | 2 (0)| 00:00:01 | 4 | 4 |
| 4 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| P_ANDY | 29 | 290 | 2 (0)| 00:00:01 | 4 | 4 |
|* 5 | INDEX RANGE SCAN | IDX_PANDY_ID_L | 29 | | 1 (0)| 00:00:01 | 4 | 4 |
| 6 | PARTITION RANGE SINGLE | | 201 | 2010 | 274 (0)| 00:00:01 | 5 | 5 |
|* 7 | TABLE ACCESS FULL | P_ANDY | 201 | 2010 | 274 (0)| 00:00:01 | 5 | 5 |
說明：分區上有索引用索引（也可能存在某種情況不走索引），無索引全表掃，跨多分區中indexing有開有關時有用索引和全表的子集union all。
文章可以轉載，必須以連結形式标明出處。
本文轉自 張沖andy 部落格園部落格，原文連結： http://www.cnblogs.com/andy6/p/6857209.html ，如需轉載請自行聯系原作者
![龍珠訓練營task04[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)