什么时候使用分区:: V/ E' t" Q+ r0 j% c
1、 大数据量的表,比如大于2GB。一方面2GB文件对于32位os是一个上限,另外备份时间长。
: C$ l4 G. N6 R; l: T% M 2、 包括历史数据的表,比如最新的数据放入到最新的分区中。典型的例子:历史表,只有当前月份的数据可以被修改,而其他月份只能read-only" \+ v" }1 C2 p9 Y9 E/ b* ?
ORACLE只支持以下分区:tables, indexes on tables, materialized views, and indexes on materialized views
7 \) m$ X' s4 F+ c9 N 分区对SQL和DML是透明的(应用程序不必知道已经作了分区),但是DDL可以对不同的分区进行管理。# y+ c' ~8 @: c7 b* t7 A, V+ N
不同的分区之间必须有相同的逻辑属性,比如共同的表名,列名,数据类型,约束;2 M) e6 K! K9 R3 Q, u' v/ I
但是可以有不同的物理属性,比如pctfree, pctused, and tablespaces. U8 I: t3 J2 \
分区独立性:即使某些分区不可用,其他分区仍然可用。
3 q) O7 N0 n, B( c 最多可以分成64000个分区,但是具有LONG or LONG RAW列的表不可以,但是有CLOB or BLOB列的表可以。3 H4 c8 ]6 Q2 Q8 G/ w
可以不用to_date函数,比如:% B- }8 ?& |# [5 e; f
alter session set nls_date_format='mm/dd/yyyy';
5 n' Z6 ~7 q9 W7 w CREATE TABLE sales_range, a) U7 i1 } w2 ?, y' S2 m
(salesman_id NUMBER(5),, J( w4 H8 S) l" C; b2 U% J0 p* [
salesman_name VARCHAR2(30),9 A2 Y1 u& K7 v$ Q
sales_amount NUMBER(10),
$ C' R9 W2 L+ g" z9 N sales_date DATE)
* A% }2 b+ N: p9 Y4 k- I PARTITION BY RANGE(sales_date)3 r) v: u ^: q- V1 @2 q) M
(* L# H, E% o$ N, b8 K
PARTITION sales_jan2000 VALUES LESS THAN('02/01/2000'),! ?' s D; @/ [* N# `: p
PARTITION sales_feb2000 VALUES LESS THAN('03/01/2000'),
, W5 v; B+ b# n PARTITION sales_mar2000 VALUES LESS THAN('04/01/2000'),
5 M/ u2 \3 h7 Z/ ^1 U PARTITION sales_apr2000 VALUES LESS THAN('05/01/2000')! j7 [) Q! b4 ~
);
2 x7 ~; G; @: r- h Partition Key:最多16个columns,可以是nullable的) c8 {9 B' J% T/ n9 a; j
非分区的表可以有分区或者非分区的索引;
4 [5 _- L) ?4 b" {* t: ?3 C 分区表可以有分区或者非分区的索引;
; h( w" m) f3 `# [ Partitioning 方法:
3 h8 p! ?& y- w3 X Range Partitioning
) M& |* ^: {" s v0 C List Partitioning
- I+ b" U9 W* J4 w8 G; o Hash Partitioning5 \/ z% P! V7 R; X6 |7 [% ~* n& u
Composite Partitioning
+ {9 @8 ^- H& G( P! q Composite Partitioning:组合,以及 range-hash and range-list composite partitioning% E% U% H* ~. a# K# z! r
Range Partitioning:* Q3 e- {( K$ a4 s' A* ?5 F5 V F
每个分区都有VALUES LESS THAN子句,表示这个分区小于(=)前一个分区的VALUES LESS THAN值。
$ |9 G- _& B2 Z5 Y3 T MAXVALUE定义最高的分区,他表示一个虚拟的无限大的值。这个分区包括null值。
9 R( ]% p. p/ | CREATE TABLE sales_range
3 b+ S& r1 z; ^- P0 g4 i9 Q0 ^. { (salesman_id NUMBER(5),& [+ L$ V1 `- h' m
salesman_name VARCHAR2(30),' y% v6 i& E/ q! a
sales_amount NUMBER(10),
- V1 N k' d) S" h3 Q& w% B sales_date DATE)
4 `, f' {) H' P+ b& a PARTITION BY RANGE(sales_date)6 S, b( \0 x. K2 V9 c7 {
(
$ P" @% _' M2 h$ j& n PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('01/02/2000','DD/MM/YYYY')),0 c- X( ~' d- \* c$ @
PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('01/03/2000','DD/MM/YYYY')),) q' o# I$ s: O [ n
PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('01/04/2000','DD/MM/YYYY')),
& F( [5 Z2 \) O3 s2 R6 L% e; V" j: l PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('01/05/2000','DD/MM/YYYY')),) S4 f6 G; m1 ]1 Y3 o8 A; s8 f/ ~
PARTITION sales_2000 VALUES LESS THAN(MAXVALUE)1 G% q4 _4 K8 ~! I- y
);
1 p) e- t9 K5 {% L2 g2 V 插入数据:9 [4 j+ t) \$ D: c7 t
Insert into sales_range values(1,2,3,to_date('21-04-2000','DD-MM-YYYY'));- ]. q. o O# V5 B" n5 }( A- P
Insert into sales_range values(1,2,3,sysdate);# K6 R: t* q* j* L6 B5 q6 X
选择数据:
+ N+ \$ f: ^* y' s* u' q0 ` select * from sales_range;
_! y% J7 p8 J( H E9 \+ D5 l B$ ] select * from sales_range partition(sales_apr2000);5 F1 p$ z' _: f; @2 s' k
select * from sales_range partition(sales_mar2000);
0 [, c+ }" V, ~ select * from sales_range partition(sales_2000);; P+ @% s7 J1 |. E
按照多个列分区:
# ]3 L7 ]) f! `' B5 F4 V1 z CREATE TABLE sales_range17 u# d9 Q4 w; M- V+ I$ |, E
(salesman_id NUMBER(5),9 R* v* P4 @* P+ Y" Q& O i
salesman_name VARCHAR2(30),) D1 `7 t* {4 f2 ?2 t7 J. a
sales_amount NUMBER(10),! v# r% o1 U& _8 a& e! P- W
sales_date DATE)
- d9 {( {: i* q PARTITION BY RANGE(sales_date, sales_amount)1 k( i! T6 R/ p' j7 B% G
(
& D' l7 z5 Y- \& f+ j% V! _ PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('01/02/2000','DD/MM/YYYY'),1000),4 e* B0 v% s* J: [
PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('01/03/2000','DD/MM/YYYY'),2000),& D/ @, U0 `7 x( K, r
PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('01/04/2000','DD/MM/YYYY'),3000),9 L, ` Z' H+ }" x$ ]- v& T
PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('01/05/2000','DD/MM/YYYY'),4000),
4 L" W" r+ b/ f9 y( `. D6 X) A PARTITION sales_2000 VALUES LESS THAN(MAXVALUE, MAXVALUE)
) t, T% g+ |/ P8 M; I. O( [ ); |