ITPub博客

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

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

原创 Linux操作系统 作者:realkid4 时间:2011-12-08 21:34:38 0 删除 编辑

上篇(http://space.itpub.net/17203031/viewspace-712904)中,我们着重讨论了分区技术和全局索引在分区表中的一些现象。注意,我们讨论的是无前导索引,那么如果是有前导prefix索引,现象是如何的呢?

 

 

SQL> drop index idx_t_id;

Index dropped

 

--分区键单独作为前导列;

SQL> create index idx_t_owner on t(owner);

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                                 INDEX                 3145728

 

 

此时,我们观察一下三个应用场景的执行计划。

 

 

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

 

 

上面的一种SQL场景,是我们设计Oracle分区表和分区索引的一种大忌!如果上面的SQL是该数据表的一种主要访问方式,我们可以认为该设计是存在瑕疵和潜在性能问题的。

 

如果一个分区表,没有进行分区裁剪的进行全表扫描,要跨过多个分区进行操作。这样的总成本消耗量要远远大于不进行分区操作。所以,如果对一个分区表进行操作,要优先将分区键作为检索条件的一部分,利用上分区裁剪特性!

 

在上面的SQL中,object_id=1000既不是分区键,也不是索引列。所以,只能对所有分区进行检索(PARTITION LIST ALL),分区内部进行全表扫描。

 

 

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

 

 

当全局Global索引,以分区键作为索引的时候,如果检索条件中出现分区键等于条件,那么直接分区定位检索就会出现在成本很低的执行计划中。就如同上面的两个SQL执行计划,优先进行分区定位,之后在进行分区内部的检索。

 

从上面的三个场景,我们可以得到一部分的结论:如果分区表,同时使用分区键作为Global索引的索引列时,通常索引是在执行计划中不会发生很重要的作用的。而且当SQL条件中没有出现分区键,执行计划是存在很大的性能风险的。

 

 

那么,我们换一下带prefix的组合索引,观察一下执行情况如何?

 

 

SQL> drop index idx_t_owner;

Index dropped

 

--以分区键作为前导列,结合关键搜索条件;

SQL> create index idx_t_ownerid on t(owner,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_OWNERID';

 

SEGMENT_NAME         PARTITION_NAME          SEGMENT_TYPE    BYTES

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

IDX_T_OWNERID                                    INDEX            4194304

 

 

此时,我们看一下三个关键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: 3525553998

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

| Id  | Operation                          | Name          | Rows  | Bytes | Cos

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

|   0 | SELECT STATEMENT                   |               |     2 |   188 |

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

|*  2 |   INDEX SKIP SCAN                  | IDX_T_OWNERID |     2 |       |

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

Predicate Information (identified by operation id):

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

   2 - access("OBJECT_ID"=1000)

       filter("OBJECT_ID"=1000)

 

15 rows selected

 

 

当分区键不出现,同时存在object_id包含的索引时,索引路径是执行计划中必然出现的一种。注意:由于object_id在索引中位于非前导列的位置,所以此处Oracle的执行计划是进行index skip scan跳跃式检索

 

下面两个是带有分区键的数据SQL

 

 

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

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

| Id  | Operation                          | Name          | Rows  | Bytes | Cos

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

|   0 | SELECT STATEMENT                   |               |     2 |   186 |

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

|*  2 |   INDEX RANGE SCAN                 | IDX_T_OWNERID |     5 |       |

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

Predicate Information (identified by operation id):

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

   2 - access("OWNER"='SYS' AND "OBJECT_ID"=1000)

 

14 rows selected

 

 

两句带分区键的“同型异值”SQL又出现了执行计划不相同的情况。我们分别进行说明。

 

在第一个SQLowner=’SCOTT’)中,由于scott分区很小,一次性的分区裁剪可以减少很多的麻烦。同时,即使使用索引,Global对应的索引结构体积甚至可能会超过scott分区的大小,这样还不如进行FTS全表扫描操作的成本更可以接受。

 

第二个SQL中,有所不同,因为SYS分区体积很大,超过了索引结构。这样,Oracle放弃了分区裁剪的路径,直接选择直接的索引树定位结果rowid的方法。

 

也就是说,当全局索引中,采用prefix方式的组合索引列时,分区裁剪和索引路径难以共存。

 

总结论是:在分区表情况下,Global索引有无prefix意义不大。

 

 

下面我们来看看分区索引的情况。分区索引默认的方式我们选择Local,也就是与数据表分区对应的方式。

 

 

SQL> drop index IDX_T_OWNERID;

Index dropped

 

--加入local关键字之后,表明建立Local分区索引;

SQL> create index IDX_T_ID on t(object_id) 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_ID';

 

SEGMENT_NAME   PARTITION_NAME           SEGMENT_TYPE      BYTES

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

IDX_T_ID             T_LIST_SYS            INDEX PARTITION        917504

IDX_T_ID             T_LIST_SCOTT         INDEX PARTITION         65536

IDX_T_ID             T_LIST_HR             INDEX PARTITION         65536

IDX_T_ID             T_LIST_OTHERS        INDEX PARTITION       2097152

 

 

注意:所谓的分区索引,就是索引段(Index Segment)也被分割为多个段对象。如果我们不指定索引分区键,那么采用的分区方式同分区表的方式是相同的。

 

那么,我们一起看看分区索引的效果。

 

 

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

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

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

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

|   0 | SELECT STATEMENT                   |          |     2 |   188 |     7

|   1 |  PARTITION LIST ALL                |          |     2 |   188 |     7

|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| T        |     2 |   188 |     7

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

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

Predicate Information (identified by operation id):

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

   3 - access("OBJECT_ID"=1000)

15 rows selected

 

 

当使用object_id=1000时,执行计划要注意两个细节。首先,由于分区键owner没有出现的SQL语句中,无论是分区数据表还是分区索引,都无法进行分区裁剪操作的。

 

此处,Oracle CBO选择出的最优执行计划:首先是检索每一个索引分区,也就是其中的PARTITION LIST ALL,在每个分区中检索object_id=1000的条件,进行Index Range Scan。将查询出符合条件的rowid返回,进行回表操作Table Access By Local 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: 4224690800

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

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

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

|   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_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: 4224690800

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

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

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

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

|   1 |  PARTITION LIST SINGLE             |          |     2 |   186 |     3

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

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

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

Predicate Information (identified by operation id):

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

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

   3 - access("OBJECT_ID"=1000)

 

16 rows selected

 

 

注意,虽然scott分区同sys分区数据差异值很大,但是执行计划是相同的。这个是我们所追求的现象。因为,如果该SQL是使用绑定变量,对应最有执行计划不同,带来的Bind Peeking风险是不能忽视的。

 

执行计划中,显然进行了分区裁剪动作。这里的两句SQL中,均出现了分区键owner条件,这里面的分区裁剪,是索引分区的选取裁剪过程,而不是数据表分区裁剪过程。

 

在每一个分区索引中,对应使用object_id=1000查询条件,从局部索引的角度进行回表操作。

 

下篇中,我们将研究prefixLocal Index的作用和效果。

 

 

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

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

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7568756