分区索引（Partition Index）与SQL执行计划（上）

 

分区技术（Partition）是Oracle从8版本开始推出的一个新技术。经历若干年的发展，分区技术不断成熟、在Oracle各产品线中处的地位愈发重要。从技术方案布局上看，分区技术还是属于Oracle VLDB（Very Large Database）解决方案。

 

1、从分区Partition技术到分区索引

 

就分区技术的目的，Oracle是要实现三个优势目标，归纳起来就是管理和性能：

 

ü        优化管理：分区技术主要解决的还是管理问题。一个很大的数据对象，如果数据活跃程度或者处理存在活跃差异，就可以将其分割在不同的分区。这样，在进行分区管理，如删除、新增加、移动等操作的时候，就不会影响到其他分区；

ü        数据库可用性提升：一般海量数据表，存在更大的坏块风险和I/O均衡压力，我们可以将分区放置在不同的位置上。这样，分区之间相互不影响，如果一个分区有故障，其他分区的访问仍能正常进行；

ü        性能提升：这里说的性能提升指的是分区裁剪（Partition Pruning）。在业务合理的情况下，当SQL条件where中出现适当的分区条件，那么执行过程只会对特定的分区进行操作，这样可以大大提高执行性能；

 

对分区技术，我们要重点关注两个问题：

 

ü        数据是不是海量，完全不是我们规划分区的根本理由。确定分区技术的关键在于数据对象在业务操作上是否有分区特性。应用分区技术之后，可否提升分区效率；

ü        分区表不是唯一可分区的对象。索引Index也是具有分区属性的，而且索引的分区与对应数据表是否分区无关；

 

本篇，我们一起对分区索引（Partition Index）进行简单的分析研究，探讨应用的场景和现象。

 

2、分区索引的类型

 

分区索引从分类上，有两个标准：局部（Local）和全局（Global）、前导（Prefix）和非前导（Non-Prefix）。

 

局部（Local）和全局（Global）是针对与对应的分区键而言的，如果一个分区索引符合下面的条件，就可以称为Local索引：

 

ü        与分区数据表具有相同数据的分区/子分区；

ü        与分区数据表具有相同的分区限制，也就是分区条件相同；

ü        与分区数据表具有相同的分区键；

 

如果一个分区索引不是Local的，那么就是Global的。

 

前导Prefix和非前导Non-Prefix是针对分区表的主键是否出现在索引index的左侧前导列中。如果出现，我们称之为Prefix Index，否则就是Non-Prefix Index。

 

针对不同的数据表（分区和非分区）和不同的数据访问方式，使用适当类型的索引，可以让CBO优化器获取到最好的执行效率。

 

下面，我们针对不同的数据表和索引类型，分析执行计划情况。

 

我们先根据分区表的情况进行分析。

 

3、分区表下的各类型索引情况

 

我们在10g下进行试验，针对的是分区表。

 

 

SQL> select * from v$version;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE     10.2.0.1.0       Production

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

 

SQL> create table t

  2  partition by list(owner)

  3  (

  4     partition t_list_sys values ('SYS'),

  5     partition t_list_scott values ('SCOTT'),

  6     partition t_list_hr values ('HR'),

  7     partition t_list_others values (default)

  8  )

  9  as select * from dba_objects where 1=0;

Table created

 

--选取分区键owner，划分分区；

SQL> insert into t  select * from dba_objects;

106610 rows inserted

 

SQL> commit;

Commit complete

 

 

此时，根据分区表特性，Oracle会创建出多个segment对象与数据表对应。

 

 

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

 

SQL> select segment_name, partition_name, segment_type, bytes from dba_segments where wner='SCOTT' and segment_name='T';

 

SEGMENT_NAME         PARTITION_NAME        SEGMENT_TYPE   BYTES

-------------------- ------------------------------ ------------------ ----------

T              T_LIST_SYS                     TABLE PARTITION       6291456

T              T_LIST_SCOTT                   TABLE PARTITION         65536

