(转)关于复合分区索引状态的研究

最新推荐文章于 2021-04-03 03:30:47 发布
最新推荐文章于 2021-04-03 03:30:47 发布
阅读量201 收藏 1
点赞数

为了得到测试目的，我建立了一个range_list的复合分区表zrp_part。建表语句如下：

SQL> create table zrp_part (
2 region number(3),
3 proc_no number(2)
4 )
5 partition by range (region)
6 subpartition by list (proc_no)
7 subpartition template
8 ( subpartition proc_1 values(1),
9 subpartition proc_2 values(2),
10 subpartition proc_max values(DEFAULT)
11 )
12 ( partition zrp_part_010 values less than(20),
13 partition zrp_part_020 values less than (30),
14 partition zrp_part_max values less than (MAXVALUE)
15 );

表已创建。


SQL>
然后再建一个LOCAL索引
SQL> create index inx_zrp_test on zrp_part (region,proc_no)
2 LOCAL
3 (PARTITION zrp_part_010,
4 PARTITION zrp_part_020,
5 PARTITION zrp_part_max
6 );

索引已创建。

SQL>

查看此时的索引状态
1）dba_indexes 视图

SQL> select index_name,status from dba_indexes where index_name='INX_ZRP_TEST';

INX_ZRP_TEST N/A

2) dba_ind_partitions视图
SQL> select index_name,partition_name,status from user_ind_partitions where index_name='INX_ZRP_TEST';

INX_ZRP_TEST ZRP_PART_010 N/A
INX_ZRP_TEST ZRP_PART_020 N/A
INX_ZRP_TEST ZRP_PART_MAX N/A

3）dba_ind_subpartitions视图

SQL> select index_name ,partition_name,subpartition_name,status from user_ind_subpartitions where index_name='INX_ZRP_TEST';

INX_ZRP_TEST ZRP_PART_010 ZRP_PART_010_PROC_1 USABLE
INX_ZRP_TEST ZRP_PART_010 ZRP_PART_010_PROC_2 USABLE
INX_ZRP_TEST ZRP_PART_010 ZRP_PART_010_PROC_MAX USABLE
INX_ZRP_TEST ZRP_PART_020 ZRP_PART_020_PROC_1 USABLE
INX_ZRP_TEST ZRP_PART_020 ZRP_PART_020_PROC_2 USABLE
INX_ZRP_TEST ZRP_PART_020 ZRP_PART_020_PROC_MAX USABLE
INX_ZRP_TEST ZRP_PART_MAX ZRP_PART_MAX_PROC_1 USABLE
INX_ZRP_TEST ZRP_PART_MAX ZRP_PART_MAX_PROC_2 USABLE
INX_ZRP_TEST ZRP_PART_MAX ZRP_PART_MAX_PROC_MAX USABLE

SQL>

插入几条数据验证一下
SQL> insert into zrp_part values(10,1);

已创建 1 行。

SQL> insert into zrp_part values(10,2);

已创建 1 行。

SQL> insert into zrp_part values(20,1);

已创建 1 行。

SQL> insert into zrp_part values(20,2);

已创建 1 行。

SQL> commit;

提交完成。

提交完成。

SQL> select * from zrp_part;

10 1
10 2
20 1
20 2

SQL>
下面我们看看数据都存到哪个分区里去了（这是为了让后面的测试更有说服力）
SQL> select * from zrp_part partition(zrp_part_010);

10 1
10 2

SQL> select * from zrp_part partition(zrp_part_020);

20 1
20 2

SQL> select * from zrp_part partition(zrp_part_max);

未选定行

SQL>
SQL> select * from zrp_part subpartition (zrp_part_010_proc_1);

10 1

SQL> select * from zrp_part subpartition (zrp_part_010_proc_2);

10 2

SQL> select * from zrp_part subpartition (zrp_part_020_proc_1);

20 1

SQL> select * from zrp_part subpartition (zrp_part_020_proc_2);

20 2

SQL> select * from zrp_part subpartition (zrp_part_max_proc_1);

未选定行

SQL> select * from zrp_part subpartition (zrp_part_max_proc_2);

未选定行

SQL>

可以看到，我们的数据按照预期的分区分别插入到了正确的地方。

下面我们做一个数据检索，看看Oracle 的执行计划是如何的

SQL> select * from zrp_part where region=10 and proc_no=1;

SQL> select * from zrp_part where region=10 and proc_no=1;

执行计划
----------------------------------------------------------
Plan hash value: 61450464

-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 1 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE| | 1 | 26 | 1 (0)| 00:00:01 | 1 | 1 |
| 2 | PARTITION LIST SINGLE| | 1 | 26 | 1 (0)| 00:00:01 | KEY | KEY |
|* 3 | INDEX RANGE SCAN | INX_ZRP_TEST | 1 | 26 | 1 (0)| 00:00:01 | 1 | 1 |
-------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("REGION"=10 AND "PROC_NO"=1)

