ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 分区索引(Partition Index)与SQL执行计划(上)

分区索引(Partition Index)与SQL执行计划(上)

原创 Linux操作系统 作者:realkid4 时间:2011-12-07 22:15:48 0 删除 编辑

 

分区技术(Partition)是Oracle8版本开始推出的一个新技术。经历若干年的发展,分区技术不断成熟、在Oracle各产品线中处的地位愈发重要。从技术方案布局上看,分区技术还是属于Oracle VLDBVery 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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
求道~

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7566651