分区表和分区索引的创建案例

--帐号信息表CNOUG博客首页4F3A~{(~*b us
CREATE TABLE TB_U_ACCOUNT
hy#kOc-D!Q"P0(
$}"S?l5aM5`~M0 ACCOUNT_ID     NUMBER(20),
~b%Wc,C#F+|0 ACCOUNT_BALANCE_TOTAL  NUMBER(13,2),CNOUG博客首页`3j&IcHR
 ACCOUNT_BALANCE   NUMBER(13,2),CNOUG博客首页O5_H[-dD
 DAY_MONEY     NUMBER(13,2),
F2riw}"vR0 MONTH_MONEY     NUMBER(13,2),
`K5^&F&G Y:x#G$rt0 ADD_DAY     VARCHAR2(8),CNOUG博客首页*?#WA5Tf
~4\t!Ca
 ADD_MONTH     VARCHAR2(6),CNOUG博客首页!X]?R-~p4UX
 ACCOUNT_REGISTER_TIME  DATE,
0nP;m;k k0 ACCOUNT_LAST_CONSUME_TIME DATE,CNOUG博客首页*k(E#h*o-R T$NM(s
 ACCOUNT_STATUS    NUMBER(1)CNOUG博客首页1q4\ G/_c#|rt o$[
)
.Q$A4k*@6zAG0PARTITION BY HASH(account_id)
c*Ax+d3K,C0i&a0(
;yZ"m |(b9bG3yN0v0 PARTITION PART_ACCOUNT_01 TABLESPACE TBS_BILLING01,
d'zga"y0Q0 PARTITION PART_ACCOUNT_02 TABLESPACE TBS_BILLING01,
K"K.Rk
ZX&z:Q0 PARTITION PART_ACCOUNT_03 TABLESPACE TBS_BILLING01,
%a*tM1iEBRW0 PARTITION PART_ACCOUNT_04 TABLESPACE TBS_BILLING01,
Q#Po.et%A$yO1\0 PARTITION PART_ACCOUNT_05 TABLESPACE TBS_BILLING01CNOUG博客首页-^
p*lRcgap1_
);

,EHV/C^T7J0--创建全局分区索引CNOUG博客首页OPh%^5k6a
CREATE INDEX PK_PARTITION_TB_U_ACCOUNT ON TB_U_ACCOUNT(ACCOUNT_ID) 
[`-~0]8RI0PARALLEL
2O |.E9R$F0global PARTITION BY HASH(ACCOUNT_ID)
4v'r q:^y b(lf~ n0( CNOUG博客首页-m@D$T{H5q
 PARTITION IDX_ACCOUNT_ID_GLOBAL_01 TABLESPACE TBS_BILLING_INDX01,
"F*D_sY-S5VL0 PARTITION IDX_ACCOUNT_ID_GLOBAL_02 TABLESPACE TBS_BILLING_INDX01,
*Z7{(k:l1M0 PARTITION IDX_ACCOUNT_ID_GLOBAL_03 TABLESPACE TBS_BILLING_INDX01, CNOUG博客首页Ey)P}Zk\
 PARTITION IDX_ACCOUNT_ID_GLOBAL_04 TABLESPACE TBS_BILLING_INDX01, CNOUG博客首页T0Mlc&Cd
 PARTITION IDX_ACCOUNT_ID_GLOBAL_05 TABLESPACE TBS_BILLING_INDX01CNOUG博客首页4b+F6SaSaEu$N X
);

--创建主键
Y%aTz$x1D0alter table TB_U_ACCOUNTCNOUG博客首页ZjFltxd
add constraint PK_PARTITION_TB_U_ACCOUNT primary key (ACCOUNT_ID) using index;

--帐户交易流水表
].Cs h3s2Z|2G0CREATE TABLE TB_L_BUSI_LOGCNOUG博客首页P v)cj"l6v;D
(
i,@os.}~8M ]0 LOG_ID   VARCHAR2(14),CNOUG博客首页:u2uZ#L_N'w
 OP_TIME  DATE,
1gR{*qKk0U0 BUS_TYPE  VARCHAR(2),CNOUG博客首页#e%S6K,S T#x
 ACCOUNT_ID  VARCHAR2(20),
#_%U\i7Q0 USER_ID  VARCHAR2(20),CNOUG博客首页\'{.E8r:Qx:@2}
 MSISDN   VARCHAR2(11),
B*Onj%I0}6tc0G0 CHANNEL_ID  VARCHAR2(8),
t&f in eO8y0 CPID   VARCHAR2(6),CNOUG博客首页]\S2s!uv^K^
 CP_SERVICE_ID  VARCHAR2(11),
I'^u6WYg)F0 CONSUME_CODE  VARCHAR2(8),CNOUG博客首页
X/f t#v `!z/ov
 MONEY   NUMBER(8,2),
#]V/HVEQ(cu-`0 POINT   NUMBER(8,2),CNOUG博客首页/_]!i#b&y5G
 POINT_FACT  NUMBER(8,2),CNOUG博客首页)s5_ dD(ew3pn;o
 PREFERENTIAL_PRICE NUMBER(1),
%J'EE+x:JLhK)K*h0 PREFERENTIAL_RULE_ID NUMBER(6),CNOUG博客首页)Ft?9zJ.az[eMz
 RESULT   NUMBER(6)CNOUG博客首页;l)t:IE'f8b9|5k
)CNOUG博客首页Ig3JLl,L9|E
PARALLELCNOUG博客首页D.r
oXgJRg
PARTITION BY RANGE(op_time) SUBPARTITION BY HASH(account_id) CNOUG博客首页f7tU2T)y&R
(
Q2W?3P5H$J~0 PARTITION PART_BUSI_200704 VALUES LESS THAN(TO_DATE('2007-5-01','YYYY-MM-DD')) TABLESPACE TBS_BILLING01,
8]7o
j7JIT,OaF0 PARTITION PART_BUSI_200705 VALUES LESS THAN(TO_DATE('2007-6-01','YYYY-MM-DD')) TABLESPACE TBS_BILLING01,
q&L,H*L.x0I d,j!Q0 PARTITION PART_BUSI_200706 VALUES LESS THAN(TO_DATE('2007-7-01','YYYY-MM-DD')) TABLESPACE TBS_BILLING01, CNOUG博客首页P
r/fe%ug
 PARTITION PART_BUSI_200707 VALUES LESS THAN(TO_DATE('2007-8-01','YYYY-MM-DD')) TABLESPACE TBS_BILLING01,
