oracle分区表总结(转)
上一篇 / 下一篇 2009-05-24 16:17:36 / 个人分类:Oracle
- 文件版本: V1.0
- 开发商: 本站原创
- 文件来源: 本地
- 界面语言: 简体中文
- 授权方式: 免费
- 运行平台: Win9X/Win2000/WinXP
在ORACLE里如果遇到特别大的表,可以使用分区的表来改变其应用程序的性能。51Testing软件测试网Q oS"ij,`j
y D5z3mb0
1.1分区表PARTITION table
在ORACLE里如果遇到特别大的表,可以使用分区的表来改变其应用程序的性能。51Testing软件测试网&[H7e{^hWrx
1.1.1分区表的建立:
某公司的每年产生巨大的销售记录,DBA向公司建议每季度的数据放在一个分区内,以下示范的是该公司1999年的数据(假设每月产生30M的数据),操作如下:
i m_+|c1zNZ0范围分区表:
ChUj;x3b0CREATE TABLE sales51Testing软件测试网 \']? q?H
(invoice_no NUMBER,
tK#Z;M3c"g0...51Testing软件测试网Ev$uu SsNGlm
sale_date DATE NOT NULL )
1b0l&@/x2S+p8rEu0PARTITION BY RANGE (sale_date)51Testing软件测试网];bJ1q lU.SU
(PARTITION sales1999_q151Testing软件测试网2Gc.i(xJ C!Y;}f*u
VALUES LESS THAN (TO_DATE(‘1999-04-01’,’YYYY-MM-DD’)51Testing软件测试网B:y2k+Tn7Sa6VA
TABLESPACE ts_sale1999q1,51Testing软件测试网r O2l2gB;|,]A
PARTITION sales1999_q251Testing软件测试网'JeM0\'\ D
VALUES LESS THAN (TO_DATE(‘1999-07-01’,’YYYY-MM-DD’)51Testing软件测试网6~6Z]G.r9tt}(`O
TABLESPACE ts_sale1999q2,51Testing软件测试网!jF7G"LE.O/\"R
PARTITION sales1999_q3
4n]ff#yQ+c n0VALUES LESS THAN (TO_DATE(‘1999-10-01’,’YYYY-MM-DD’)
_LIL)x0TABLESPACE ts_sale1999q3,
*U6_3j2w[6\6X0PARTITION sales1999_q451Testing软件测试网(?"\8|no
VALUES LESS THAN (TO_DATE(‘2000-01-01’,’YYYY-MM-DD’)
clG|$[|0TABLESPACE ts_sale1999q4 );
$d0E@ho0--values less than (maxvalue)51Testing软件测试网 \0v3xF6|bVA{
列表分区表:
#i5^ `v'b({0create table emp (51Testing软件测试网 r?^7}*pVD%[
empno number(4),
C!l6F0T,J4|:w O_0ename varchar2(30),51Testing软件测试网 U n;_doP]
location varchar2(30))
2?Q WR3lR V0partition by list (location)
6V|} {c0(partition p1 values ('北京'),
}6YN7L0o/K;t l0partition p2 values ('上海','天津','重庆'),51Testing软件测试网B9R)j'U!W8g
partition p3 values ('广东','福建')
7U3w!UO*|2z.qo0partition p0 values (default)51Testing软件测试网.Er[hc8vL
);
%|E5V^;~F?a)l0哈希分区:
)y Px%\+lV]z^0create table emp (
m M9I-t;Bo/K7Tb0empno number(4),51Testing软件测试网cC8V~[(f0L
ename varchar2(30),51Testing软件测试网|2FjJJ
sal number)51Testing软件测试网S BwiO;IQ7|(}
partition by hash (empno)51Testing软件测试网"E_Oj7[H6mY
partitions 851Testing软件测试网Q&^!ff+niW;t^
store in (emp1,emp2,emp3,emp4,emp5,emp6,emp7,emp8);
:?ZL5J)jx0组合分区:51Testing软件测试网J ^2o9WTy@
范围哈希组合分区:51Testing软件测试网2BD |W1k$eIr _"BKw
create table emp (
mi3h a^[(c(D~0empno number(4),
b"w'Uc.\;o9c0ename varchar2(30),
5S5A*p&Pc iK;@5z,`^P0hiredate date)51Testing软件测试网Q@9Y1jj
partition by range (hiredate)
.M+Jo'[$Au*@0subpartition by hash (empno)51Testing软件测试网H-h0iF^W9j&~ B
subpartitions 2
P/g-[P(xh `I0(partition e1 values less than (to_date('20020501','YYYYMMDD')),
U#z4RG*s2Sz0partition e2 values less than (to_date('20021001','YYYYMMDD')),
C@.y w0D0partition e3 values less than (maxvalue));
*q P6Hkt1]3cu0范围列表组合分区:51Testing软件测试网YGE fU8J rW
CREATE TABLE customers_part (51Testing软件测试网Q i8J-hKw
customer_id NUMBER(6),
'P$b}5~0Yr@-f*t0cust_first_name VARCHAR2(20),51Testing软件测试网;O.x\l*B}nK
cust_last_name VARCHAR2(20),
PsQc4M:M0nls_territory VARCHAR2(30),51Testing软件测试网_bhLj q
credit_limit NUMBER(9,2))51Testing软件测试网5\B6M!v"H iR
PARTITION BY RANGE (credit_limit)
y3F+afK9tEP8gU0SUBPARTITION BY LIST (nls_territory)
8Bl}iZ]8L9X0SUBPARTITION TEMPLATE51Testing软件测试网 sz(~+piq$d
(SUBPARTITION east VALUES ('CHINA', 'JAPAN', 'INDIA', 'THAILAND'),
Fh{i.z~[w0SUBPARTITION west VALUES ('AMERICA', 'GERMANY', 'ITALY', 'SWITZERLAND'),
+e2Xrz7U3_&Zo0SUBPARTITION other VALUES (DEFAULT))
s-y-b[w+Y){0(PARTITION p1 VALUES LESS THAN (1000),
3g1b0{4aiNg0PARTITION p2 VALUES LESS THAN (2500),
6ojxkS _0PARTITION p3 VALUES LESS THAN (MAXVALUE));51Testing软件测试网zeA/Y3j
create table t1 (id1 number,id2 number)
2wv$Z!j3S @!{9P0partition by range (id1) subpartition by list (id2)