本文探讨了在数据库中使用分区索引进行查询优化的方法，包括如何选择合适的分区键、分区类型以及不同查询条件下的执行计划变化。通过实例展示了分区索引如何影响查询性能，并对比了全局索引与局部索引的使用场景。
上面，我们讨论了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关键字。在本系列的开篇，我们就已经分清了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 | 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/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/17203031/viewspace-713129/
扫一扫
432
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
