Oracle 11g分区表性能测试（2）

第二部分：分区索引

一、分区索引

与分区表一样，索引也可以进行分区，包括

1、全局分区索引(GLOBAL)

是一个独立的分区。

2、本地分区索引（LOCAL）

自动和分区表相关联。

适用范围：

通常来说，全局分区索引适用于 OLTP 在线交易系统；而局部分区索引适用于数据仓库或决策系统。

**选择分区索引的类型：

**
按照下面的顺序
1、如果分区表的分区字段是索引键值的子集，则使用本地索引；如果不是这样，则进入步骤2。

2、如果索引是唯一的并且不包含分区表的分区字段，则使用全局索引；如果不是这样，则进入步骤3。

3、如果想管理方便，则使用本地索引；如果不是这样，则进入步骤4。

4、如果应用程序是在线交易系统（OLTP）且需要很快的响应时间，则使用全局索引。如果应用程序是决策系统（DDS）且更关注数据的吞吐量，则使用本地索引。

（1）全局分区索引(GLOBAL)

Oracle 包括全局范围分区索引和全局哈希分区索引。

• 全局范围分区索引

全局范围分区索引是分区程度和分区键独立于分区表的一种灵活分区方法。

如果一个全局分区索引包含数据，删除此分区将会导致下一个最高键值的分区被标记为不可用。你不能删除全局分区中的最高分区。

• 全局哈希分区索引

全局哈希分区索引通过分散索引的竞争来提高性能。大多数的索引插入只会出现在索引的右侧。

（2）本地分区索引（LOCAL）

SG上说明：
an index is non-prefixed if it not parttioned on a left prefix of the index columns.
non-prefixed indexes can be unique only if their partition key is a subset of the index key.

所谓的prefixed或non-prefixed是指一个索引的索相列与这个索引的分区键是不是prefixed关系。

说明：

如果一个索引的的索引列为(first_name,last_name),此时如果想以last_name做为分区键，这即为non-prefixed的索引，如果你以first_name做为分区键，那此时这个索引为prefixed。

关于后一句话，正确的翻译该为：只有当分区键是索引的一部分后，non-prefixed 索引才能建成unique索引，其实这句话说的不全面，因为这句话指的是local index说的。如果分区键不是索引的一部分时，是可以建成global unique index的。

本地分区索引易于维护，每一个本地索引的分区都和一个表的分区相关联，使得 Oracle 自动地维护索引分区到表分区的同步。如果一个分区中的数据失效了，则只会影响一个分区。

非前缀的本地索引在历史数据库中非常有用，这种类型的索引中，分区不是在索引列的左边前缀。不能为本地索引添加分区，只有当你给某个表添加分区时，你才可以添加本地索引。

本地索引可以是唯一的，让一个本地索引是唯一的，表的分区键必须是索引键列的一部分。

二、RANGE分区表前缀索引测试

（1）索引定义

–创建局部前缀索引：分区键（ORDER_DATE）作为索引定义的第一列

create index local_prefixed_ind_range on tb_part_range (ORDER_DATE, CITY) local;

SQL> create index local_prefixed_ind_range on tb_part_range (ORDER_DATE, CITY) local;

Index created


Executed in 1.139 seconds

（2） 索引测试

测试四个情况：

• 按索引逆序

select count(*) from dh.tb_part_range where CITY =‘SHANGHAI’ and ORDER_DATE =to_date(‘2015-11-18’,‘yyyy-mm-dd’) ;

• 按分区键

select count(*) from dh.tb_part_range where ORDER_DATE =to_date(‘2015-11-18’,‘yyyy-mm-dd’) ;

• 按索引顺序

select count(*) from dh.tb_part_range where ORDER_DATE =to_date(‘2015-11-18’,‘yyyy-mm-dd’) and CITY =‘SHANGHAI’ ;

• 非分区键

select count(*) from dh.tb_part_range where CITY =‘SHANGHAI’ ;

