ITPub博客

首页 > 数据库 > 数据库开发技术 > 创建查看和浅析LOCAL INDEX和GLOBAL INDEX

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

原创 数据库开发技术 作者:kl911 时间:2008-07-04 16:36:36 0 删除 编辑

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@]

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/661919/viewspace-1006726/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-01-03

  • 博文量
    20
  • 访问量
    318518