全局索引和本地索引分析

本地索引是分區(qū)的，而且是根據(jù)分區(qū)表的分區(qū)鍵值來對應(yīng)，就是分區(qū)表的每個(gè)分區(qū)都有對應(yīng)的分區(qū)索引，而全局索引可以分區(qū)也可以不分區(qū)，全局索引對應(yīng)的表可以是分區(qū)表也可以不是分區(qū)表。

比如這里t_global01是heap table并不是partition table
SQL> create table t_global01 as select * from dba_objects;

Table created.
SQL> CREATE INDEX index_t_objid
2 ON t_global01 (object_id) global
3 PARTITION BY RANGE(object_id)
4 (PARTITION p1 VALUES LESS THAN(10000),
5 PARTITION p2 VALUES LESS THAN(20000),
6 PARTITION pmax VALUES LESS THAN(MAXVALUE));

Index created.

索引又可以分為前綴索引和非前綴索引，前綴索引是指索引的分區(qū)鍵包含在索引中，并且是索引的前導(dǎo)列，而非前綴索引則是分區(qū)鍵不在索引中或者不是索引的前導(dǎo)列，本地索引可以建立前綴索引和非前綴索引，而全局索引只能建立前綴索引。

SQL> create table t_local01 partition by range(object_id)
2 (partition p1 values less than(10000),
3 partition p2 values less than(20000),
4 partition p3 values less than(30000),
5 partition p4 values less than(40000),
6 partition p5 values less than(maxvalue))
7 as select * from dba_objects;

Table created.

建立本地的前綴索引
SQL> CREATE INDEX index_t_pre01 on t_local01(object_id,object_name) local;

Index created.

建立本地的非前綴索引
SQL> CREATE INDEX index_t_nonpre01 on t_local01(object_name) local;

Index created.

全局索引不允許建立非前綴索引
SQL> create index ind_t_objid_nonpre on t_local01(object_id) global
2 partition by range(data_object_id)
3 (partition p1 values less than(10000),
4 partition pmax values less than(maxvalue));
partition by range(data_object_id)
*
ERROR at line 2:
ORA-14038: GLOBAL partitioned index must be prefixed

分區(qū)表的全局索引可能會(huì)因?yàn)榉謪^(qū)表的ddl而導(dǎo)致全局索引失效，這個(gè)需要我們特別注意，一般來說oltp建立全局索引，而在olap系統(tǒng)建立本地索引。

Partitioned Indexes: Global, Local, Prefixed and Non-Prefixed (文檔 ID 69374.1)
To illustrate the usefulness of global indexes, imagine that we have a large
fact table partitioned on a DATE column. We frequently need to search the table
on a VARCHAR2 column (VCOL) which is not part of the table's partition key.
Assume that there are currently 12 partitions in the table.

We could use 2 possible methods:

A local non-prefixed index on VCOL:

| |
------- -------
| | (10 more | |
Values: A.. Z.. partitions here) A.. Z..

Or a global prefixed index on VCOL:

| |
------- -------
| | (10 more | |
Values: A.. D.. partitions here) T.. Z..

A global prefixed index would usually be the best choice for a unique index on
our example VCOL column. For nonunique indexes, the issue is whether we can use
parallel index searches (local non-prefixed) or whether we need a serial search,
even at the expense of the greater maintenance problems of global indexes.

這里提出了對于唯一列建立全局索引較合適

Common Questions on Indexes on Partitioned Table (文檔 ID 1481609.1)記錄了local index和global index的適用特點(diǎn)
What are the performance implications of local indexes
Partition elimination/pruning during SQLs against the partitioned table with predicate on the partition key (prefixed more often allows for partition elimination than non prefixed).
這里提出了如果查詢條件中有分區(qū)鍵，建立本地索引可以讓分區(qū)裁剪生效（前綴索引通常比非前綴索引更容易發(fā)生分區(qū)裁剪）

