Oracle 12C 新特性之表分割槽部分索引(Partial Indexes)
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.
-- 檢視索引狀態
SQL>
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;
Index created.
-- 檢視索引狀態
SQL>
col index_name for a25
select table_name,index_name,partitioned,blevel,leaf_blocks,indexing from user_Indexes where index_name ='IDX_PANDY_ID01';
TABLE_NAME INDEX_NAME PAR BLEVEL LEAF_BLOCKS INDEXIN
------------------------- ------------------------- --- ---------- ----------- -------
P_ANDY IDX_PANDY_ID01 NO 1 3 FULL
SQL> drop index IDX_PANDY_ID01;
Index dropped.
SQL> create index idx_pAndy_id02 on p_andy(id) GLOBAL INDEXING PARTIAL;
Index created.
-- 檢視索引狀態
SQL>
col index_name for a25
select table_name,index_name,partitioned,blevel,leaf_blocks,indexing from user_Indexes where index_name ='IDX_PANDY_ID02';
TABLE_NAME INDEX_NAME PAR BLEVEL LEAF_BLOCKS INDEXIN
------------------------- ------------------------- --- ---------- ----------- -------
P_ANDY IDX_PANDY_ID02 NO 0 1 PARTIAL
SQL> drop index IDX_PANDY_ID02;
Index dropped.
說明：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;
Index created.
-- 檢視索引狀態
SQL>
col index_name for a25
select table_name,index_name,partitioned,blevel,leaf_blocks,indexing from user_Indexes where index_name ='IDX_PANDY_ID03';
TABLE_NAME INDEX_NAME PAR BLEVEL LEAF_BLOCKS INDEXIN
------------------------- ------------------------- --- ---------- ----------- -------
P_ANDY IDX_PANDY_ID03 YES 0 5 FULL
SQL> drop index idx_pAndy_id03;
Index dropped.
建立LOCAL索引
SQL> create index idx_pAndy_id03 on p_andy(id) local INDEXING PARTIAL;
Index created.
-- 檢視索引狀態
SQL>
col index_name for a25
select table_name,index_name,partitioned,blevel,leaf_blocks,indexing from user_Indexes where index_name ='IDX_PANDY_ID03';
TABLE_NAME INDEX_NAME PAR BLEVEL LEAF_BLOCKS INDEXIN
------------------------- ------------------------- --- ---------- ----------- -------
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 ;
Index dropped.
SQL> drop index IDX_PANDY_ID_G;
Index dropped.
-- 檢視現在表的分割槽：
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 ON
P_ANDY P4 4 USERS 800 OFF
P_ANDY P5 5 USERS MAXVALUE OFF
-- 建立全域性索引
SQL> create index idx_pAndy_id_G on p_andy(id) GLOBAL INDEXING PARTIAL;
Index created.
-- 驗證是不是部分全域性索引
SQL> select index_name,indexing from dba_indexes where index_name='IDX_PANDY_ID_G';
INDEX_NAME INDEXIN
-------------------- -------
IDX_PANDY_ID_G PARTIAL > 說明是部分全域性索引
SQL>
col index_name for a25
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;
Table altered.
SQL> 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
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
-- 改變分割槽 indexing 屬性
-- 建立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;
Index created.
說明：同一欄位上不能建立兩個相同種類的索引，這裡把第二次建立的索引設定為 invisible 。
SQL>
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;
Execution Plan
----------------------------------------------------------
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;
Index altered.
SQL> select * from p_andy where id=770;
Execution Plan
----------------------------------------------------------
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;
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 230 | 2300 | 276 (0)| 00:00:01 | | |
| 1 | VIEW | VW_TE_2 | 230 | 6900 | 276 (0)| 00:00:01 | | |
| 2 | UNION-ALL | | | | | | | |
| 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。
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.
-- 檢視索引狀態
SQL>
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;
Index created.
-- 檢視索引狀態
SQL>
col index_name for a25
select table_name,index_name,partitioned,blevel,leaf_blocks,indexing from user_Indexes where index_name ='IDX_PANDY_ID01';
TABLE_NAME INDEX_NAME PAR BLEVEL LEAF_BLOCKS INDEXIN
------------------------- ------------------------- --- ---------- ----------- -------
P_ANDY IDX_PANDY_ID01 NO 1 3 FULL
SQL> drop index IDX_PANDY_ID01;
Index dropped.
SQL> create index idx_pAndy_id02 on p_andy(id) GLOBAL INDEXING PARTIAL;
Index created.
-- 檢視索引狀態
SQL>
col index_name for a25
select table_name,index_name,partitioned,blevel,leaf_blocks,indexing from user_Indexes where index_name ='IDX_PANDY_ID02';
TABLE_NAME INDEX_NAME PAR BLEVEL LEAF_BLOCKS INDEXIN
------------------------- ------------------------- --- ---------- ----------- -------
P_ANDY IDX_PANDY_ID02 NO 0 1 PARTIAL
SQL> drop index IDX_PANDY_ID02;
Index dropped.
說明：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;
Index created.
-- 檢視索引狀態
SQL>
col index_name for a25
select table_name,index_name,partitioned,blevel,leaf_blocks,indexing from user_Indexes where index_name ='IDX_PANDY_ID03';
TABLE_NAME INDEX_NAME PAR BLEVEL LEAF_BLOCKS INDEXIN
------------------------- ------------------------- --- ---------- ----------- -------
P_ANDY IDX_PANDY_ID03 YES 0 5 FULL
SQL> drop index idx_pAndy_id03;
Index dropped.
建立LOCAL索引
SQL> create index idx_pAndy_id03 on p_andy(id) local INDEXING PARTIAL;
Index created.
-- 檢視索引狀態
SQL>
col index_name for a25
select table_name,index_name,partitioned,blevel,leaf_blocks,indexing from user_Indexes where index_name ='IDX_PANDY_ID03';
TABLE_NAME INDEX_NAME PAR BLEVEL LEAF_BLOCKS INDEXIN
------------------------- ------------------------- --- ---------- ----------- -------
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 ;
Index dropped.
SQL> drop index IDX_PANDY_ID_G;
Index dropped.
-- 檢視現在表的分割槽：
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 ON
P_ANDY P4 4 USERS 800 OFF
P_ANDY P5 5 USERS MAXVALUE OFF
-- 建立全域性索引
SQL> create index idx_pAndy_id_G on p_andy(id) GLOBAL INDEXING PARTIAL;
Index created.
-- 驗證是不是部分全域性索引
SQL> select index_name,indexing from dba_indexes where index_name='IDX_PANDY_ID_G';
INDEX_NAME INDEXIN
-------------------- -------
IDX_PANDY_ID_G PARTIAL > 說明是部分全域性索引
SQL>
col index_name for a25
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;
Table altered.
SQL> 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
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
-- 改變分割槽 indexing 屬性
-- 建立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;
Index created.
說明：同一欄位上不能建立兩個相同種類的索引，這裡把第二次建立的索引設定為 invisible 。
SQL>
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;
Execution Plan
----------------------------------------------------------
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;
Index altered.
SQL> select * from p_andy where id=770;
Execution Plan
----------------------------------------------------------
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;
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 230 | 2300 | 276 (0)| 00:00:01 | | |
| 1 | VIEW | VW_TE_2 | 230 | 6900 | 276 (0)| 00:00:01 | | |
| 2 | UNION-ALL | | | | | | | |
| 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。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31383567/viewspace-2139092/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle 12C 新特性之非分割槽錶轉分割槽表online clause（不停業務+索引有效）Oracle索引
- Oracle 12C 新特性之表分割槽或子分割槽的線上遷移Oracle
- Oracle 12C新特性-線上把非分割槽錶轉為分割槽表Oracle
- ［引用分割槽表］Oracle 11g新特性之引用分割槽表Oracle
- Oracle 12C 新特性之move （非分割槽表）table onlineOracle
- 關於分割槽表和分割槽索引(About Partitioned Tables and Indexes)索引Index
- ORACLE 19c 新特性之混合分割槽表Oracle
- 分割槽表、分割槽索引和全域性索引部分總結索引
- Oracle分割槽表及分割槽索引Oracle索引
- Oracle11.2表分割槽新特性Oracle
- 簡單ORACLE分割槽表、分割槽索引Oracle索引
- 12C新特性之表分割槽非同步全域性索引非同步維護（add、truncate、drop、spilt、merge多分割槽）非同步索引
- Oracle帶區域性分割槽索引的分割槽表刪除舊分割槽新增新分割槽Oracle索引
- oracle 19C新特性——混合分割槽表Oracle
- ORACLE10g新特性——全域性HASH分割槽索引Oracle索引
- 【學習筆記】分割槽表和分割槽索引——概念部分（一）筆記索引
- 深入學習Oracle分割槽表及分割槽索引Oracle索引
- 全面認識oracle分割槽表及分割槽索引Oracle索引
- Oracle分割槽表基礎運維-06分割槽表索引Oracle運維索引
- 【ORACLE新特性】11G 分割槽新特性Oracle
- Oracle分割槽之五：建立分割槽索引總結Oracle索引
- oracle 之全文索引表的分割槽交換案例Oracle索引
- Oracle 12C 新特性之 恢復表Oracle
- LightDB 22.4 新特性之相容Oracle線上轉換普通表為分割槽表Oracle
- Oracle 12c新特性之：使用高階索引壓縮建立索引Oracle索引
- Oracle索引分割槽Oracle索引
- rebuild分割槽表分割槽索引的方法Rebuild索引
- 分割槽表及分割槽索引建立示例索引
- 移動 oracle object 到新的表空間(表,索引,分割槽,LOB)OracleObject索引
- 【學習筆記】分割槽表和分割槽索引——管理索引分割槽（四）筆記索引
- 全面學習分割槽表及分割槽索引(13)--分隔表分割槽索引
- Oracle分割槽表全域性索引新增分割槽時不會失效Oracle索引
- 【12.2】Oracle 12C R2新特性-外部表支援分割槽了（Partitioning External Tables）Oracle
- 全面學習分割槽表及分割槽索引(17)--其它索引分割槽管理操作索引
- oracle 針對普通表的索引分割槽及10g新增hash 索引分割槽Oracle索引
- 全面學習分割槽表及分割槽索引(9)--刪除表分割槽索引
- 全面學習分割槽表及分割槽索引(11)--合併表分割槽索引
- 全面學習分割槽表及分割槽索引(12)--修改list表分割槽索引