y@-HgtI'L+O3WA y0 PARTITION PART_BUSI_200708 VALUES LESS THAN(TO_DATE('2007-9-01','YYYY-MM-DD')) TABLESPACE TBS_BILLING01, CNOUG博客首页EFD ?:S0r
 PARTITION PART_BUSI_200709 VALUES LESS THAN(TO_DATE('2007-10-01','YYYY-MM-DD')) TABLESPACE TBS_BILLING01,
Z?-r [_jV0 PARTITION PART_BUSI_200710 VALUES LESS THAN(TO_DATE('2007-11-01','YYYY-MM-DD')) TABLESPACE TBS_BILLING01CNOUG博客首页ml nU2O^ d-f
);

--创建全局分区索引(op_time)
!K4iMlU)cS0CREATE INDEX IDX_BUSI_GLOBAL_DATE ON TB_L_BUSI_LOG(op_time)  CNOUG博客首页~ {i~8X
PARALLELCNOUG博客首页DC.Y5UAl
?
GLOBAL PARTITION BY RANGE(op_time)
#Pb:GSx+B @o)K0( CNOUG博客首页PT\7Y.Fix.b
 PARTITION IDX_BUSI_GLOBAL_DATE_200704 VALUES LESS THAN(TO_DATE('2007-5-01','YYYY-MM-DD')) TABLESPACE TBS_BILLING_INDX01, CNOUG博客首页S!`vF$b7kh-n
 PARTITION IDX_BUSI_GLOBAL_DATE_200705 VALUES LESS THAN(TO_DATE('2007-6-01','YYYY-MM-DD')) TABLESPACE TBS_BILLING_INDX01, CNOUG博客首页Ui%R"W2r
 PARTITION IDX_BUSI_GLOBAL_DATE_200706 VALUES LESS THAN(TO_DATE('2007-7-01','YYYY-MM-DD')) TABLESPACE TBS_BILLING_INDX01, CNOUG博客首页fR_:a
k
 PARTITION IDX_BUSI_GLOBAL_DATE_200707 VALUES LESS THAN(TO_DATE('2007-8-01','YYYY-MM-DD')) TABLESPACE TBS_BILLING_INDX01, CNOUG博客首页|(`kO-sQfh
 PARTITION IDX_BUSI_GLOBAL_DATE_200708 VALUES LESS THAN(TO_DATE('2007-9-01','YYYY-MM-DD')) TABLESPACE TBS_BILLING_INDX01, CNOUG博客首页CN Y/y-x$q o+H1D'mK(S
 PARTITION IDX_BUSI_GLOBAL_DATE_200709 VALUES LESS THAN(TO_DATE('2007-10-01','YYYY-MM-DD')) TABLESPACE TBS_BILLING_INDX01,
P4ueF%UyD0 PARTITION IDX_BUSI_GLOBAL_DATE_200710 VALUES LESS THAN(MAXVALUE) TABLESPACE TBS_BILLING_INDX01CNOUG博客首页 l.uT%@~YE2nQ)~
);

CNOUG博客首页 ?Th9Q@/f
--创建全局分区索引(account_id)CNOUG博客首页t^ vAdRXjR
CREATE INDEX INDX_PARTITION_TB_L_BUSI_LOG ON TB_L_BUSI_LOG(ACCOUNT_ID) 
S
\)u3D k4f/q&]4O.h0PARALLELCNOUG博客首页Npp*i#d6Ma+r
global PARTITION BY HASH(ACCOUNT_ID)
3FMn])q6X0[#~0( CNOUG博客首页e~HP[
 PARTITION IDX_TB_L_BUSI_LOG_GLOBAL_01 TABLESPACE TBS_BILLING_INDX01,
nz,] P%}I0 PARTITION IDX_TB_L_BUSI_LOG_GLOBAL_02 TABLESPACE TBS_BILLING_INDX01, CNOUG博客首页 F.[q(w _M8j"V4N)f8f
 PARTITION IDX_TB_L_BUSI_LOG_GLOBAL_03 TABLESPACE TBS_BILLING_INDX01, CNOUG博客首页fd,P;xUw2r$T:uV
 PARTITION IDX_TB_L_BUSI_LOG_GLOBAL_04 TABLESPACE TBS_BILLING_INDX01,
\:Vx4~F0 PARTITION IDX_TB_L_BUSI_LOG_GLOBAL_05 TABLESPACE TBS_BILLING_INDX01
-I2u+]c7b?y0);

q0q$v5Q\TQ)E0/*CNOUG博客首页 m
Dnjy_5M!@
--若需新建时间分区可采用如下的语句CNOUG博客首页dLDB A/Z-xe
ALTER TABLE TB_L_BUSI_LOG
I8tP0]9D;d
G"g0ADD PARTITION PART_BUSI_200711 VALUES LESS THAN(TO_DATE('2007-12-01','YYYY-MM-DD'))CNOUG博客首页(u0o"aR3b0j
TABLESPACE TBS_BILLING01;
9X [ \
sV0*/