执行计划分别如下：

• 按索引顺序

-----------------------------------------------------------------------------------------------
| Id  | Operation                 | Name                     | Rows | Bytes | Cost | Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                          |    1 |    21 |    4 | 00:00:01 |
|   1 |   SORT AGGREGATE          |                          |    1 |    21 |      |          |
|   2 |    PARTITION RANGE SINGLE |                          |   81 |  1701 |    4 | 00:00:01 |
| * 3 |     INDEX RANGE SCAN      | LOCAL_PREFIXED_IND_RANGE |   81 |  1701 |    4 | 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 3 - access("ORDER_DATE"=TO_DATE(' 2015-11-18 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "CITY"='SHANGHAI')


Note
-----
- dynamic sampling used for this statement

• 按分区键

-----------------------------------------------------------------------------------------------
| Id  | Operation                 | Name                     | Rows | Bytes | Cost | Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                          |    1 |     9 |   25 | 00:00:01 |
|   1 |   SORT AGGREGATE          |                          |    1 |     9 |      |          |
|   2 |    PARTITION RANGE SINGLE |                          | 1000 |  9000 |   25 | 00:00:01 |
| * 3 |     INDEX RANGE SCAN      | LOCAL_PREFIXED_IND_RANGE | 1000 |  9000 |   25 | 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 3 - access("ORDER_DATE"=TO_DATE(' 2015-11-18 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))


Note
-----
- dynamic sampling used for this statement

• 按索引逆序

-----------------------------------------------------------------------------------------------
| Id  | Operation                 | Name                     | Rows | Bytes | Cost | Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                          |    1 |    21 |    4 | 00:00:01 |
|   1 |   SORT AGGREGATE          |                          |    1 |    21 |      |          |
|   2 |    PARTITION RANGE SINGLE |                          |   81 |  1701 |    4 | 00:00:01 |
| * 3 |     INDEX RANGE SCAN      | LOCAL_PREFIXED_IND_RANGE |   81 |  1701 |    4 | 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 3 - access("ORDER_DATE"=TO_DATE(' 2015-11-18 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "CITY"='SHANGHAI')


Note
-----
- dynamic sampling used for this statement

• 非分区键

--------------------------------------------------------------------------------------------------
| Id  | Operation                | Name                     | Rows   | Bytes   | Cost | Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                          |      1 |      12 | 2246 | 00:00:27 |
|   1 |   SORT AGGREGATE         |                          |      1 |      12 |      |          |
|   2 |    PARTITION RANGE ALL   |                          | 174017 | 2088204 | 2246 | 00:00:27 |
| * 3 |     INDEX FAST FULL SCAN | LOCAL_PREFIXED_IND_RANGE | 174017 | 2088204 | 2246 | 00:00:27 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 3 - filter("CITY"='SHANGHAI')


Note
-----
- dynamic sampling used for this statement

（3）结论

• 按索引顺序

• 按分区键

• 按索引逆序

• 非分区键

四个查询都走前缀索引

前三个有分区关键字：INDEX RANGE SCAN

最后一个无分区关键字：INDEX FAST FULL SCAN

三、RANGE分区表非缀索引测试

（1）索引定义

先删除前缀索引

创建局部非前缀索引；分区键未作为索引定义的第一列

SQL> drop index local_prefixed_ind_range;

Index dropped


Executed in 0.133 seconds


create index local_nonprefixed_ind_range on tb_part_range (CITY, ORDER_DATE) local;
SQL> create index local_nonprefixed_ind_range on tb_part_range (CITY, ORDER_DATE) local;

Index created


Executed in 1.283 seconds

（2）索引测试

测试四个情况：

• 按索引逆序

select count(*) from dh.tb_part_range where CITY =‘SHANGHAI’ and ORDER_DATE =to_date(‘2015-11-18’,‘yyyy-mm-dd’) ;

• 按分区键

select count(*) from dh.tb_part_range where ORDER_DATE =to_date(‘2015-11-18’,‘yyyy-mm-dd’) ;

• 按索引顺序

select count(*) from dh.tb_part_range where ORDER_DATE =to_date(‘2015-11-18’,‘yyyy-mm-dd’) and CITY =‘SHANGHAI’ ;

• 非分区键

select count(*) from dh.tb_part_range where CITY =‘SHANGHAI’ ;

执行计划分别如下：

• 按索引逆序

--------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name                        | Rows | Bytes | Cost | Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                             |    1 |    21 |   75 | 00:00:01 |
|   1 |   SORT AGGREGATE          |                             |    1 |    21 |      |          |
|   2 |    PARTITION RANGE SINGLE |                             |   81 |  1701 |   75 | 00:00:01 |
| * 3 |     INDEX RANGE SCAN      | LOCAL_NONPREFIXED_IND_RANGE |   81 |  1701 |   75 | 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 3 - access("CITY"='SHANGHAI' AND "ORDER_DATE"=TO_DATE(' 2015-11-18 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))


Note
-----
- dynamic sampling used for this statement

• 按分区键

------------------------------------------------------------------------------------
| Id  | Operation                 | Name          | Rows | Bytes | Cost | Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |               |    1 |     9 | 1189 | 00:00:15 |
|   1 |   SORT AGGREGATE          |               |    1 |     9 |      |          |
|   2 |    PARTITION RANGE SINGLE |               |  896 |  8064 | 1189 | 00:00:15 |
| * 3 |     TABLE ACCESS FULL     | TB_PART_RANGE |  896 |  8064 | 1189 | 00:00:15 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 3 - filter("ORDER_DATE"=TO_DATE(' 2015-11-18 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))


Note
-----
- dynamic sampling used for this statement

注意：TABLE ACCESS FULL ，没有走索引！！！

• 按索引顺序

--------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name                        | Rows | Bytes | Cost | Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                             |    1 |    21 |   75 | 00:00:01 |
|   1 |   SORT AGGREGATE          |                             |    1 |    21 |      |          |
|   2 |    PARTITION RANGE SINGLE |                             |   81 |  1701 |   75 | 00:00:01 |
| * 3 |     INDEX RANGE SCAN      | LOCAL_NONPREFIXED_IND_RANGE |   81 |  1701 |   75 | 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 3 - access("CITY"='SHANGHAI' AND "ORDER_DATE"=TO_DATE(' 2015-11-18 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))


Note
-----
- dynamic sampling used for this statement

• 非分区键

---------------------------------------------------------------------------------------------------
| Id  | Operation              | Name                        | Rows   | Bytes   | Cost | Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                             |      1 |      12 |  789 | 00:00:10 |
|   1 |   SORT AGGREGATE       |                             |      1 |      12 |      |          |
|   2 |    PARTITION RANGE ALL |                             | 174017 | 2088204 |  789 | 00:00:10 |
| * 3 |     INDEX RANGE SCAN   | LOCAL_NONPREFIXED_IND_RANGE | 174017 | 2088204 |  789 | 00:00:10 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 3 - access("CITY"='SHANGHAI')


Note
-----
- dynamic sampling used for this statement

（3）结论

• 按索引逆序

• 按分区键

• 按索引顺序

• 非分区键

1 3 4查询都走非前缀索引，INDEX RANGE SCAN

2查询TABLE ACCESS FULL ，没有走索引！！！

四、前缀和非前缀索引对比

前缀索引：

四个查询都走前缀索引

顺序、逆序、按分区查询，有分区关键字：INDEX RANGE SCAN

非分区键查询，无分区关键字：INDEX FAST FULL SCAN

非前缀索引：

顺序、逆序、非分区键查询都走非前缀索引，INDEX RANGE SCAN

非前缀索引按分区查询：TABLE ACCESS FULL ，没有走索引！！！