问答文章1 问答文章501 问答文章1001 问答文章1501 问答文章2001 问答文章2501 问答文章3001 问答文章3501 问答文章4001 问答文章4501 问答文章5001 问答文章5501 问答文章6001 问答文章6501 问答文章7001 问答文章7501 问答文章8001 问答文章8501 问答文章9001 问答文章9501

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则是单纯的范围扫描。
声明声明:本网页内容为用户发布,旨在传播知识,不代表本网认同其观点,若有侵权等问题请及时与本网联系,我们将在第一时间删除处理。E-MAIL:11247931@qq.com
刺身怎么下刀? 养老护理员资格证的等级是什么 养老护理员证有几级啊 工行手机银行如何提前还房贷 工行手机银行提前还房贷操作 工行手机银行app可以提前房贷还款吗_工行手机银行提前还房贷步骤... 金龙八部是什么时出版的 天龙八部陈浩民那一年出版的 金庸作品集:天龙八部图书信息 puum243请到柜面、网银或手机银行修改密码? 超重被交警抓一般怎么处理 面包PEI怎么写? 坯这个字念什么啊 陈勋奇拍过的武侠电影 陈勋奇饰演一个电影叫什么名字? 电影 边城英雄 叶开是谁主演的 从配乐、演员到导演,被洪金宝捧红的实力打星,陈勋奇现状如何? 陈勋奇 林正英 成龙 洪金宝他们的关系以及他们一起有演过那些电影 陈勋奇的电影有哪些? 佳人有约的下载地址? 陈勋奇的电影 陈勋奇主演的电影、 急!营销高手支招,渠道网络大换血? 有人认为网络渠道终将取代传统渠道 网络渠道可不可以赚钱 比较传统渠道和网络渠道的异同 最近在选择几家招商网站,现在比较倾向于选择的是渠道网络,渠道网络的招商到底靠谱不? 国网渠道平台网络技术(北京)有限公司怎么样? 网络渠道的优势和劣势 8p换原装屏幕有原彩吗? 北京市渠道网络技术开发有限公司怎么样? 阿里渠道网络技术(深圳)有限公司怎么样? 眉户曲子的内容题材 下载眉户村官 如何委婉的向父母要钱? 怎么开口问父母要钱啊 惠普m132a打印机如何打自检页 素老黄瓜汤的做法有哪些 门口的野蛮人的基本信息 门口的野蛮人的评价 门口的野蛮人怎么样 猫的耳朵为什么很热? 哪位大神知道,电脑硬盘的健康度怎么看? 微信怎么设置隐藏,不让好友看到? 怎么检测硬盘健康程度 硬盘检测工具健康数值怎么看? 如何判断硬盘是否健康 如何不让对方查看我的微信主页我不想让别人看到我的? 微信怎么设置隐藏,不让好友看到? 不想让别人看见自己的怎么办 怎么对微信好友隐藏自己的 怎么设置不让别人看到自己的。