create global index和create local index消耗temp空间的区别
发布网友
发布时间:2022-05-25 14:42
我来回答
共1个回答
热心网友
时间:2023-11-05 07:38
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则是单纯的范围扫描。