Non prefixed local index is useful if it is important to have quick access according to a column which is not the partition key (e.g. look up for value account_number column, hence the account_number is placed as a leading column of the index), while it is also important to have the index equipartitioned with the table e.g. to support the time interval for rolling out old data and rolling in new data (e.g. partition key is time_id column, rolling out/in data is done by partition maintenance commands). This scenario often happens in historical databases.

而非前綴索引通常在查詢中沒有分區(qū)鍵過濾時(shí)比較適用。

下面來通過測試來看看上面文章提供的結(jié)論：
SQL> create table tab01
2 partition by range(object_id)
3 (partition p1 values less than(10000),
4 partition p2 values less than(20000),
5 partition p3 values less than(30000),
6 partition p4 values less than(40000),
7 partition p5 values less than(maxvalue))
8 as
9 select * from dba_objects;

Table created.

SQL> create index ind_type_local_pre on tab01(object_id,object_type) local;

Index created.

SQL> create index ind_type_local_nonpre on tab01(object_type) local;

Index created.
SQL> analyze table tab01 compute statistics;

Table analyzed.

上面已經(jīng)建立了前綴和非前綴的本地索引，然后如果我們的查詢中沒有分區(qū)鍵，那么看看兩個(gè)索引的實(shí)用性
SQL> select /*+index(tab01 ind_type_local_nonpre)*/* from tab01 where object_typ
e='INDEX';

1726 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 4022647995

--------------------------------------------------------------------------------
--------------------------------------------
| Id | Operation | Name | Rows | Byt
es | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------
--------------------------------------------
| 0 | SELECT STATEMENT | | 1481 | 1
27K| 74 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE ALL | | 1481 | 1
27K| 74 (0)| 00:00:01 | 1 | 5 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| TAB01 | 1481 | 1
27K| 74 (0)| 00:00:01 | 1 | 5 |
|* 3 | INDEX RANGE SCAN | IND_TYPE_LOCAL_NONPRE | 1481 |
| 10 (0)| 00:00:01 | 1 | 5 |
--------------------------------------------------------------------------------
--------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("OBJECT_TYPE"='INDEX')

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
353 consistent gets
0 physical reads
0 redo size
87737 bytes sent via SQL*Net to client
1757 bytes received via SQL*Net from client
117 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1726 rows processed

SQL> select /*+index(tab01 ind_type_local_pre)*/* from tab01 where object_type='
INDEX';

1726 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1706313756

--------------------------------------------------------------------------------
-----------------------------------------
| Id | Operation | Name | Rows | Bytes
| Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------
-----------------------------------------
| 0 | SELECT STATEMENT | | 1481 | 127K
| 198 (1)| 00:00:03 | | |
| 1 | PARTITION RANGE ALL | | 1481 | 127K
| 198 (1)| 00:00:03 | 1 | 5 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| TAB01 | 1481 | 127K
| 198 (1)| 00:00:03 | 1 | 5 |
|* 3 | INDEX FULL SCAN | IND_TYPE_LOCAL_PRE | 1481 |
| 176 (1)| 00:00:03 | 1 | 5 |
--------------------------------------------------------------------------------
-----------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("OBJECT_TYPE"='INDEX')
filter("OBJECT_TYPE"='INDEX')

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
521 consistent gets
174 physical reads
0 redo size
87699 bytes sent via SQL*Net to client
1757 bytes received via SQL*Net from client
117 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1726 rows processed

這里看來對于本地索引，在查詢條件中沒有分區(qū)鍵時(shí)非前綴索引比較實(shí)用。

而如果有分區(qū)鍵的查詢，本地索引是可以走分區(qū)裁剪的
SQL> select /*+index(tab01 ind_type_local_pre)*/* from tab01 where object_type='
INDEX' and object_id<10000;

920 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 4238522555

