ITPub博客

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

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

原创 Linux操作系统 作者:realkid4 时间:2011-12-11 23:24:05 0 删除 编辑

 

 

上面,我们讨论了Local Index的分区特性。如果我们将分区键作为前缀加入到索引中,是什么现象呢?

 

 

SQL> drop index IDX_T_ID;

Index dropped

 

SQL> create index idx_t_owner on t(owner) local;

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_OWNER';

 

SEGMENT_NAME         PARTITION_NAME       SEGMENT_TYPE  BYTES

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

IDX_T_OWNER          T_LIST_SYS              INDEX PARTITION  917504

IDX_T_OWNER          T_LIST_SCOTT          INDEX PARTITION   65536

IDX_T_OWNER          T_LIST_HR             INDEX PARTITION         65536

IDX_T_OWNER          T_LIST_OTHERS        INDEX PARTITION       2097152

 

 

 

我们创建了索引idx_t_owner,选择分区键owner作为索引列。当选择Local类型时,也就意味着每个索引分区只包括相同的owner值。对应执行计划,效果如何呢?

 

 

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: 3845649146

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

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

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

|   0 | SELECT STATEMENT   |      |     2 |   188 |   351   (2)| 00:00:05 |

|   1 |  PARTITION LIST ALL|      |     2 |   188 |   351   (2)| 00:00:05 |

|*  2 |   TABLE ACCESS FULL| T    |     2 |   188 |   351   (2)| 00:00:05 |

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

Predicate Information (identified by operation id):

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

   2 - filter("OBJECT_ID"=1000)

14 rows selected

 

 

对于只包括object_id=1000的查询条件。无疑,Oracle会检索所有数据表分区,并且在每个分区中进行全表扫描。

 

 

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: 1903319282

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

| Id  | Operation                          | Name        | Rows  | Bytes | Cost

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

|   0 | SELECT STATEMENT                   |             |     1 |    85 |     2

|   1 |  PARTITION LIST SINGLE             |             |     1 |    85 |     2

|*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID| T           |     1 |    85 |     2

|*  3 |    INDEX RANGE SCAN                | IDX_T_OWNER |    50 |       |     1

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

Predicate Information (identified by operation id):

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

   2 - filter("OBJECT_ID"=1000)

   3 - access("OWNER"='SCOTT')

16 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: 882533222

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

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

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

|   0 | SELECT STATEMENT      |      |     2 |   186 |   154   (2)| 00:00:02 |

|   1 |  PARTITION LIST SINGLE|      |     2 |   186 |   154   (2)| 00:00:02 |

|*  2 |   TABLE ACCESS FULL   | T    |     2 |   186 |   154   (2)| 00:00:02 |

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

Predicate Information (identified by operation id):

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

   2 - filter("OBJECT_ID"=1000)

 

14 rows selected

 

两个同型SQL的执行计划完全不同。当owner=’SCOTT’时,Oracle会去检索索引分区,定位到owner=’SCOTT’的索引分区上,之后回表使用object_id=1000的条件检索。

 

但是,对于owner=’SYS’的语句,情况有所不同。由于该分区较大,所以Oracle选择直接去进行数据表分区的选取,最后在进行分区内部的全表扫描。

 

说明:我们说,在分区索引情况下,采用数据表分区键作为索引分区键,同时将分区键作为索引是没有什么意义的。

 

 

上面一直在讨论默认分区条件。我们在创建分区索引的时候,是可以选择非分区键作为索引分区键。

 

 

SQL> drop index IDX_T_OWNER;

Index dropped

 

SQL> create index idx_t_id on t(object_id)

  2  global partition by range(object_id)

  3  (

  4     partition t_list_01 values less than (30000),

  5     partition t_list_02 values less than (70000),

  6     partition t_list_other values less than (maxvalue)

  7  );

 

Index created

 

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

PL/SQL procedure successfully completed

 

 

上面代码中注意两个方面。一个是索引的global关键字。在本系列的开篇,我们就已经分清了localglobal的区别和标准。这里,我们建立了三个索引分区,与数据表的四个分区首先就是不同的,所以这里我们只能使用global关键字。

 

另一个就是我们可以自己选择索引分区的分区键以及分区类型。这里,我们就选择了object_id作为范围分区的分区键。

 

建立之后,可以清晰的看到分区段空间。

 

 

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             T_LIST_01            INDEX PARTITION       2097152

IDX_T_ID             T_LIST_02            INDEX PARTITION       1048576

IDX_T_ID             T_LIST_OTHER        INDEX PARTITION        196608

 

 

此时,我们的执行计划就按照如下的规则进行。

 

 

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: 3010955244

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

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

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

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

|   1 |  PARTITION RANGE SINGLE             |          |     2 |   188 |     3

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

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

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

Predicate Information (identified by operation id):

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

   3 - access("OBJECT_ID"=1000)

15 rows selected

 

 

Global索引使用对应的Table Access By Global Index Rowid,而Local索引使用对应Table Access By Local Index Rowid

 

