上篇(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又出现了执行计划不相同的情况。我们分别进行说明。
在第一个SQL（owner=’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查询条件，从局部索引的角度进行回表操作。
下篇中，我们将研究prefix在Local Index的作用和效果。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17203031/viewspace-712989/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/17203031/viewspace-712989/
扫一扫
1744
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
