分区分类
- 字段范围分区 range `Oracle 8`
- 哈希分区 hash `Oracle 8i`
- 组合分区 range-hash,range-list ... `Oracle 9i`
- 列表分区 list `Oracle 9i`
- 间隔分区 interval `Oracle 11g`
- 系统分区 System `Oracle 11g`
- 引用分区 Reference `Oracle 11g`
字段范围 range
- NUMBER类型字段
CREATE TABLE emp (
empno NUMBER(4),
ename VARCHAR2(30),
sal NUMBER
)
PARTITION BY RANGE(empno) (
partition e1 values less than (1000) tablespace ts1,
partition e2 values less than (2000) tablespace ts2,
partition e3 values less than (MAXVALUE) tablespace ts3
);
- VARCHAR2类型字段
CREATE TABLE emp
( id NUMBER(5) PRIMARY KEY,
name VARCHAR2(50) NOT NULL,
phone VARCHAR2(15),
email VARCHAR2(100) )
PARTITION BY RANGE ( name )
( PARTITION p1 VALUES LESS THAN ('L') TABLESPACE ts1,
PARTITION p2 VALUES LESS THAN (MAXVALUE) TABLESPACE ts2 )
- 基于时间类型字段
CREATE TABLE t1 (id NUMBER, c1 DATE)
PARTITION BY RANGE (c1)
(PARTITION t1p1 VALUES LESS THAN (TO_DATE('2007-11-01', 'YYYY-MM-DD')),
PARTITION t1p2 VALUES LESS THAN (TO_DATE('2007-12-01', 'YYYY-MM-DD')),
PARTITION t1p3 VALUES LESS THAN (TO_DATE('2008-01-01', 'YYYY-MM-DD')),
PARTITION t1p4 VALUES LESS THAN (MAXVALUE)
);
### Hash分区
- 两种写法
create table emp2 (
empno number(4),
ename varchar2(30),
sal number
)
partition by hash(empno) (
partition e1 tablespace emp1,
partition e2 tablespace emp2,
partition e3 tablespace emp3,
partition e4 tablespace emp4
);
-- tablespace 可以去掉
create table emp2 (
empno number(4),
ename varchar2(30),
sal number
)
PARTITION BY HASH(empno)
PARTITIONS 3
-- STORE IN (empts1, empts2, empts3);
组合分区
- 8i 之前 只支持 range-hash
- 9i 支持 range-list
- 11g 支持更多 详情看这[http://www.orafaq.com/wiki/Composite_partitioning](http://www.orafaq.com/wiki/Composite_partitioning)
CREATE TABLE orders(
ord# NUMBER,
orderdate DATE,
prod# NUMBER,
quantity NUMBER)
PARTITION BY RANGE(orderdate)
SUBPARTITION BY HASH(prod#) SUBPARTITIONS 4 -- STORE IN(ts1, ts2, ts3, ts4)
( PARTITION q1 VALUES LESS THAN (TO_DATE('01-APR-2009', 'DD-MON-YYYY')),
PARTITION q2 VALUES LESS THAN (TO_DATE('01-JUL-2009', 'DD-MON-YYYY')),
PARTITION q3 VALUES LESS THAN (TO_DATE('01-OCT-2009', 'DD-MON-YYYY')),
PARTITION q4 VALUES LESS THAN (MAXVALUE)
);
List分区
CREATE TABLE myemp_work (
emp# NUMBER PRIMARY KEY,
ename VARCHAR2(30),
salary NUMBER(8,2),
deptno NUMBER)
PARTITION BY LIST (deptno) (
PARTITION p10 VALUES (10),
PARTITION p20 VALUES (20),
PARTITION p30 VALUES (30,40));
间隔分区 Interval
- 11g引入，作为range分区的补充，当插入的值超过所有其他分区的范围时，自动创建分区
- 限制
- 分区字段必须是NUMBER或者DATE类型
- 不支持索引组织表 index-organized table
- 不能在间隔分区创建域索引domain index
-- 创建salary有限值的分区
create table test
(sno number(6),
last_name varchar2(30),
salary number(6))
partition by range(salary)
(
partition p1 values less than (5000),
partition p2 values less than (10000),
partition p3 values less than (15000),
partition p4 values less than (20000));
-- 插入一条超过分区区间的记录
insert into test values (1,'Michel',25000);
-- 抛出分区key不能映射到某个分区的错误
ORA-14400:
inserted partition key does not map to any partition
-- interval分区用法
create table test
(sno number(6),
last_name varchar2(30),
salary number(6))
partition by range(salary)
Interval (5000) -- 每5000一个分区
(
partition p1 values less than (5000),
partition p2 values less than (10000),
partition p3 values less than (15000),
partition p4 values less than (20000));
系统分区
- 11g中引入的一种分区方法，它允许应用程序控制分区选择。系统分区不像其他分区模式那样具有分区键。
-- 创建若干分区
CREATE TABLE syspart (c1 NUMBER, c2 NUMBER)
PARTITION BY SYSTEM (
PARTITION p1 TABLESPACE ts1,
PARTITION p2 TABLESPACE ts2,
PARTITION p3 TABLESPACE ts3
);
-- 插入数据时程序选择指定分区
INSERT INTO syspart PARTITION (p1) VALUES (1, 2);
INSERT INTO syspart PARTITION (p2) VALUES (3, 4);
引用分区
- 11g中介绍的一种分区方法。使用引用分区，子表可以从父表继承分区特征。
-- 父表
CREATE TABLE orders (
order_id NUMBER PRIMARY KEY,
order_date DATE NOT NULL,
customer_id NUMBER NOT NULL,
shipper_id NUMBER)
PARTITION BY RANGE (order_date) (
PARTITION y1 VALUES LESS THAN (TO_DATE('01-JAN-2006', 'DD-MON-YYYY')),
PARTITION y2 VALUES LESS THAN (TO_DATE('01-JAN-2007', 'DD-MON-YYYY')),
PARTITION y3 VALUES LESS THAN (TO_DATE('01-JAN-2008', 'DD-MON-YYYY')));
-- 子表外键继承父表的分区
CREATE TABLE order_items (
order_id NUMBER NOT NULL,
product_id NUMBER NOT NULL,
price NUMBER,
quantity NUMBER,
CONSTRAINT order_items_fk FOREIGN KEY (order_id) REFERENCES orders)
PARTITION BY REFERENCE (order_items_fk);
分区压缩
- 11g分区可以被单独压缩,10g只支持整表压缩
查看压缩特性
-- 创建分区表，指定分区是否压缩
CREATE TABLE test
(sno NUMBER(6),
last_name VARCHAR2(30),
salary NUMBER(6))
PARTITION BY RANGE (salary)
INTERVAL (5000)
(
PARTITION p1 VALUES LESS THAN (5000) COMPRESS,
PARTITION p2 VALUES LESS THAN (10000) NOCOMPRESS,
PARTITION p3 VALUES LESS THAN (15000) COMPRESS,
PARTITION p4 VALUES LESS THAN (20000)) NOCOMPRESS;
-- 检查该表的压缩属性
SELECT table_name, partition_name, compression
FROM user_tab_partitions
WHERE table_name='TEST';
--单个关键字指定整个表的所有分区可被压缩
CREATE TABLE test
(sno NUMBER(6),
last_name VARCHAR2(30),
salary NUMBER(6))
COMPRESS
PARTITION BY RANGE (salary)
INTERVAL (5000)
(
PARTITION p1 VALUES LESS THAN (5000) ,
PARTITION p2 VALUES LESS THAN (10000),
PARTITION p3 VALUES LESS THAN (15000),
PARTITION p4 VALUES LESS THAN (20000));