建立檢視和淺析LOCAL INDEX和GLOBAL INDEX

1. 首先了解一下local index 和 global index的建立過程:

SQL> create table kl911_1 (no number, name varchar2(60))
2 partition by range (no)
3 (partition p1 values less than (10) pctfree 50,
4 partition p2 values less than (20) pctfree 50,
5 partition max_values values less than (maxvalue));

Table created.

SQL> create index idx_kl911_1 on kl911_1(no) local;

Index created.

SQL> select index_name from dba_ind_partitions where index_name='IDX_KL911_1';

INDEX_NAME
--------------------------------------------------------------------------------
IDX_KL911_1
IDX_KL911_1
IDX_KL911_1

SQL> select index_name, locality from dba_part_indexes where index_name='IDX_KL911_1';

INDEX_NAME
--------------------------------------------------------------------------------
LOCALITY
------------------
IDX_KL911_1
LOCAL

----- 如果什麼都不加，預設是Global index
SQL> create index idx_kl911_2 on kl911_1(name);

Index created.

SQL> select index_name from dba_ind_partitions where index_name='IDX_KL911_2';

no rows selected

SQL> select index_name, locality from dba_part_indexes where index_name='IDX_KL911_2';

no rows selected

SQL> select index_name from dba_indexes where index_name='IDX_KL911_2';

INDEX_NAME
--------------------------------------------------------------------------------
IDX_KL911_2

2. 接下來我想驗證一下為什麼對於分割槽表一定要用local index的benefit，聽說global index在每次交換分割槽以後需要重建，否則會出現錯誤提示:

SQL> select index_name from dba_indexes where table_name='KL911_1';

INDEX_NAME
--------------------------------------------------------------------------------
IDX_KL911_1
IDX_KL911_2

SQL> create table kl_temp (no number, name varchar2(60));

Table created.

SQL> insert into kl_temp values (15,'TOM');

1 row created.

SQL> insert into kl_temp values (14,'JON');

1 row created.

SQL> commit;

SQL> alter table kl911_1 exchange partition p2 with table kl_temp;

Table altered.

SQL> select * from kl911_1 partition (p2);

NO
----------
NAME
--------------------------------------------------------------------------------
15
TOM

14
JON

SQL> select * from kl911_1 where name='TOM';

NO
----------
NAME
-----------------------------------------------------------
15
TOM

發現並沒報錯，我用的是ORACLE 10.2.0.2的版本。呵呵，並沒有報錯。看看執行計劃，也許根本沒走索引:

SQL> select * from kl911_1 a where a.no=22;

--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 45 | 3 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE| | 1 | 45 | 3 (0)| 00:00:01 | 3 | 3 |
|* 2 | TABLE ACCESS FULL | KL911_1 | 1 | 45 | 3 (0)| 00:00:01 | 3 | 3 |
--------------------------------------------------------------------------------------------------

看起來並沒有走索引，因為資料量比較小，CBO選擇了一種更高效的方法:全表掃描

***使用hint試一下:

--- 謂詞是local index，沒有報錯:

SQL> select /*+ index(a idx_kl911_1) */ * from kl911_1 a where a.no=22;

no rows selected

--- 謂詞是global index, 報錯如下:

SQL> select /*+ index(a idx_kl911_2) */ * from kl911_1 a where name='TOM';
select /*+ index(a idx_kl911_2) */ * from kl911_1 a where name='TOM'
*
ERROR at line 1:
ORA-01502: index 'KL.IDX_KL911_2' or partition of such index is in unusable state

3. 然後看看如果都是正常狀態，LOCAL index的優勢在哪裡呢?

SQL> alter index idx_kl911_1 rebuild partition p1;

Index altered.

SQL> alter index idx_kl911_1 rebuild partition p2;

Index altered.

SQL> alter index idx_kl911_1 rebuild partition max_values;

Index altered.

SQL> select /*+ index(a idx_kl911_1) */ * from kl911_1 a where a.no=22;

Execution Plan
----------------------------------------------------------
Plan hash value: 1669532652

------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 45 | 1 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 45 | 1 (0)| 00:00:01 | 3 | 3 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| KL911_1 | 1 | 45 | 1 (0)| 00:00:01 | 3 | 3 |
|* 3 | INDEX RANGE SCAN | IDX_KL911_1 | 1 | | 1 (0)| 00:00:01 | 3 | 3 |
------------------------------------------------------------------------------------------------------------------

注意: PARTITION RANGE SINGLE，使用LOCAL INDEX是透過分割槽範圍的來走索引的，減少了結果集。

而接下來看GLOBAL INDEX,則無視分割槽表的特點，完完全全的按照普通索引範圍掃描來定義執行計劃,如下:

SQL> alter index idx_kl911_2 rebuild;

Index altered.

SQL> select /*+ index(a idx_kl911_2) */ * from kl911_1 a where name='TOM';


Execution Plan
----------------------------------------------------------
Plan hash value: 4155448299

------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 90 | 3 (0)| 00:00:01 | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| KL911_1 | 2 | 90 | 3 (0)| 00:00:01 | ROWID | ROWID |
|* 2 | INDEX RANGE SCAN | IDX_KL911_2 | 2 | | 1 (0)| 00:00:01 | | |
------------------------------------------------------------------------------------------------------------------

4. 結論

4.1 我們可以透過local選項為分割槽表建立local index，並透過dba_part_indexes.locality來檢視其屬性，或者透過dba_ind_partitions來檢視索引分割槽的內容.

4.2 如果執行計劃選擇了走索引,那麼在每次交換分割槽以後，global index是不可用的，必須重建。

4.3 如果執行計劃選擇了走索引，local index會以PARTITION RANGE SINGLE的方式，進行索引範圍掃描，而Global則是單純的範圍掃描。

---- 還有不完整和不正確的希望大家補充糾正！ (Jeff)

[@more@]