分区类别:哈希(散列)分区,范围(区间)分区,列表分区,间隔分区,引用分区,组合分区。
分区的好处:提高可用性,减少管理负担,改善语句性能。 分区可以提高查询速度,但不是一定会提高查询速度。
创建表空间:
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)
)
);