ITPub博客

首页 > 数据库 > Oracle > 分区索引学习笔记

分区索引学习笔记

Oracle 作者:jianghaifuyun1987 时间:2014-03-21 14:51:36 0 删除 编辑

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

--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

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

请登录后发表评论 登录
全部评论

注册时间:2014-03-07

  • 博文量
    75
  • 访问量
    74647