oracle 表的分区

分区：-是指将表拆分为更小且更便于管理的几个部分的方法。

-根据键值范围将单个逻辑表拆分为多个

-在数据仓库应用程序中非常有用

-分区被独立存储和管理

-通过引用逻辑表名称进行查询和更新

特点：

-表的所有分区共享逻辑属性,如列和约束条件的定义,但同一表的不同分区可以具有不同的物理属性,如存储规范

-表的所有子分区具有相同的逻辑属性,同一个分区的子分区只能具有相同的物理属性

-一般将分区存放在不同的表空间中

-减少所有分区的数据同时损坏的可能性

-可以独立地备份和恢复每个分区

–分区独立性允许同时将各种分区用于各种用途

–分区透明性:程序应不受它们所访问的数据是否已分区以及如何分区的影响

注意事项：

数据类型限制:不能具有LONG/LONG RAW数据类型的列，Date列必须通过TO_DATE指定年份格式

不能指定NULL或空字符串作为分区边界value_list中的值

关键字：MAXVALUE，此关键字表示排序高于此数据类型的其他任意值(包含NULL值)的无限虚拟值。因此如果插入为NULL的值，必须指定MAXVALUE的分区边界，否则插入会失败

3.1 分区类型

范围分区

散列分区

复合分区

列表分区

3.2 范围分区语法

–创建表区间

CREATE TABLESPACE A1 DATAFILE ‘C:\app\Administrator\oradata\A1’ SIZE 1M;

CREATE TABLESPACE A2 DATAFILE ‘C:\app\Administrator\oradata\A2’ SIZE 1M;

CREATE TABLESPACE A3 DATAFILE ‘C:\app\Administrator\oradata\A3’ SIZE 1M;

–创建队列

CREATE SEQUENCE sqe_test;

CREATE TABLE test01(

sqe_test.nextval,

id number(8) primary key,

name varchar2(20) not null)

partition by range (id)

(partition B1 VALUES LESS THAN(250) TABLESPACE A1,

PARTITION B2 VALUES LESS THAN (500) TABLESPACE A2,

PARTITION B3 VALUES LESS THAN (MAXVALUE) TABLESPACE A3

);

-----------------创建员工表，并三个分区-------------

–切换身份到系统管理者

connect system/123456;

–创建表空间

CREATE TABLESPACE P1 datafile ‘C:\app\Administrator\oradata\p1’ size 1M;

create Tablespace p2 datafile ‘C:\app\Administrator\oradata\p2’ size 1M;

create TABLESPACE P3 datafile ‘C:\app\Administrator\oradata\p3’ size 1M;

–创建表并分区

CREATE TABLE EMPLOYES (

id number(5) primary key,

name varchar2(20) not null,

birth date)

partition by range(id)

( PARTITION t1 VALUES LESS THAN(250) tablespace p1,

PARTITION t2 VALUES LESS THAN(500) tablespace p2,

PARTITION t3 VALUES LESS THAN (MAXVALUE) tablespace p3);

–插入测试数据

INSERT INTO EMPLOYES VALUES(1,‘小二’,to_date(‘1921-01-01’,‘yyyy-mm-dd’));

INSERT INTO EMPLOYES VALUES(360,‘小三’,to_date(‘1966-09-01’,‘yyyy-mm-dd’));

INSERT INTO EMPLOYES VALUES(785,‘张三’,to_date(‘1956-01-01’,‘yyyy-mm-dd’));

–分区只对查询做限制，要么指明表区间，要么就直接from

按照分区查询

SELECT * FROM EMPLOYES PARTITION(t1);

------------------------------------多列分区-------------------------------

– 多列分区

CREATE TABLE 表名

(

列名 数据类型(宽度,精度) 是否为空 约束/属性,

…

)

PARTITION BY RANGE(分区字段列1,分区字段列2,…)

(

PARTITION 分区名 LESS THAN (值1,值2,…) TABLESPACE 表空间名称,

…

);

–案例

CREATE TABLE ord_mast_new

(

orderno VARCHAR2(5) CONSTRAINT prim_new PRIMARY KEY,

odate DATE,

vencode VARCHAR2(5),

o_status CHAR(1) CHECK(o_status in(‘p’, ‘c’)),

del_date DATE

)

PARTITION BY RANGE(orderno,vencode)

(

PARTITION om1 VALUES LESS THAN(‘o010’, ‘v010’) TABESPACE t1,

PARTITION om2 VALUES LESS THAN(‘o020’, ‘v020’) TABESPACE t2,

PARTITION om3 VALUES LESS THAN(MAXVALUE,MAXVALUE) TABESPACE t3

);

INSERT INTO ord_mast_new VALUES(‘o001’,TO_DATE(‘2013-12-31’,‘YYYY-MM-DD’), ‘V001’,‘p’,TO_DATE(‘2013-12-31’,‘YYYY-MM-DD’));

INSERT INTO ord_mast_new VALUES(‘o023’,TO_DATE(‘2003-12-31’,‘YYYY-MM-DD’), ‘V002’,‘p’,TO_DATE(‘2003-12-31’,‘YYYY-MM-DD’));

注意事项： 对于多列的分区，优先级将从左到右。

3.3 散列分区

