|
楼主 |
发表于 2012-8-4 13:54:50
|
显示全部楼层
OracleRMAN快速入门指南:Oracle分区表,分区索引
</p> 3 (
0 R0 p. M3 S( @$ @- Q+ s R& Z8 o) K4 R, R 4 partition p1 values less than (50),
7 P" R" s8 ?/ e" C 5 partition p2 values less than (maxvalue)/ M/ x5 ]% }# s0 t0 S8 `+ E
6 )
+ ] G8 }" l/ ]* I: ?7 j0 P 7 ;/ @! z. g+ s$ y. X2 ]. x) C
partition by range(id1)2 a# p1 \- N: ], `
*
# c7 T/ n0 _5 L/ W ERROR at line 2:: e6 ]) C1 R3 T! o* g
ORA-14038: GLOBAL partitioned index must be prefixed* ~3 Q! k; G! t8 J' v
SQL> create index global_index5 on part_tab(id2,id1) global
9 e% R; T* O$ D9 e, _$ [7 @ 2 partition by range(id2)7 g3 G; o9 B* X, ^
3 (, T) S7 ~( t/ C$ r: \, s$ W
4 partition p1 values less than (50),
5 j/ \, P6 V X 5 partition p2 values less than (maxvalue)
' |* O$ n( h1 r 6 );: z- f [3 t N
Index created.
; \: F% k, Y7 c+ e; m0 a 如果建立本地索引的时候,要建立的字段已经建立的全局索引,那建立这个索引的时候也要报错,反之亦然:1 Q7 F8 Y6 Y3 b( M
SQL> create index local_index1 on part_tab(id1) local;
, q }4 m7 g. l1 P* \ create index local_index1 on part_tab(id1) local& b: _4 k, f0 r" Y5 b6 {% F* c- |8 ^" [
*
7 ?1 w6 t/ K7 M8 v9 o( X9 c ERROR at line 1:# t2 O4 v* K+ v E# C
ORA-01408: such column list already indexed
# ^! Q$ H& _' z) g4 d9 ?3 ` SQL> create index local_index1 on part_tab(id2) local;
& C( U4 C5 N+ z4 L" B create index local_index1 on part_tab(id2) local
- x8 w2 s+ q" ]" Q& S0 Z a5 o *2 A, z2 F& U" J
ERROR at line 1:! n q5 `1 p4 _) a- u
ORA-01408: such column list already indexed
% f5 E: U1 W3 | 删除其中之一就可以正确建立:
2 y+ J0 V Q/ D( g SQL> drop index global_index2;# _, Z5 f3 o# a5 S3 D$ _
Index dropped.6 ~ n9 @% |+ P7 ~8 U" e
SQL> create index local_index1 on part_tab(id2) local;
+ G( Y$ A( Y) _ Index created.* z* j. ~+ N. r2 e! A4 k2 _
三:增加分区
# y. R3 n1 O8 r0 ]: E1 s 增加分区的时候,本地索引会自动更新,会自动增加相应的分区索引;而全局索引则不会,如果增加分区的时候没加上update global indexes 字句的时候,全局索引就会失效:
. p5 `/ H; m4 q8 o1 R7 B 在原先分区表的基础上增加一个分区: L' o% u8 J& L/ M! v5 o z
SQL> alter table part_tab add partition p5 values less than (80);: J5 S' B* Q) p9 c
alter table part_tab add partition p5 values less than (80) |
|