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新特性-線上把非分割槽錶轉為分割槽表Oracle
- ORACLE 19c 新特性之混合分割槽表Oracle
- oracle 19C新特性——混合分割槽表Oracle
- 【12.2】Oracle 12C R2新特性-外部表支援分割槽了（Partitioning External Tables）Oracle
- LightDB 22.4 新特性之相容Oracle線上轉換普通表為分割槽表Oracle
- Oracle分割槽表基礎運維-06分割槽表索引Oracle運維索引
- oracle分割槽表和分割槽表exchangeOracle
- Oracle優化案例-分割槽索引之無字首索引（六）Oracle優化索引
- oracle分割槽表和非分割槽表exchangeOracle
- index_oracle索引梳理系列及分割槽表梳理IndexOracle索引
- 移動分割槽表和分割槽索引的表空間索引
- Oracle 12C新特性-RMAN恢復表Oracle
- MySQL 8 新特性之Invisible IndexesMySqlIndex
- Oracle 12.2之後ALTER TABLE .. MODIFY轉換非分割槽表為分割槽表Oracle
- [oracle] expdp 匯出分割槽表的分割槽Oracle
- oracle 分割槽表move和包含分割槽表的lob moveOracle
- ORACLE分割槽表梳理系列Oracle
- Oracle分割槽表基礎運維-04列表分割槽Oracle運維
- Oracle分割槽表基礎運維-07增加分割槽(3列表分割槽)Oracle運維
- 測試分割槽表部分匯出
- 【INDEX】Oracle分割槽索引技術詳解IndexOracle索引
- oracle將表配置為分割槽表Oracle
- oracle 普通表-分割槽表改造流程Oracle
- Oracle分割槽表基礎運維-07增加分割槽(2 HASH分割槽)Oracle運維
- 增加表分割槽時，為local分割槽索引指定不同表空間的方法索引
- Oracle分割槽表基礎運維-09刪除分割槽Oracle運維
- Oracle分割槽表基礎運維-05組合分割槽Oracle運維
- Oracle分割槽表基礎運維-02範圍分割槽Oracle運維
- Oracle分割槽表基礎運維-03HASH分割槽Oracle運維
- Oracle 12C新特性-History命令Oracle
- Oracle 12C新特性In-MemoryOracle
- Oracle 12c 兩個新特性Oracle
- Oracle分割槽表基礎運維-07增加分割槽(1範圍分割槽)Oracle運維
- 記一次Oracle分割槽表全域性索引重建的過程Oracle索引
- Oracle分割槽表基礎運維-01分割槽表分類Oracle運維
- Oracle 12c新特性--ASMFD（ASM Filter Driver）特性OracleASMFilter
- 對oracle分割槽表的理解整理Oracle
- Oracle SQL調優之分割槽表OracleSQL
- oracle 線上重新定義，普通表改變分割槽表，分割槽表可以更改型別、分割槽欄位等Oracle型別