CREATE TABLE 表名

(

… )

partition by hash(分区列)

partition (分区数量) --可省略

(partition p1 tablespace t1,

partition p2 tablespace t2,

…

);

特点：

-如果不知道将有多少数据映射到指定的范围，散列分区非常有用。

-分区的数目应是 2 的幂

-----------------------创建部门表departments,对deptno部门编号创建两个散列分区-----------------------------------

CREATE TABLE departments (

deptno number(5) primary key,

dname varchar2(20),

loc varchar2(20)

)

partition by hash(deptno)

(

partition p1 ,

partition p2

);

–检测数据

INSERT INTO departments VALUES(10,‘ACCOUNTING’,‘NEW YORK’);

INSERT INTO departments VALUES(20,‘RESEARCH’,‘DALLAS’);

INSERT INTO departments VALUES(30,‘SALES’,‘CHICAGO’);

INSERT INTO departments VALUES(40,‘OPERATIONS’,‘BOSTON’);

–验证数据

SELECT * FROM departments;

select * from departments partition(p1);

select * from departments partition(p2);

3.4 复合分区

复合分区的语法：

CREATE TABLE <table_name>

(

…

)

PARTITION BY RANGE(column_list)

SUBPARTITION BY HASH(column_list)

SUBPARTITIONS <number_of_subpartitions>

STORE IN(<tablespace_name_list>, < >, < >,…)

(

PARTITION <parition_name1> VALUES LESS THAN()

(

SUBPARTITION <partition_name> TABLESPACE <tablespace_name>,

SUBPARTITION <partition_name> TABLESPACE <tablespace_name>,

SUBPARTITION <partition_name> TABLESPACE <tablespace_name>

),

PARTITION <partition_name2> VALUES LESS THAN()

(

SUBPARTITION <partition_name> TABLESPACE <tablespace_name>,

SUBPARTITION <partition_name> TABLESPACE <tablespace_name>,

SUBPARTITION <partition_name> TABLESPACE <tablespace_name>

)

…

);

–案例：对工资等级表进行分区，先按等级编号进行两个范围分区，每个分区再按照工资范围进行两个散列分区。

CREATE TABLE salgrades(

grade number(5) primary key,

losal number(8,2),

hisal number(8,2))

PARTITION BY range (grade)

SUBPARTITION BY HASH (losal,hisal)

(

PARTITION P1 VALUES less than(10)

(

SUBPARTITION t1,

SUBPARTITION t2

),

partition p2 VALUES less than (20)

(

SUBPARTITION t3,

SUBPARTITION t4

)

);

– 插入数据：

INSERT INTO salgrade VALUES(1,700,1200);

INSERT INTO salgrade VALUES(2,1201,1400);

INSERT INTO salgrade VALUES(3,1401,2000);

INSERT INTO salgrade VALUES(4,2001,3000);

INSERT INTO salgrade VALUES(5,3001,9999);

– 查询验证

SELECT * FROM salgrade;

SELECT * FROM salgrade PARTITION(p1);

SELECT * FROM salgrade PARTITION(p2);

SELECT * FROM salgrade SUBPARTITION(sp1);

SELECT * FROM salgrade SUBPARTITION(sp2);

– 再插入数据：

INSERT INTO salgrade VALUES(11,10001,12000);

INSERT INTO salgrade VALUES(12,12001,14000);

INSERT INTO salgrade VALUES(13,14001,20000);

INSERT INTO salgrade VALUES(14,20001,30000);

INSERT INTO salgrade VALUES(15,30001,99999);

– 查询验证

SELECT * FROM salgrade;

SELECT * FROM salgrade PARTITION(p1);

SELECT * FROM salgrade PARTITION(p2);

SELECT * FROM salgrade SUBPARTITION(sp3);

SELECT * FROM salgrade SUBPARTITION(sp4);

3.5 列表分区

特点：

-基于 PARTITION BY LIST 中指定的分区键对表进行分区

-明确地控制行到分区的映射

-指定分区键的离散值

---------------------创建销售表，将指定的销售人员进行分区，分为东部区域和西部区域----------

CREATE TABLE sales_details

(

sales_id NUMBER(6),

sman_name VARCHAR2(20),

sales_state VARCHAR2(15),

sales_date DATE,

amount NUMBER(10)

)

PARTITION BY LIST(sman_name)

(

PARTITION sales_east VALUES(‘antony’,‘henry’,‘jack’),

PARTITION sales_west VALUES(‘peter’,‘serena’,‘venus’)

);

INSERT INTO sales_details VALUES(10,‘antony’,‘California’,TO_DATE(‘2003-4-1’,‘YYYY-MM-DD’),6000);–映射到sales_east

INSERT INTO sales_details VALUES(20,‘peter’,’ Illinois’,TO_DATE(‘2003-4-10’,‘YYYY-MM-DD’),5000);–映射到sales_west

INSERT INTO sales_details VALUES(5,‘george’,‘New York’,TO_DATE(‘2003-4-1’,‘YYYY-MM-DD’),6000);–不映射到任何分区

– 查询验证分区

SELECT * FROM sales_details;

SELECT * FROM sales_details PARTITION(sales_east);

SELECT * FROM sales_details PARTITION(sales_west);