分区索引学习笔记 (77天)

续接上次的分区表学习笔记,对分区索引进行了总结。

--index maintance SQL> select index_name,table_name from user_indexes where table_name='RANGE_PART';

no rows selected

--create one global index SQL> create index glb_range_part on range_part(a,b) 2 global partition by range(a) 3 ( partition part_01 values less than(1000), 4 partition part_02 values less than(maxvalue) 5 );

Index created. SQL> create index ind_range_part on range_part(a,b) local; create index ind_range_part on range_part(a,b) local * ERROR at line 1: ORA-01408: such column list already indexed --如果已经定义了index的列,则不能再创建其他的索引 --再次验证 SQL> create index ind_range_part on range_part(a,b);

Index created.

SQL> create index ind_range_part on range_part(a,b) local; create index ind_range_part on range_part(a,b) local * ERROR at line 1: ORA-00955: name is already used by an existing object

--提示是index名字重复了,似乎可以重新建一个其他名字的index SQL> create index ind1_range_part on range_part(a,b); create index ind1_range_part on range_part(a,b) * ERROR at line 1: ORA-01408: such column list already indexed --这次还是回到上一步,错误重现

--如果不加local,索引在status列会有不同 SQL> select index_name,table_name,status from user_indexes where table_name='RANGE_PART';

INDEX_NAME TABLE_NAME STATUS ------------------------------ ------------------------------ -------- IND_RANGE_PART RANGE_PART VALID

SQL> drop index ind_range_part;

Index dropped.

SQL> create index ind_range_part on range_part(a,b) local;

Index created.

SQL> select index_name,table_name,status from user_indexes where table_name='RANGE_PART';

INDEX_NAME TABLE_NAME STATUS ------------------------------ ------------------------------ -------- IND_RANGE_PART RANGE_PART N/A

关于global index还有一点是global partition Index必须是prefixed的 SQL> create index ind_range_part on range_part(b,a) global partition by range(a) ( partition part_01 values less than(1000), partition part_02 values less than(maxvalue) ) SQL> / global partition by range(a) * ERROR at line 2: ORA-14038: GLOBAL partitioned index must be prefixed

--清除所有的索引,重新测试 SQL> create index ind_range_part on range_part(a,b) global partition by range(a) ( partition glb_part_01 values less than(1000), partition glb_part_02 values less than(maxvalue) ) 2 3 4 5 6 SQL> /

Index created.

SQL> create index ind_range_part2 on range_part(b,a) local;

Index created.

1* select index_name,status from user_indexes where table_name='RANGE_PART' SQL> /

INDEX_NAME STATUS ------------------------------ -------- IND_RANGE_PART N/A IND_RANGE_PART2 N/A

SQL> select index_name,partition_name,high_value,status from user_ind_partitions where index_name in(select index_name from user_indexes where table_name='RANGE_PART');

INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS ------------------------------ ------------------------------ -------------------- -------- IND_RANGE_PART GLB_PART_01 1000 USABLE IND_RANGE_PART2 PART_05 MAXVALUE USABLE IND_RANGE_PART2 PART_02 4000 USABLE IND_RANGE_PART2 PART_01 2000 USABLE IND_RANGE_PART GLB_PART_02 MAXVALUE USABLE

SQL> alter table range_part merge partitions part_01,part_02;

Table altered.

SQL> select index_name,partition_name,high_value,status from user_ind_partitions where index_name in(select index_name from user_indexes where table_name='RANGE_PART');

INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS ------------------------------ ------------------------------ -------------------- -------- IND_RANGE_PART GLB_PART_01 1000 UNUSABLE IND_RANGE_PART2 PART_05 MAXVALUE USABLE IND_RANGE_PART GLB_PART_02 MAXVALUE UNUSABLE IND_RANGE_PART2 SYS_P51 4000 UNUSABLE

--分区编译 SQL> alter index ind_range_part2 rebuild partition SYS_P51;

Index altered. INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS ------------------------------ ------------------------------ -------------------- -------- IND_RANGE_PART GLB_PART_01 1000 UNUSABLE IND_RANGE_PART2 PART_05 MAXVALUE USABLE IND_RANGE_PART GLB_PART_02 MAXVALUE UNUSABLE IND_RANGE_PART2 SYS_P51 4000 USABLE

--对全局索引全表rebuild失败 SQL> ALTER INDEX IND_RANGE_PART REBUILD; ALTER INDEX IND_RANGE_PART REBUILD * ERROR at line 1: ORA-14086: a partitioned index may not be rebuilt as a whole --只能根据分区来相应rebuild

