创建查看和浅析LOCAL INDEX和GLOBAL INDEX

局部与全局索引对比

最新推荐文章于 2024-01-25 15:25:28 发布

转载最新推荐文章于 2024-01-25 15:25:28 发布 · 1.3w 阅读

文章标签：

#table #sql #access #less #insert #exchange

oracle 专栏收录该内容

63 篇文章

订阅专栏

创建查看和浅析LOCAL INDEX和GLOBAL INDEX

2008-09-16 15:45

url:http://hi.baidu.com/klkl1110/blog/item/4c803fd99f80133332fa1c34.html

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)