Note
-----
- dynamic sampling used for this statement

SQL>

可以看到此时用上了分区索引，并进行了索引范围扫描。


下面我手工将索引的状态修改为UNUSABLE，模拟索引破坏的情况（比如移动分区的情况）
SQL> alter index inx_zrp_test unusable;

索引已更改。

SQL>

再看索引的状态
1）dba_indexes 视图

SQL> select index_name,status from dba_indexes where index_name='INX_ZRP_TEST';

INX_ZRP_TEST N/A

2) dba_ind_partitions视图
SQL> select index_name,partition_name,status from user_ind_partitions where index_name='INX_ZRP_TEST';

INX_ZRP_TEST ZRP_PART_010 N/A
INX_ZRP_TEST ZRP_PART_020 N/A
INX_ZRP_TEST ZRP_PART_MAX N/A

3）dba_ind_subpartitions视图

SQL> select index_name ,partition_name,subpartition_name,status from user_ind_subpartitions where index_name='INX_ZRP_TEST';

INX_ZRP_TEST ZRP_PART_010 ZRP_PART_010_PROC_1 UNUSABLE
INX_ZRP_TEST ZRP_PART_010 ZRP_PART_010_PROC_2 UNUSABLE
INX_ZRP_TEST ZRP_PART_010 ZRP_PART_010_PROC_MAX UNUSABLE
INX_ZRP_TEST ZRP_PART_020 ZRP_PART_020_PROC_1 UNUSABLE
INX_ZRP_TEST ZRP_PART_020 ZRP_PART_020_PROC_2 UNUSABLE
INX_ZRP_TEST ZRP_PART_020 ZRP_PART_020_PROC_MAX UNUSABLE
INX_ZRP_TEST ZRP_PART_MAX ZRP_PART_MAX_PROC_1 UNUSABLE
INX_ZRP_TEST ZRP_PART_MAX ZRP_PART_MAX_PROC_2 UNUSABLE
INX_ZRP_TEST ZRP_PART_MAX ZRP_PART_MAX_PROC_MAX UNUSABLE

SQL>

再做数据检索的验证（还是刚才那个检索语句）

SQL> select * from zrp_part where region=10 and proc_no=1;

执行计划
----------------------------------------------------------
Plan hash value: 3999291197
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 27 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE| | 1 | 26 | 27 (0)| 00:00:01 | 1 | 1 |
| 2 | PARTITION LIST SINGLE| | 1 | 26 | 27 (0)| 00:00:01 | KEY | KEY |
|* 3 | TABLE ACCESS FULL | ZRP_PART | 1 | 26 | 27 (0)| 00:00:01 | 1 | 1 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("REGION"=10)

Note
-----
- dynamic sampling used for this statement

SQL>

很明显，此时只能是走全表扫描了。

下面依次按照子分区来修复索引
SQL> alter index INX_ZRP_TEST rebuild subpartition ZRP_PART_010_PROC_1;

索引已更改。

SQL> select * from zrp_part where region=10 and proc_no=1;

执行计划
----------------------------------------------------------
Plan hash value: 3999291197

---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 27 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE| | 1 | 26 | 27 (0)| 00:00:01 | 1 | 1 |
| 2 | PARTITION LIST SINGLE| | 1 | 26 | 27 (0)| 00:00:01 | KEY | KEY |
|* 3 | TABLE ACCESS FULL | ZRP_PART | 1 | 26 | 27 (0)| 00:00:01 | 1 | 1 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("REGION"=10)

Note
-----
- dynamic sampling used for this statement

SQL>

SQL> select * from zrp_part subpartition (zrp_part_010_proc_1);
10 1

SQL> select index_name ,partition_name,subpartition_name,status from user_ind_subpartitions where index_name='INX_ZRP_TEST' and status='USABLE';

INX_ZRP_TEST ZRP_PART_010 ZRP_PART_010_PROC_1 USABLE

SQL>


SQL> alter index inx_zrp_test rebuild subpartition zrp_part_010_proc_2;

索引已更改。

SQL> select * from zrp_part where region=10 and proc_no=1;

执行计划
----------------------------------------------------------
Plan hash value: 3999291197

---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 27 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE| | 1 | 26 | 27 (0)| 00:00:01 | 1 | 1 |
| 2 | PARTITION LIST SINGLE| | 1 | 26 | 27 (0)| 00:00:01 | KEY | KEY |
|* 3 | TABLE ACCESS FULL | ZRP_PART | 1 | 26 | 27 (0)| 00:00:01 | 1 | 1 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("REGION"=10)

Note
-----
- dynamic sampling used for this statement

SQL> alter index inx_zrp_test rebuild subpartition zrp_part_010_proc_max;

