转载：http://space.itpub.net/17203031/viewspace-713129
分区技术（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 Database10gEnterpriseEdition Release10.2.0.1.0 - Prod
PL/SQL Release10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version10.2.0.1.0 - Production
NLSRTL Version10.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，条件相同，不同在于条件的取值不同，统计量引起计算成本有差别。最终造成选择出的执行计划有差异。
下面，我们继续讨论当有分区索引的时候，执行计划是如何进行选取？
分区索引（Partition Index）与SQL执行计划（中）
上一篇 / 下一篇 2011-12-08 21:34:38 / 个人分类：Oracle性能优化
上篇(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的作用和效果。
分区索引（Partition Index）与SQL执行计划（下）
上一篇 / 下一篇 2011-12-11 23:24:05 / 个人分类：Oracle性能优化
上面，我们讨论了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 globalpartitionby 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关键字。在本系列的开篇，我们就已经分清了local和global的区别和标准。这里，我们建立了三个索引分区，与数据表的四个分区首先就是不同的，所以这里我们只能使用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 | PARTITIONRANGESINGLE | | 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 | PARTITIONRANGESINGLE | | 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 | PARTITIONRANGESINGLE | | 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 | PARTITIONRANGESINGLE | | 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 | PARTITIONRANGESINGLE | | 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 | PARTITIONRANGESINGLE | | 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 | PARTITIONRANGESINGLE | | 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、结论
分治是一种古老的考虑方法，将复杂问题进行简化，大面积的削减备选集合，是很多现代算法和现代系统设计的核心要义。
分区索引是我们在设计分区表中不能回避的问题。针对不同的应用场景，选择合适的分区索引，才能做到真正的设计优化。
898
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
