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

 

创建查看和浅析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)