在分区视图中测试全局和局部索引（12C）

windjack

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都不会走全局索引。