SQL> ALTER INDEX IND_RANGE_PART REBUILD PARTITION GLB_PART_01;

Index altered.

SQL> ALTER INDEX IND_RANGE_PART REBUILD PARTITION GLB_PART_02;

Index altered.

SQL> ALTER TABLE RANGE_PART RENAME PARTITION SYS_P51 TO PART_01;

Table altered.

SQL> ALTER TABLE RANGE_PART SPLIT PARTITION PART_01 AT(2000) INTO (PARTITION PART_01,PARTITION PART_02);

Table altered.

SQL> select index_name,partition_name,high_value,status from user_ind_partitions where index_name in(select index_name from user_indexes where table_name='RANGE_PART');

INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS ------------------------------ ------------------------------ -------------------- -------- IND_RANGE_PART GLB_PART_01 1000 UNUSABLE IND_RANGE_PART2 PART_05 MAXVALUE USABLE IND_RANGE_PART2 PART_02 4000 UNUSABLE IND_RANGE_PART2 SYS_P51 2000 UNUSABLE IND_RANGE_PART GLB_PART_02 MAXVALUE UNUSABLE

--PARTITION_NAME为SYS_P51,这个是Index的partition_name SQL> SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='RANGE_PART';

PARTITION_NAME ------------------------------ PART_01 PART_02 PART_05

--分区名字没有问题

--rebuild索引有以下的方式 SQL> ALTER TABLE RANGE_PART MODIFY PARTITION PART_01 REBUILD UNUSABLE LOCAL INDEXES;

Table altered.

SQL> ALTER INDEX IND_RANGE_PART2 REBUILD PARTITION PART_02;

Index altered.

最后有几个视图需要注意一下。

SQL> SELECT PARTITION_COUNT,STATUS,TABLE_NAME FROM USER_PART_TABLES WHERE TABLE_NAME='RANGE_PART';

PARTITION_COUNT STATUS TABLE_NAME --------------- -------- ------------------------------ 3 VALID RANGE_PART

--查找partition key SQL> SELECT NAME,OBJECT_TYPE,COLUMN_NAME FROM USER_PART_KEY_COLUMNS WHERE NAME='RANGE_PART';

NAME OBJEC COLUMN_NAME ------------------------------ ----- -------------------- RANGE_PART TABLE A

--USER_TAB_PARTITIONS --USER_IND_PARTITIONS

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2014-05-19

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏大内老A

Audit Logging-Stored Procedure

1. T_ORDER For Insert: sp_order_i IF EXISTS (SELECT * FROM sysobjects WHERE type...

2017
来自专栏菩提树下的杨过

如何让oracle的select强制走索引

大多数情况下,oracle数据库内置的查询优化策略还是很成功的,但偶尔也有犯2的时候,即使有索引,也会做全表扫描,可以参考以下语句的写法,强制让select语句...

1965
来自专栏xingoo, 一个梦想做发明家的程序员

windows程序设计-第四章 system1.c

/*---------------------------------------------------- SYSMETS1.C -- System M...

31611
来自专栏你不就像风一样

Mysql字段名与保留字冲突导致的异常解决

742
来自专栏Jed的技术阶梯

在MySQL中创建Oracle中的dept、emp、bonus、salgrade表

Oracle数据库中自带的4张表: dept、emp、bonus和salgrade是练习SQL查询的经典案例表,本文提供在MySQL中建这4张表的SQL语句

2271
来自专栏冷冷

Quartz依赖数据库表

Quartz的集群部署方案在架构上是分布式的,没有负责集中管理的节点,而是利用数据库锁的方式来实现集群环境下进行并发控制。BTW,分布式部署时需要保证各个节点的...

30510
来自专栏跟着阿笨一起玩NET

(2)SQL语句实现表的横向聚合

经过sql查询后输出的结果集为:(字段后面增加聚合[最大值] [最小值] [>=5的值个数])

871
来自专栏杨建荣的学习笔记

一条执行时间两天半的sql语句简化(r4笔记第62天)

周末刚过去,今天来到办公室做例行检查,就发现一条sql语句已经执行234841秒(65小时),已经两天多了。 查看了一下对应的Undo资源消耗,发现这个语句是最...

2868
来自专栏程序猿

4.3.4.7 Pattern Matching

MySQL provides standard SQL pattern matching as well as a form of pattern matchi...

3046
来自专栏测试开发架构之路

分分钟搞懂union与union all

SQL UNION 操作符 UNION 操作符用于合并两个或多个 SELECT 语句的结果集。 请注意,UNION 内部的 SELECT 语句必须拥有相同数量的...

3399

扫码关注云+社区