12C分区视图中测试全局和局部索引
-----------------------------------------------------------------------------------------------------
-- 创建分表区
CREATE TABLE p_andy
(ID number(10), NAME varchar2(30))
INDEXING OFF
PARTITION BY RANGE (id)
(PARTITION p1 VALUES LESS THAN (200),
PARTITION p2 VALUES LESS THAN (400),
PARTITION p3 VALUES LESS THAN (600) INDEXING ON,
PARTITION p4 VALUES LESS THAN (800),
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> create index IDX_ANDY_ID on p_andy(id) GLOBAL INDEXING full;
Index created.
SQL> alter index IDX_ANDY_ID invisible;
Index altered.
--创建局部索引
SQL> create index IDX_ANDY_ID_L on p_andy(id) GLOBAL INDEXING partial;
Index created.
--查看索引状态
SQL> select index_name,index_type,visibility,indexing from user_indexes where table_name='P_ANDY';
INDEX_NAME INDEX_TYPE VISIBILITY INDEXING
------------------------------ ------------------------------------------------------ ------------------ --------------
IDX_PANDY_ID_L NORMAL VISIBLE PARTIAL
IDX_ANDY_ID NORMAL INVISIBLE FULL
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 INDEXING
------------------------- ------------------------- ------------------ ------------------------------ ---------- --------
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
--创建分区视图
SQL> create view v_pandy as
select * from p_andy partition(p1) where id<=200 union all
select * from p_andy partition(p2) where id>200 and id<=400 union all
select * from p_andy partition(p3) where id>400 and id<=600 union all
select * from p_andy partition(p4) where id>600 and id<=800 union all
select * from p_andy partition(p5) where id>800 ;
--查看执行计划
SQL> select * from v_pandy;
执行计划
----------------------------------------------------------
Plan hash value: 2836486000
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2127 | 63810 | 1100 (1)| 00:00:01 | | |
| 1 | VIEW | V_PANDY | 2127 | 63810 | 1100 (1)| 00:00:01 | | |
| 2 | UNION-ALL | | | | | | | |
| 3 | PARTITION RANGE SINGLE | | 2 | 60 | 275 (1)| 00:00:01 | 1 | 1 |
|* 4 | TABLE ACCESS FULL | P_ANDY | 2 | 60 | 275 (1)| 00:00:01 | 1 | 1 |
| 5 | PARTITION RANGE SINGLE | | 314 | 9420 | 275 (1)| 00:00:01 | 2 | 2 |
|* 6 | TABLE ACCESS FULL | P_ANDY | 314 | 9420 | 275 (1)| 00:00:01 | 2 | 2 |
| 7 | PARTITION RANGE SINGLE | | 314 | 9420 | 275 (1)| 00:00:01 | 3 | 3 |
|* 8 | TABLE ACCESS FULL | P_ANDY | 314 | 9420 | 275 (1)| 00:00:01 | 3 | 3 |
| 9 | PARTITION RANGE SINGLE | | 239 | 7170 | 2 (0)| 00:00:01 | 4 | 4 |
| 10 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| P_ANDY | 239 | 7170 | 2 (0)| 00:00:01 | 4 | 4 |
|* 11 | INDEX RANGE SCAN | IDX_PANDY_ID_L | 370 | | 1 (0)| 00:00:01 | 4 | 4 |
| 12 | PARTITION RANGE SINGLE | | 1258 | 37740 | 275 (1)| 00:00:01 | 5 | 5 |
|* 13 | TABLE ACCESS FULL | P_ANDY | 1258 | 37740 | 275 (1)| 00:00:01 | 5 | 5 |
-------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("ID"<=200)
6 - filter("ID">200 AND "ID"<=400)
8 - filter("ID">400 AND "ID"<=600)
11 - access("ID">600 AND "ID"<=800)
13 - filter("ID">800)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- 1 Sql Plan Directive used for this statement
--查询原分区表的执行计划:
SQL> select * from p_andy;
执行计划
----------------------------------------------------------
Plan hash value: 3629908784
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 1365 (1)| 00:00:01 | | |
| 1 | PARTITION RANGE ALL| | 1 | 30 | 1365 (1)| 00:00:01 | 1 | 5 |
| 2 | TABLE ACCESS FULL | P_ANDY | 1 | 30 | 1365 (1)| 00:00:01 | 1 | 5 |
----------------------------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- 1 Sql Plan Directive used for this statement
--修改全局索引为可见,局部索引为不可见。
SQL> alter index IDX_ANDY_ID_L invisible;
Index altered.
SQL> alter index IDX_ANDY_ID invisible;
Index altered.
SQL> select index_name,index_type,visibility,indexing from user_indexes where table_name='P_ANDY';
INDEX_NAME INDEX_TYPE VISIBILITY INDEXING
------------------------------ ------------------------------------------------------ ------------------ --------------
IDX_ANDY_ID NORMAL VISIBLE FULL
IDX_PANDY_ID_L NORMAL INVISIBLE PARTIAL
--再次观察执行计划
SQL> select * from v_pandy;
执行计划
----------------------------------------------------------
Plan hash value: 4277153543
----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1070 | 32100 | 15 (0)| 00:00:01 | | |
| 1 | VIEW | V_PANDY | 1070 | 32100 | 15 (0)| 00:00:01 | | |
| 2 | UNION-ALL | | | | | | | |
| 3 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| P_ANDY | 195 | 5850 | 3 (0)| 00:00:01 | 1 | 1 |
|* 4 | INDEX RANGE SCAN | IDX_ANDY_ID | 200 | | 2 (0)| 00:00:01 | | |
| 5 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| P_ANDY | 189 | 5670 | 3 (0)| 00:00:01 | 2 | 2 |
|* 6 | INDEX RANGE SCAN | IDX_ANDY_ID | 200 | | 2 (0)| 00:00:01 | | |
| 7 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| P_ANDY | 220 | 6600 | 3 (0)| 00:00:01 | 3 | 3 |
|* 8 | INDEX RANGE SCAN | IDX_ANDY_ID | 200 | | 2 (0)| 00:00:01 | | |
| 9 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| P_ANDY | 233 | 6990 | 3 (0)| 00:00:01 | 4 | 4 |
|* 10 | INDEX RANGE SCAN | IDX_ANDY_ID | 200 | | 2 (0)| 00:00:01 | | |
| 11 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| P_ANDY | 233 | 6990 | 3 (0)| 00:00:01 | 5 | 5 |
|* 12 | INDEX RANGE SCAN | IDX_ANDY_ID | 200 | | 2 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("ID"<=200)
filter(TBL$OR$IDX$PART$NUM(HR."P_ANDY",0,1,0,ROWID)=1)
6 - access("ID">200 AND "ID"<=400)
filter(TBL$OR$IDX$PART$NUM(HR."P_ANDY",0,1,0,ROWID)=2)
8 - access("ID">400 AND "ID"<=600)
filter(TBL$OR$IDX$PART$NUM(HR."P_ANDY",0,1,0,ROWID)=3)
10 - access("ID">600 AND "ID"<=800)
filter(TBL$OR$IDX$PART$NUM(HR."P_ANDY",0,1,0,ROWID)=4)
12 - access("ID">800)
filter(TBL$OR$IDX$PART$NUM(HR."P_ANDY",0,1,0,ROWID)=5)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- 1 Sql Plan Directive used for this statement
--查询原分区表的执行计划:
SQL> select * from p_andy;
执行计划
----------------------------------------------------------
Plan hash value: 3629908784
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 1365 (1)| 00:00:01 | | |
| 1 | PARTITION RANGE ALL| | 1 | 30 | 1365 (1)| 00:00:01 | 1 | 5 |
| 2 | TABLE ACCESS FULL | P_ANDY | 1 | 30 | 1365 (1)| 00:00:01 | 1 | 5 |
----------------------------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- 1 Sql Plan Directive used for this statement
结论:
在查询分区视图时(不加条件),
当前可见索引为局部索引时,只有分区的indexing为ON时才会走局部索引扫描,其它的走全表扫描;
当前可见索引为全局索引时,不论分区indexing是否为ON都会走全局索引。
在查看原分区表时(不加条件),
不论当前可见索引为全局索引还是局部索引,不论分区indexing是否为ON都不会走全局索引。
|