OracleRMAN快速入门指南:Oracle分区表,分区索引
一:建立一个分区表,并插入数据:SQL> create table part_tab (id1 number,id2 number)
2 partition by RANGE (id1)
3 (
4 partition p1 values less than (20),
5 partition p2 values less than (40),
6 partition p3 values less than (60),
7 partition p4 values less than (maxvalue)
8 );
Table created.
SQL>
SQL> insert into part_tab select user_id,trunc(dbms_random.value(1,100)) from dba_users;
37 rows created.
SQL>
SQL> commit;
Commit complete.
二:建立索引
在分区表上建立索引可以分为全局索引和本地索引:全局索引是针对整张表建立的一个索引,可以在建立索引的时候根据索引键进行相应的分区;
SQL> create index global_index on part_tab(id1) global
2 partition by range(id1)
3 (
4 partition p1 values less than (50),
5 partition p2 values less than (maxvalue)
6 );
Index created.
SQL> create index global_index2 on part_tab(id2) global
2 partition by range(id2)
3 (
4 partition p1 values less than (50),
5 partition p2 values less than (maxvalue)
6 );
Index created.
以上就是对id1和id2字段建立了2个全局索引
建立全局索引的时候索引键必须是索引分区键的前缀,不然建立索引的时候会报错:
SQL> create index global_index2 on part_tab(id2) global
2 partition by range(id1)
OracleRMAN快速入门指南:Oracle分区表,分区索引
</p> 3 (4 partition p1 values less than (50),
5 partition p2 values less than (maxvalue)
6 )
7 ;
partition by range(id1)
*
ERROR at line 2:
ORA-14038: GLOBAL partitioned index must be prefixed
SQL> create index global_index5 on part_tab(id2,id1) global
2 partition by range(id2)
3 (
4 partition p1 values less than (50),
5 partition p2 values less than (maxvalue)
6 );
Index created.
如果建立本地索引的时候,要建立的字段已经建立的全局索引,那建立这个索引的时候也要报错,反之亦然:
SQL> create index local_index1 on part_tab(id1) local;
create index local_index1 on part_tab(id1) local
*
ERROR at line 1:
ORA-01408: such column list already indexed
SQL> create index local_index1 on part_tab(id2) local;
create index local_index1 on part_tab(id2) local
*
ERROR at line 1:
ORA-01408: such column list already indexed
删除其中之一就可以正确建立:
SQL> drop index global_index2;
Index dropped.
SQL> create index local_index1 on part_tab(id2) local;
Index created.
三:增加分区
增加分区的时候,本地索引会自动更新,会自动增加相应的分区索引;而全局索引则不会,如果增加分区的时候没加上update global indexes 字句的时候,全局索引就会失效:
在原先分区表的基础上增加一个分区
SQL> alter table part_tab add partition p5 values less than (80);
alter table part_tab add partition p5 values less than (80)
OracleRMAN快速入门指南:Oracle分区表,分区索引
ERROR at line 1:ORA-14074: partition bound must collate higher than that of the last partition
报错,意思就是说现在要建立的分区值必须大于最后一个的分区值
这里可以有2种方法:
1:把大的那个分区删掉,再增加相应的分区,但这样会丢失数据
SQL> select * from part_tab where id1 > 60;
ID1 ID2
---------- ----------
86 7
88 18
70 25
65 34
78 51
76 57
67 59
85 60
91 62
89 63
74 66
ID1 ID2
---------- ----------
75 66
87 68
84 73
OracleRMAN快速入门指南:Oracle分区表,分区索引
</p> 2147483638 8183 82
72 89
79 89
61 92
90 95
20 rows selected.
SQL> alter table part_tab drop partition p4 update global indexes;
Table altered.
SQL> select * from part_tab where id1 > 60;
no rows selected
SQL> alter table part_tab add partition p4 values less than (maxvalue);
Table altered.
2:SPLIT PARTITION,分割那个分区,但对应分区上的本地索引会失效,分割完以后要重建这个本地索引
点击(此处)折叠或打开
SQL> select count(*) from part_tab where id1 > 60;
COUNT(*)
----------
20
SQL> ALTER TABLE part_tab SPLIT PARTITION p4 AT (80) INTO (PARTITION P5, PARTITION P4) UPDATE GLOBAL INDEXES ;
Table altered.
SQL> select count(*) from part_tab where id1 > 60;
COUNT(*)
----------
20
SQL> select * from part_tab where id1 > 80;
ID1 ID2
---------- ----------
90 4
2147483638 5
89 21
84 24
86 28
83 36
OracleRMAN快速入门指南:Oracle分区表,分区索引
</p> 87 5285 62
88 70
91 95
10 rows selected.
SQL> select index_name,partition_name,status from user_ind_partitions;
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
GLOBAL_INDEX P1 USABLE
GLOBAL_INDEX P2 USABLE
LOCAL_INDEX1 P2 USABLE
LOCAL_INDEX1 P1 USABLE
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
LOCAL_INDEX1 P3 USABLE
GLOBAL_INDEX5 P1 USABLE
GLOBAL_INDEX5 P2 USABLE
LOCAL_INDEX1 P5 UNUSABLE
LOCAL_INDEX1 P4 UNUSABLE
SQL> alter index local_index1 rebuild partition p4;
Index altered.
SQL> alter index local_index1 rebuild partition p5;
Index altered.
页:
[1]