T              T_LIST_HR                      TABLE PARTITION         65536

T             T_LIST_OTHERS                  TABLE PARTITION       7340032

 

 

下面是索引，首先我们创建普通的全局索引，就是一个数据表对应一个索引类型。

 

 

SQL> create index idx_t_id on t(object_id);

Index created

 

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

 

SQL> select segment_name, partition_name,segment_type, bytes from dba_segments where wner='SCOTT' and segment_name='IDX_T_ID';

SEGMENT_NAME         PARTITION_NAME         SEGMENT_TYPE  BYTES

-------------------- ------------------------------ ------------------ ----------

IDX_T_ID                                           INDEX            3145728

 

 

下面分别寻找三个查询业务场景，对SQL语句执行计划进行检索。

 

 

SQL> explain plan for select * from t where object_id=1000;

Explained

 

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 2226237847

--------------------------------------------------------------------------------

| Id  | Operation                          | Name     | Rows  | Bytes | Cost (%C

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                   |          |     2 |   188 |     3

|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| T        |     2 |   188 |     3

|*  2 |   INDEX RANGE SCAN                 | IDX_T_ID |     2 |       |     1

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("OBJECT_ID"=1000)

14 rows selected

 

 

注意，这个执行计划值得关注的是数据表索引访问方式。条件object_id=1000，正好落在全局索引的叶子节点上，可以直接定位到条件的rowid。在根据rowid定位数据行的过程中，检索了数据表分区。这个操作就是根据全局索引返回rowid定位数据行过程，称作“TABLE ACCESS BY GLOBAL INDEX ROWID”。

 

 

 

SQL> explain plan for select * from t where object_id=1000 and wner='SCOTT';

Explained

 

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 882533222

--------------------------------------------------------------------------------

| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     | P

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT      |      |     1 |    85 |     3   (0)| 00:00:01 |

|   1 |  PARTITION LIST SINGLE|      |     1 |    85 |     3   (0)| 00:00:01 |

|*  2 |   TABLE ACCESS FULL   | T    |     1 |    85 |     3   (0)| 00:00:01 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter("OBJECT_ID"=1000)

14 rows selected

 

 

SQL> explain plan for select * from t where object_id=1000 and wner='SYS';

Explained

 

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 3317687338

--------------------------------------------------------------------------------

| Id  | Operation                          | Name     | Rows  | Bytes | Cost (%C

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                   |          |     2 |   186 |     6

|*  1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| T        |     2 |   186 |     6

|*  2 |   INDEX RANGE SCAN                 | IDX_T_ID |     5 |       |     1

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("OWNER"='SYS')

   2 - access("OBJECT_ID"=1000)

 

15 rows selected

 

 

上面两个SQL体现出CBO的运算规则和工作方式。

 

当查询条件为object_id=1000 and wner=’SCOTT’的时候，Oracle选择了先在一个分区上定位（owner=’SCOTT’），也就是“PARTITION LIST SINGLE”。定位之后，Oracle有两个选择，一个是搜索Global Index，依据条件object=1000。另一种是进行分区内全表扫描。

 

全局索引只是一个段segment对象，体积包括了所有的object_id值。进行检索消耗的成本要大于只对scott分区全表扫描的成本。所以此处，Oracle CBO选择了全表扫描子分区。

 

当查询条件为owner=’SYS’之后，事情有所不同。SYS分区大小超过全局索引大小。如果选择落入分区空间，之后全表扫描或者索引扫描，成本都是不容易接受的。所以，Oracle放弃了SYS条件，先搜索索引树，按照条件object_id=1000检索，最后根据全局索引返回的rowid，直接定位到结果行。

 

这两个SQL，条件相同，不同在于条件的取值不同，统计量引起计算成本有差别。最终造成选择出的执行计划有差异。

 

下面，我们继续讨论当有分区索引的时候，执行计划是如何进行选取？

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17203031/viewspace-712904/，如需转载，请注明出处，否则将追究法律责任。