--------------------------------------------------------------------------------
-----------------------------------------
| Id | Operation | Name | Rows | Bytes
| Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------
-----------------------------------------
| 0 | SELECT STATEMENT | | 281 | 21075
| 34 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 281 | 21075
| 34 (0)| 00:00:01 | 1 | 1 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| TAB01 | 281 | 21075
| 34 (0)| 00:00:01 | 1 | 1 |
|* 3 | INDEX RANGE SCAN | IND_TYPE_LOCAL_PRE | 281 |
| 30 (0)| 00:00:01 | 1 | 1 |
--------------------------------------------------------------------------------
-----------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("OBJECT_TYPE"='INDEX' AND "OBJECT_ID"<10000)
filter("OBJECT_TYPE"='INDEX')

Statistics
----------------------------------------------------------
244 recursive calls
0 db block gets
244 consistent gets
0 physical reads
0 redo size
45241 bytes sent via SQL*Net to client
1163 bytes received via SQL*Net from client
63 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
920 rows processed

SQL> select /*+index(tab01 ind_type_local_nonpre)*/* from tab01 where object_typ
e='INDEX' and object_id<10000;

920 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1322437935

--------------------------------------------------------------------------------
--------------------------------------------
| Id | Operation | Name | Rows | Byt
es | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------
--------------------------------------------
| 0 | SELECT STATEMENT | | 281 | 210
75 | 21 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 281 | 210
75 | 21 (0)| 00:00:01 | 1 | 1 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| TAB01 | 281 | 210
75 | 21 (0)| 00:00:01 | 1 | 1 |
|* 3 | INDEX RANGE SCAN | IND_TYPE_LOCAL_NONPRE | 281 |
| 1 (0)| 00:00:01 | 1 | 1 |
--------------------------------------------------------------------------------
--------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("OBJECT_TYPE"='INDEX')

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
182 consistent gets
0 physical reads
0 redo size
45241 bytes sent via SQL*Net to client
1163 bytes received via SQL*Net from client
63 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
920 rows processed

這里我們看出執(zhí)行計(jì)劃中都出現(xiàn)了PARTITION RANGE SINGLE單個(gè)分區(qū)掃描（pstart和pstop都是1），這個(gè)是因?yàn)閳?zhí)行計(jì)劃的INDEX RANGE SCAN索引范圍掃描時(shí)pstart 1和pstop 1，此時(shí)索引掃描就只會(huì)掃描指定的索引分區(qū)，這個(gè)也就是索引的分區(qū)裁剪，當(dāng)然還有表的分區(qū)裁剪，關(guān)于分區(qū)裁剪的內(nèi)容小魚后面有時(shí)間會(huì)列出來單獨(dú)討論。

而如果是全局索引，索引默認(rèn)不分區(qū)，所以也就無法發(fā)生索引的分區(qū)裁剪：
SQL> drop index ind_type_local_nonpre；

Index dropped.

SQL> create index ind_type_global on tab01(object_type) global;

Index created.

SQL> select /*+index(tab01 ind_type_global)*/* from tab01 where object_type='IND
EX' and object_id<10000;

920 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3954671853

--------------------------------------------------------------------------------
--------------------------------------
| Id | Operation | Name | Rows | Bytes | C
ost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------
--------------------------------------
| 0 | SELECT STATEMENT | | 281 | 21075 |
69 (0)| 00:00:01 | | |
|* 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| TAB01 | 281 | 21075 |
69 (0)| 00:00:01 | 1 | 1 |
|* 2 | INDEX RANGE SCAN | IND_TYPE_GLOBAL | 1481 | |
5 (0)| 00:00:01 | | |
--------------------------------------------------------------------------------
--------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_ID"<10000)
2 - access("OBJECT_TYPE"='INDEX')

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
185 consistent gets
6 physical reads
0 redo size
45241 bytes sent via SQL*Net to client
1163 bytes received via SQL*Net from client
63 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
920 rows processed