索引已更改。

SQL> select * from zrp_part where region=10 and proc_no=1;

执行计划
----------------------------------------------------------
Plan hash value: 61450464

-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 1 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE| | 1 | 26 | 1 (0)| 00:00:01 | 1 | 1 |
| 2 | PARTITION LIST SINGLE| | 1 | 26 | 1 (0)| 00:00:01 | KEY | KEY |
|* 3 | INDEX RANGE SCAN | INX_ZRP_TEST | 1 | 26 | 1 (0)| 00:00:01 | 1 | 1 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("REGION"=10 AND "PROC_NO"=1)

Note
-----
- dynamic sampling used for this statement

SQL>

SQL> select index_name ,partition_name,subpartition_name,status from user_ind_subpartitions where index_name='INX_ZRP_TEST' and status='USABLE';

INX_ZRP_TEST ZRP_PART_010 ZRP_PART_010_PROC_1 USABLE
INX_ZRP_TEST ZRP_PART_010 ZRP_PART_010_PROC_2 USABLE
INX_ZRP_TEST ZRP_PART_010 ZRP_PART_010_PROC_MAX USABLE

SQL>

到这个时候，第一个分区的所有子分区的索引全部rebuild 了，所以索引就用了，而其他分区的还不行，还需要修复后才可以。我就不写了。累死我了。

小结一下吧：根据测试的情况来看，复合分区索引和一般的分区索引性质是一样的，只不过大家访问的时候需要换成另外的视图而已了。

值得一提的是：
SQL> alter index inx_zrp_test rebuild partition zrp_part_010;
alter index inx_zrp_test rebuild partition zrp_part_010
*
第 1 行出现错误:
ORA-14287: 不能 REBUILD (重建) 组合范围分区的索引的分区
SQL>

请大家要注意。

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

转载于:http://blog.itpub.net/10522815/viewspace-420422/