从上面的执行计划看,进行了Partition Range Single操作,属于分区裁剪动作。在SQL中,没有涉及到owner的问题,所以这个剪裁动作必然是针对索引而言的。

 

 

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: 2862326821

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

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

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

|   0 | SELECT STATEMENT                    |          |     1 |    85 |     3

|   1 |  PARTITION RANGE SINGLE             |          |     1 |    85 |     3

|*  2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| T        |     1 |    85 |     3

|*  3 |    INDEX RANGE SCAN                 | IDX_T_ID |     1 |       |     1

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

Predicate Information (identified by operation id):

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

   2 - filter("OWNER"='SCOTT')

   3 - access("OBJECT_ID"=1000)

 

16 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: 2862326821

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

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

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

|   0 | SELECT STATEMENT                    |          |     2 |   186 |     3

|   1 |  PARTITION RANGE SINGLE             |          |     2 |   186 |     3

|*  2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| T        |     2 |   186 |     3

|*  3 |    INDEX RANGE SCAN                 | IDX_T_ID |     1 |       |     1

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

Predicate Information (identified by operation id):

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

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

   3 - access("OBJECT_ID"=1000)

 

16 rows selected

 

 

上面两个执行计划,显然是利用索引分区的裁剪,先定位到具体的索引分区,之后进行owner条件查询筛选。

 

 

4、非分区表的情况

 

并不是只有分区表才能对应分区索引。如果一个非分区表有需要,是可以将其索引分区的。

 

 

SQL> create table t as select * from dba_objects;

Table created

 

SQL> insert into t select * from t;

53295 rows inserted

 

SQL> commit;

Commit complete

 

SQL> create index idx_t_id on t(object_id)

  2  global partition by range(object_id)

  3  (

  4     partition t_list_01 values less than (30000),

  5     partition t_list_02 values less than (70000),

  6     partition t_list_other values less than (maxvalue)

  7  );

 

Index created

 

 

普通数据表T,对应分区数据索引。我们观察一下结果情况。

 

 

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             T_LIST_01         INDEX PARTITION       2097152

IDX_T_ID             T_LIST_02         INDEX PARTITION        851968

IDX_T_ID             T_LIST_OTHER    INDEX PARTITION        196608

 

 

三个关键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: 381899048

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

| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| T

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

|   0 | SELECT STATEMENT             |          |     2 |   188 |     3   (0)| 0

|   1 |  PARTITION RANGE SINGLE      |          |     2 |   188 |     3   (0)| 0

|   2 |   TABLE ACCESS BY INDEX ROWID| T        |     2 |   188 |     3   (0)| 0

|*  3 |    INDEX RANGE SCAN          | IDX_T_ID |     2 |       |     1   (0)| 0

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

Predicate Information (identified by operation id):

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

   3 - access("OBJECT_ID"=1000)

 

15 rows selected

 

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: 381899048

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

| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| T

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

|   0 | SELECT STATEMENT             |          |     1 |    94 |     3   (0)| 0

|   1 |  PARTITION RANGE SINGLE      |          |     1 |    94 |     3   (0)| 0

|*  2 |   TABLE ACCESS BY INDEX ROWID| T        |     1 |    94 |     3   (0)| 0

|*  3 |    INDEX RANGE SCAN          | IDX_T_ID |     2 |       |     1   (0)| 0

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

Predicate Information (identified by operation id):

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

   2 - filter("OWNER"='SCOTT')

   3 - access("OBJECT_ID"=1000)

 

16 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: 381899048

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

| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| T

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

|   0 | SELECT STATEMENT             |          |     1 |    94 |     3   (0)| 0

|   1 |  PARTITION RANGE SINGLE      |          |     1 |    94 |     3   (0)| 0

|*  2 |   TABLE ACCESS BY INDEX ROWID| T        |     1 |    94 |     3   (0)| 0

|*  3 |    INDEX RANGE SCAN          | IDX_T_ID |     2 |       |     1   (0)| 0

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

Predicate Information (identified by operation id):

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

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

   3 - access("OBJECT_ID"=1000)

 

16 rows selected

 

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: 381899048

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

| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| T

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

|   0 | SELECT STATEMENT             |          |     1 |    94 |     3   (0)| 0

|   1 |  PARTITION RANGE SINGLE      |          |     1 |    94 |     3   (0)| 0

|*  2 |   TABLE ACCESS BY INDEX ROWID| T        |     1 |    94 |     3   (0)| 0

|*  3 |    INDEX RANGE SCAN          | IDX_T_ID |     2 |       |     1   (0)| 0

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

Predicate Information (identified by operation id):

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

   2 - filter("OWNER"='SCOTT')

   3 - access("OBJECT_ID"=1000)

 

16 rows selected

 

 

索引分区裁剪、内部索引回表操作在上面的执行计划中体现的很明显。

 

5、结论

 

分治是一种古老的考虑方法,将复杂问题进行简化,大面积的削减备选集合,是很多现代算法和现代系统设计的核心要义。

 

分区索引是我们在设计分区表中不能回避的问题。针对不同的应用场景,选择合适的分区索引,才能做到真正的设计优化。

 

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

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

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7568826