What are the performance implications of global indexes?
Global index can be useful where rapid access, data integrity, and availability are important. In an OLTP system, a table may be partitioned by one key, for example, the employees.department_id column, but an application may need to access the data with many different keys, for example, by employee_id or job_id. Global indexes can be useful in this scenario as global indexes are prefixed and can provide better performance than local nonprefixed indexes because they minimize the number of index partition probes (cf. local prefixed more often allows for partition elimination than non prefixed mentioned in the previous section).

全局索引多用于OLTP系統(tǒng)，可以快速的返回查詢的數(shù)據(jù)，特別適用于查詢條件中不包含分區(qū)鍵的查詢，這種情況全局索引相比本地索引更加高效。

Global indexes are harder to manage than local indexes. At partition maintenance of the table, all partitions of a global index are affected.

這里提出全局索引難以維護(hù)，如果分區(qū)修改了，所有分區(qū)的索引都會(huì)影響

Partition elimination/pruning during SQLs against the partitioned table: prefixed - always allows for partition elimination.

同樣全局索引也是可以發(fā)生分區(qū)裁剪的

SQL> create table t_global01 as select * from dba_objects;

Table created.
SQL> CREATE INDEX index_t_objid
2 ON t_global01 (object_id) global
3 PARTITION BY RANGE(object_id)
4 (PARTITION p1 VALUES LESS THAN(10000),
5 PARTITION p2 VALUES LESS THAN(20000),
6 PARTITION pmax VALUES LESS THAN(MAXVALUE));

Index created.
SQL> select * from t_global01 where object_id<10000;

9568 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1223163610

--------------------------------------------------------------------------------
------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CP
U)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------
------------------------------
| 0 | SELECT STATEMENT | | 9194 | 897K| 177 (
0)| 00:00:03 | | |
| 1 | PARTITION RANGE SINGLE | | 9194 | 897K| 177 (
0)| 00:00:03 | 1 | 1 |
| 2 | TABLE ACCESS BY INDEX ROWID| T_GLOBAL01 | 9194 | 897K| 177 (
0)| 00:00:03 | | |
|* 3 | INDEX RANGE SCAN | INDEX_T_OBJID | 9194 | | 43 (
0)| 00:00:01 | 1 | 1 |
--------------------------------------------------------------------------------
------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("OBJECT_ID"<10000)

這里看出也發(fā)生了所謂的分區(qū)裁剪，index range scan的pstart和pstop都是1，說明是掃描了索引的一個(gè)分區(qū)，這也和上面partition range single相對應(yīng)（出現(xiàn)partition range single并不一定表示該表是分區(qū)表，有可能有分區(qū)的索引）

The hash index partitioning can improve performance of indexes where a small number leaf blocks in the index have high contention in multiuser OLTP environment. In some OLTP applications, index insertions happen only at the right edge of the index. This situation could occur when the index is defined on monotonically increasing columns (e.g. column value is populated by a sequence). In such situations, the right edge of the index becomes a hotspot because of contention for index pages, buffers, latches for update, and additional index maintenance activity, which results in performance degradation.

這里提出了一個(gè)hash index partition，在高并發(fā)情況下，索引的數(shù)據(jù)會(huì)不停往右邊傾斜（比如列是序列填充時(shí)），這種情況下索引右邊葉塊會(huì)成為熱點(diǎn)塊，造成大量的buffer latches競爭和額外的維護(hù)（比如索引分裂）而導(dǎo)致性能下降。

關(guān)于本地索引和全局索引小魚也沒有較多的實(shí)戰(zhàn)案例，個(gè)人而言小魚維護(hù)的大多是OLTP系統(tǒng)，所以一般都是建立的全局索引，可以參考以下建立：
Global index和local index適用范圍
non-prefixed Local indexes特別適用于基于歷史數(shù)據(jù)查詢分析的數(shù)據(jù)庫，在這樣的數(shù)據(jù)庫中，歷史數(shù)據(jù)一般都是根據(jù)時(shí)間來分區(qū)的。

prefixed Local index適用于對分區(qū)主鍵進(jìn)行索引，可以明顯減少查詢所搜索到的分區(qū)數(shù)目，極大的加快查詢速度。