分区索引学习笔记 (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 条评论
登录 后参与评论

相关文章

来自专栏一个会写诗的程序员的博客

MyBatis 多参数综合查询的动态 SQL 实现示例

431
来自专栏程序猿

MySQL Regular Expression

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

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

分分钟搞懂union与union all

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

3549
来自专栏小古哥的博客园

常用SQL语句集合

一、数据定义 1、创建新数据库:CREATE DATABASE database_name 2、创建新表:CREATE TABLE table_name (c...

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

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

852
来自专栏深度学习之tensorflow实战篇

SQL处理表结构的基本方法整理(创建表,关联表,复制表)

复制表结构及数据到新表 CREATE TABLE 新表 SELECT * FROM 旧表 只复制表结构到新表 CREATE TABLE 新表 SELECT * ...

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

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

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

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

(3)合并列值与分拆列值

在SQL中分拆列值和合并列值老生常谈了,从网上搜刮了一下并记录下来,以便不时之需 :)

821
来自专栏数据库新发现

CTAS、Nologging以及数据库运行模式

http://www.itpub.net/showthread.php?threadid=242761

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

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

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

1121

扫码关注云+社区

领取腾讯云代金券