Oracle 分区表

分区类别：哈希（散列）分区，范围（区间）分区，列表分区，间隔分区，引用分区，组合分区。

分区的好处：提高可用性，减少管理负担，改善语句性能。 分区可以提高查询速度，但不是一定会提高查询速度。

创建表空间：

create tablespace P1   
datafile 'F:\app\tdn084\oradata\orcl\p1.dbf'   
size 100M   
autoextend on next 5M maxsize 3000M; 


create tablespace P2   
datafile 'F:\app\tdn084\oradata\orcl\p2.dbf'   
size 100M   
autoextend on next 5M maxsize 3000M; 

哈希（散列）分区：散列分区一定要使用2的幂（两个、四个、八个。。。）。

CREATE TABLE EMP_PAR
(EMPNO INT,
ENAME  VARCHAR2(20)
)
PARTITION BY HASH(EMPNO)
(PARTITION PART_1 TABLESPACE P1,              -- 这里的表空间在同一磁盘上，未必会分散磁盘IO加快执行速度。
 PARTITION PART_2 TABLESPACE P2
)

范围（区间）分区：

CREATE TABLE EMP_PAR_RANGE
(EMPNO INT,
ENAME  VARCHAR2(20)
)
PARTITION BY RANGE(EMPNO)
(PARTITION PART_1 VALUES LESS THAN (7600),
 PARTITION PART_2 VALUES LESS THAN (9999),
 PARTITION PART_3 VALUES LESS THAN (MAXVALUE),
); 

CREATE TABLE EMP_PAR_RANGE_1
(EMPNO INT,
ENAME  VARCHAR2(20)
)
PARTITION BY RANGE(EMPNO)
(PARTITION PART_1 VALUES LESS THAN (7600) TABLESPACE P1,
 PARTITION PART_2 VALUES LESS THAN (9999) TABLESPACE P2
)

列表分区：

CREATE TABLE EMP_PAR_LIST
(EMPNO INT,
ENAME  VARCHAR2(20)
)
PARTITION BY LIST (EMPNO)
(PARTITION PART_1 VALUES ('7369','7499','7521'),
 PARTITION PART_2 VALUES ('7566','7654','7698'),
 PARTITION PART_3 VALUES (DEFAULT)
);

间隔分区：自动创建分区，即使事务取消，分区还存在。

CREATE TABLE EMP_PAR_INTERVAL
(EMPNO INT,
ENAME  VARCHAR2(20)
)
PARTITION BY RANGE(EMPNO)
INTERVAL (10)
STORE IN (USERS,P1)
(PARTITION PART_0 VALUES LESS THAN (10)
);

INSERT INTO EMP_PAR_INTERVAL(EMPNO,ENAME)
SELECT ROWNUM,'AA' FROM DUAL CONNECT BY ROWNUM<=20;

SELECT * FROM USER_TAB_PARTITIONS A WHERE A.TABLE_NAME='EMP_PAR_INTERVAL';

引用分区：主要用于主外键表

CREATE TABLE ORDERS
(
ORDERID  INT PRIMARY KEY,
ORDER_DATE DATE,
DATA  VARCHAR2(20)
)
ENABLE ROW MOVEMENT
PARTITION BY RANGE (ORDER_DATE)
(
PARTITION PART_2009 VALUES LESS THAN (TO_DATE('2010-01-01','YYYY-MM-DD')),
PARTITION PART_2010 VALUES LESS THAN (TO_DATE('2011-01-01','YYYY-MM-DD'))
);


CREATE TABLE ORDERS_LINE
(
ORDERID   INT,
ORDER_LINE INT,
DATA VARCHAR2(20),
CONSTRAINT ORL_PK PRIMARY KEY(ORDERID,ORDER_LINE),
CONSTRAINT ORL_FK FOREIGN KEY(ORDERID) REFERENCES ORDERS
)
ENABLE ROW MOVEMENT
PARTITION BY REFERENCE(ORL_FK)

组合分区：

CREATE TABLE ORDERS_COMPOSITE
(
ORDERID  INT PRIMARY KEY,
ORDER_DATE DATE,
DATA  VARCHAR2(20)
)
ENABLE ROW MOVEMENT
PARTITION BY RANGE (ORDER_DATE)
SUBPARTITION BY LIST(DATA)
(

PARTITION PART_2009 VALUES LESS THAN (TO_DATE('2010-01-01','YYYY-MM-DD'))
(
SUBPARTITION PART_2009_SUBPART_01 VALUES(1,2,3,4),
SUBPARTITION PART_2009_SUBPART_02 VALUES(5,6,7,8)
),

PARTITION PART_2010 VALUES LESS THAN (TO_DATE('2011-01-01','YYYY-MM-DD'))
(
SUBPARTITION PART_2010_SUBPART_01 VALUES(1,2,3,4),
SUBPARTITION PART_2010_SUBPART_02 VALUES(5,6,7,8),
SUBPARTITION PART_2010_SUBPART_03 VALUES(9)
)
);