上篇中我们讨论了单纯数据表段分区的行为特性和方式。本篇我们继续讨论如果有索引对象参与，会是如何呢？
3、全局索引Global Index情况
Global Index是分区表中使用的一种默认索引方案。对于分区的数据表，Global Index形成的是一个覆盖所有分区的索引树结构，对应的是一个段对象。
行业里面对于Global Index的“声誉”并不是很好。一般优化人员对于分区表索引，都是推荐使用Local Index，而非Global Index。这样的决策主要是针对两个方面的考量：管理方面和性能优化方面。管理方面主要是Global Index失效问题。Global Index覆盖到整个数据表所有分区，分区表进行归档销毁动作的时候，通常会选择分区摘除动作。Drop partition动作虽然很快，效果也很好，但是会直接导致global index的失效。Index的失效会直接引起系统性能下降。
在性能优化方面，Global Index和分区表是“相斥”的。SQL语句走分区裁剪，就不会选择Global Index。反之选择了Global Index，直接定位结果集合rowid，也就没有必要进行数据表分区裁剪。作为两个都需要消耗优化资源的方案，无论哪个路径，都需要“闲置”一种优化策略。这种的确是不能让优化人员接受的。
下面我们进行一次10046的执行计划。
SQL> create index idx_t_part_id on t_part(object_id);
Index created
SQL> exec dbms_stats.gather_table_stats(user,'T_PART',cascade => true);
PL/SQL procedure successfully completed
查看执行计划：
SQL> explain plan for select * from t_part where owner='SYS' and object_id=1000;
Explained
SQL> select * from table(dbms_xplan.display)
2 ;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2004327352
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cos
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 97 |
|* 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| T_PART | 1 | 97 |
|* 2 | INDEX RANGE SCAN | IDX_T_PART_ID | 1 | |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='SYS')
2 - access("OBJECT_ID"=1000)
15 rows selected
Oracle承认T_PART是一个分区表，索引IDX_T_PART是上面的Global Index。这个过程中没有进行分区裁剪，也就是直接利用Global Index路径进行检索。
下面我们通过10046来进行检查。
SQL> select value from v$diag_info where name='Default Trace File';
VALUE
-----------------------------------------
/u01/app/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_2035.trc
SQL> alter system flush shared_pool;
System altered.
SQL> alter system flush buffer_cache;
System altered.
Session level进行10046监控。
SQL> alter session set events='10046 trace name context forever, level 12';
Session altered.
SQL> select * from t_part where owner='SYS' and object_id=1000;
（结果略）
SQL> alter session set events='10046 trace name context off';
Session altered.
Trace文件中片段如下：
=====================
PARSING IN CURSOR #3075330936 len=58 dep=0 uid=0 oct=3 lid=0 tim=1403366711465450 hv=2083714074 ad='35d34494' sqlid='2kcw7yjy35x0u'
select * from t_part where owner='SYS' and object_id=1000
END OF STMT
PARSE #3075330936:c=243962,e=363698,p=33,cr=451,cu=0,mis=1,r=0,dep=0,og=1,plh=2004327352,tim=1403366711465446
EXEC #3075330936:c=0,e=92,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2004327352,tim=1403366711465912
WAIT #3075330936: nam='SQL*Net message to client' ela= 10 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1403366711466062
WAIT #3075330936: nam='db file sequential read' ela= 182 file#=1 block#=93129 blocks=1 obj#=87169 tim=1403366711466584
WAIT #3075330936: nam='db file sequential read' ela= 136 file#=1 block#=93132 blocks=1 obj#=87169 tim=1403366711466948
WAIT #3075330936: nam='db file sequential read' ela= 105 file#=1 block#=93837 blocks=1 obj#=87165 tim=1403366711467256
FETCH #3075330936:c=1000,e=1186,p=3,cr=3,cu=0,mis=0,r=1,dep=0,og=1,plh=2004327352,tim=1403366711467497
WAIT #3075330936: nam='SQL*Net message from client' ela= 716 driver id=1650815232 #bytes=1 p3=0 obj#=87165 tim=1403366711468310
FETCH #3075330936:c=0,e=18,p=0,cr=1,cu=0,mis=0,r=0,dep=0,og=1,plh=2004327352,tim=1403366711468571
STAT #3075330936 id=1 cnt=1 pid=0 pos=1 obj=87164 op='TABLE ACCESS BY GLOBAL INDEX ROWID T_PART PARTITION: 1 1 (cr=4 pr=3 pw=0 time=1235 us cost=2 size=97 card=1)'
STAT #3075330936 id=2 cnt=1 pid=1 pos=1 obj=87169 op='INDEX RANGE SCAN IDX_T_PART_ID (cr=3 pr=2 pw=0 time=771 us cost=1 size=0 card=1)'
WAIT #3075330936: nam='SQL*Net message to client' ela= 4 driver id=1650815232 #bytes=1 p3=0 obj#=87165 tim=1403366711469180
*** 2014-06-22 00:05:17.550
WAIT #3075330936: nam='SQL*Net message from client' ela= 6080882 driver id=1650815232 #bytes=1 p3=0 obj#=87165 tim=1403366717550106
CLOSE #3075330936:c=0,e=46,dep=0,type=0,tim=1403366717551418
=====================
传统的索引路径都是从索引段入手，访问到索引根节点块，之后逐层向下访问进行定位。这个过程进行的大都是单块读动作。从Trace File结果，Oracle首先进行了对file#=1 block#=93129 blocks=1，对象编号为87169。
SQL> col object_name for a30;
SQL> select object_name, subobject_name, object_type from dba_objects where object_id=87169;
OBJECT_NAME SUBOBJECT_ OBJECT_TYPE
------------------------------ ---------- -------------------
IDX_T_PART_ID INDEX
对对象87169，Oracle进行了两次单块访问。一次是段头块访问，另一次是定位到叶子节点。获取到符合条件的叶子节点rowid之后，就直接回表到数据表指定的块中。这也就是之后的第三次单块访问，编号为87165。
SQL> select object_name, subobject_name, object_type from dba_objects where object_id=87165;
OBJECT_NAME SUBOBJECT_ OBJECT_TYPE
------------------------------ ---------- -------------------
T_PART T_PART_P0 TABLE PARTITION
在这个过程中，我们没有看到分区裁剪，只看到了普通索引定位检索过程。我们说：对于全局索引和分区表而言，SQL执行计划是相斥的。这个实验中，我们看到了分区裁剪被闲置的情况，在另外的一些情况下，是可能出现索引被闲置的情况的。
4、局部Local Index
最后我们来看看Local Index的情况。和Global Index不同，Local Index是由两个特点值得关注。一个是索引段分区，从传统的一个单树结构，演变到多树多索引段的结构。另一个是分区策略，local索引的分区策略（分区种类和分区键）和数据表保持一致。
对于Local Index，要记住：除了索引中携带的索引字段信息外，还包括了分区键和分区条件。Local Index是能够利用分区裁剪和索引双重特性的解决方案。
下面我们创建本地索引。
SQL> drop index idx_t_part_id;
Index dropped
SQL> create index idx_t_part_id_local on t_part(object_id) local;
Index created
此时，索引段按照数据表段方式进行分区，分区名称和数据表分区相同。
SQL> select PARTITION_NAME, SEGMENT_TYPE, HEADER_FILE, HEADER_BLOCK, BYTES, BLOCKS, EXTENTS from dba_segments where owner='SYS' and segment_name='IDX_T_PART_ID_LOCAL';
PARTITION_ SEGMENT_TYPE HEADER_FILE HEADER_BLOCK BYTES BLOCKS EXTENTS
---------- ------------------ ----------- ------------ ---------- ---------- ----------
T_PART_P0 INDEX PARTITION 1 93128 720896 88 11
T_PART_P1 INDEX PARTITION 1 97952 65536 8 1
T_PART_P2 INDEX PARTITION 1 97960 65536 8 1
T_PART_P3 INDEX PARTITION 1 97968 917504 112 14
执行计划分析：
SQL> explain plan for select * from t_part where owner='SYS' and object_id=1000;
Explained
SQL> select * from table(dbms_xplan.display)
2 ;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2413422785
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 97
| 1 | PARTITION LIST SINGLE | | 1 | 97
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| T_PART | 1 | 97
|* 3 | INDEX RANGE SCAN | IDX_T_PART_ID_LOCAL | 1 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_ID"=1000)
15 rows selected
我们在执行计划中看到了分区裁剪动作，并且也看到了索引路径。这里一个问题：数据表和索引都分区了，这个裁剪裁剪的是谁？我们通过10046来进行验证。
SQL> select value from v$diag_info where name='Default Trace File';
VALUE
--------------------------------------
/u01/app/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_2165.trc
SQL> alter system flush shared_pool;
System altered.
SQL> alter system flush buffer_cache;
System altered.
开启10046检查。
SQL> alter session set events='10046 trace name context forever, level 12';
Session altered.
SQL> select * from t_part where owner='SYS' and object_id=1000;
SQL> alter session set events='10046 trace name context off';
Session altered.
对应的Trace File信息片段如下：
=====================
PARSING IN CURSOR #3074950008 len=57 dep=0 uid=0 oct=3 lid=0 tim=1403367710144497 hv=919498438 ad='2d9290f8' sqlid='2q5x87wvcwvq6'
select * from t_part where owner='SYS' and object_id=1000
END OF STMT
PARSE #3074950008:c=242963,e=404745,p=37,cr=462,cu=0,mis=1,r=0,dep=0,og=1,plh=2413422785,tim=1403367710144492
EXEC #3074950008:c=0,e=146,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2413422785,tim=1403367710144857
WAIT #3074950008: nam='SQL*Net message to client' ela= 7 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1403367710144940
WAIT #3074950008: nam='db file sequential read' ela= 152 file#=1 block#=93129 blocks=1 obj#=87172 tim=1403367710146094
WAIT #3074950008: nam='db file sequential read' ela= 149 file#=1 block#=93132 blocks=1 obj#=87172 tim=1403367710146520
WAIT #3074950008: nam='db file sequential read' ela= 373 file#=1 block#=93837 blocks=1 obj#=87165 tim=1403367710147080
FETCH #3074950008:c=999,e=1367,p=3,cr=3,cu=0,mis=0,r=1,dep=0,og=1,plh=2413422785,tim=1403367710147177
WAIT #3074950008: nam='SQL*Net message from client' ela= 760 driver id=1650815232 #bytes=1 p3=0 obj#=87165 tim=1403367710148032
FETCH #3074950008:c=0,e=22,p=0,cr=1,cu=0,mis=0,r=0,dep=0,og=1,plh=2413422785,tim=1403367710148339
STAT #3074950008 id=1 cnt=1 pid=0 pos=1 obj=0 op='PARTITION LIST SINGLE PARTITION: KEY KEY (cr=4 pr=3 pw=0 time=1453 us cost=2 size=97 card=1)'
STAT #3074950008 id=2 cnt=1 pid=1 pos=1 obj=87164 op='TABLE ACCESS BY LOCAL INDEX ROWID T_PART PARTITION: 1 1 (cr=4 pr=3 pw=0 time=1422 us cost=2 size=97 card=1)'
STAT #3074950008 id=3 cnt=1 pid=2 pos=1 obj=87171 op='INDEX RANGE SCAN IDX_T_PART_ID_LOCAL PARTITION: 1 1 (cr=3 pr=2 pw=0 time=804 us cost=1 size=0 card=1)'
WAIT #3074950008: nam='SQL*Net message to client' ela= 4 driver id=1650815232 #bytes=1 p3=0 obj#=87165 tim=1403367710148876
*** 2014-06-22 00:22:03.505
WAIT #3074950008: nam='SQL*Net message from client' ela= 13356053 driver id=1650815232 #bytes=1 p3=0 obj#=87165 tim=1403367723504972
CLOSE #3074950008:c=0,e=142,dep=0,type=0,tim=1403367723506903
=====================
Oracle直接定位到了object_id=87172，我们来查看一下这个对象是谁？
SQL> select object_name, subobject_name, object_type from dba_objects where object_id=87172;
OBJECT_NAME SUBOBJECT_ OBJECT_TYPE
------------------------------ ---------- -------------------
IDX_T_PART_ID_LOCAL T_PART_P0 INDEX PARTITION
是索引段的分区！说明Oracle进行本地索引分区的时候，是进行的索引段的分区裁剪。同标准分区裁剪相同，Oracle首先利用recursive call获取到分区、分区索引的基本元数据信息，其中最重要的是分区键和分区信息。这就提供了Oracle直接就访问到分区索引树的条件，数据段分区也就不需要了。
5、结论
分区表、Global Index和Local Index，是会影响到管理运维、性能优化的重要组合概念。在SQL层面，尽可能的利用已有的优化策略获取到最优的性能，是我们决策问题的出发点。所有的优化策略，都需要付出管理、性能的成本付出，以最少的付出，获取最大的综合性能，也就是我们优化人员的职分所在。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17203031/viewspace-1191341/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/17203031/viewspace-1191341/
384
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