cuichui6699
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
SQL要怎么写才会调用到复合索引和分区
my_super的博客
04-11 464
本文讨论复合索引以及分区的简单使用，也即是SQL语句要如何写才会调用到复合索引以及分区。在此只作为新手交流。   Titles表的设计：   CREATE TABLE `titles` ( `emp_no` int(11) NOT NULL, `title` varchar(50) NOT NULL, `from_date` date NOT NULL, `to...
Mysql索引知识概括
weixin_41005188的博客
12-02 1676
Mysql索引知识概括索引详解索引分析 索引详解 性能下降SQL慢 、执行时间长 、等待时间长： 查询语句写的烂 索引失效 关联查询太多join(设计缺陷或不得已的需求) 服务器调优及各个参数设置(缓冲\线程数等) SQL执行顺序： 手写： 机读： 总结： 常见通用的join查询： 索引简介： MySQL官方对索引的定义为：索引(Index)是帮助MySQL高校获取数据的数据结构。 ①可以得到索引的本质：索引是数据结构。 ②索引的目的在于提高查询效率，可以类比字典， ③你可以简单理解为"排好序的
关于复合分区索引状态的研究
我的Oracle DBA之路
03-31 794
关于复合分区索引状态的研究 http://www.itpub.net/thread-499219-1-1.html
oracle 建分区索引_你真的会创建数据库复合索引吗？附具体执行计划
weixin_29793473的博客
12-06 216
前言举例数据库的复合索引不是用到哪列，就在哪列上创建。遇到以下这种情况就不能创建复合索引。举个例子数据库表这张表有什么特点？第一、经a、b和c都经常要被查询，并且有经常a、b、c组合查询的可能性。这就给了我们建索引的理由。第二、a列区分度不大，b和c都有区分度。请问这种场景如何建索引？下面看我们思考的逻辑：很直观的想法，分别在a、b和c上分别建单列索引，即我们最终有三个索引，分别是(a)、(b)、...
Oracle复合分区索引,如何确定复合分区索引的状态？
weixin_42271195的博客
04-03 116
最初由 Toms_zhang 发布[B]不正常，已经失效了，需要rebuild否则会报错的 [/B]u think so?C:\Documents and Settings\Roger>sqlplus roger/maggie@rasSQL*Plus: Release 9.2.0.4.0 - Production on 星期日 3月 5 11:12:04Copyright (c) 1982,...
全面学习分区表及分区索引(6)--创建range-list组合分区
cqbh2011的博客
05-14 109
创建range-list组合分区 Range-list组合分区的创建与range-hash极为相似，只是子分区为list分区，当然同样也可以应用分区模板，下面也举一个示例： JSSWEB>createtable...
分布式计算中的弱对称破缺问题的归纳式算法研究
−C以前的研究表明，C= 1 +n1i=0n+1ki，对某些系数ntski∈Z. 这种特征化C一期+1可在www.sciencedirect.com在线获取理论计算机科学电子笔记283（2012）13-27www.elsevier.com/locate/entcs一种归纳式的对称单色单形...
视觉基础：基于神经模块树网络的复合视觉推理
然而，现有的方法将语言的复合性质过度简化为单一的句子嵌入或主谓宾三元组的粗略组合。在本文中，我们建议地面自然语言在一个直观的，可解释的，和复合的时尚，因为它应该是。特别地，我们开发了一种称为神经模块树...
基于乘积量化器的倒排索引优化方案
12210数据点粗心细码丢失基于乘积量化器的可伸缩最近邻搜索倒排索引Haechan Noh，Taeho Kim，Jae-PilHeo*成均馆大学{noru0114，kth0522，jaepilheo} @ skku.edu摘要倒排索引是大规模数据集上非穷举最近邻搜索最常用...
基于压电传感器的脉象信号特征挖掘算法及疾病诊断研究
医学信息学解锁26（2021）100717基于压电传感器的脉象信号特征挖掘算法及疾病诊断研究<$，<$<$范琳a，b，张金城a，*，王忠民a， b，张小康 a，姚瑞玲 a，李艳 aaXi邮电大学计算机科学与技术学院b西安邮电大学网络...
oracle分区索引的失效和重建代码示例
09-09
主要介绍了oracle分区的失效和重建代码示例，小编觉得挺不错的，这里分享给大家，供需要的朋友参考。
Oracle 错误总结及问题解决 ORA
热门推荐
基于Oracle11G
07-08 18万+
ORA 错误大全
数据迁移
weixin_30951231的博客
10-08 75
pkill -9 java 停止服务 create table sic_hi_guest_201608 as select * from sic_hi_guest partition(PART201608);alter table sic_hi_guest drop partition PART201608; alter index INDEX_SIC_HI_GUEST_CERTNO_...
rebuild 分区索引
落落的专栏 专注SQL调优 性能调优
07-14 1万+
今天要做一个任务，rebuild 一个索引， 该索引建立在有11亿条数据的表上。 对于非组合分区索引，需要rebuild 每个分区(partition),不能直接rebuild整个索引， 对于组合分区索引，需要rebuild每个子分区（subpartition）,不能直接rebuild整个索引，也不能直接rebuild 分区(partition) 由于我要rebuild的索引很大
分区索引重建
weiwang的专栏
07-09 1万+
创建分区表： CREATE TABLE test(        id NUMBER,        name VARCHAR2(10)  )  partition by range(id)(            partition p1 values less than (1000),            partition p2 values less than
ora-16014;ora-00312 常见的问题
xiaomifeng287的专栏
01-30 560
今天早晨打开alter_XX.log  。看了一下日志，发现了两个错误，昨天刚使用rman 的delete archivelog all，delete obsolete backup，把控制文件里面的日志记录删除和备份过期的文件删除，这个错也是昨天产生的。就想总结一下处理过程：   使用 cat /XXX/XXX/XXX/XXX/bdump/alter_zdtdb.log|grep -i or
ORACLE 错误一览表(4)
jianhai1229的博客
04-18 2813
ORA-13401: 不受支持的 geoimage 格式 ORA-13402: NULL 目标 ORA-13403: NULL 源几何结构 ORA-13404: 不是本地源 ORA-13405: NULL 源 ORA-13406: 不受支持的图象处理命令 [] ORA-13407: 不受认可的空间类型限定词 [] ORA-13408: NULL 表名 ORA-13409: Oracle 表 "" ...
Oracle 错误代码详解及解决方式--ORA
心寒的博客
04-21 2万+
ORA-00001: 违反唯一约束条件 (.) 错误说明：当在唯一索引所对应的列上键入重复值时，会触发此异常。 ORA-00017: 请求会话以设置跟踪事件 ORA-00018: 超出最大会话数 ORA-00019: 超出最大会话许可数 ORA-00020: 超出最大进程数 () ORA-00021: 会话附属于其它某些进程；无法转换会话 ORA-00022: 无效的会话 ID；访问被拒绝 ORA...
首次遭遇ORA-15064
OS is DB,DB is OS
06-13 7347
整理一下前段时间遇到的ORA-15064错误。 有一套测试环境是11gR2的rac使用ASM，应用在测试时发现一个节点宕机了，检查了一下alert日志如下错误： Mon Jun 04 23:27:32 2012 NOTE: ASMB terminating Errors in file /soft/oracle/diag/rdbms/pt1db/pt1db2/trace/pt1db2_as
全面解读：分区表与分区索引实战指南
- 提供了关于如何对分区索引进行增加、删除、重编译和重命名的操作指导，确保索引性能与分区策略协同工作。 4. 实用工具和资源： - 学习资源链接至www.5ienet.com，以及作者提供的QQ号（5454589君三思）和QQ群...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值