SQL分区

---分区 把一个表的数据按照字段的值，保存在多个区域，查询时，仅仅查询某一个或者多个区域，避免全表扫描。

--建表的时候，需要指定分区字段 以及 预制的分区。先要划好分区，才能插入数据。

列表分区

---1.不连续的，零散的字段的值，作为分区，常采用list列表类型

CREATE TABLE MYEMP2 ( EMPNO NUMBER(4) PRIMARY KEY, ENAME VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7,2), COMM NUMBER(7,2), DEPTNO NUMBER(7,2) )
PARTITION BY LIST(DEPTNO) 
( 
PARTITION MYEMP_DEPTNO_10 VALUES (10), 
PARTITION MYEMP_DEPTNO_20 VALUES (20), 
PARTITION MYEMP_DEPTNO_30 VALUES (30), 
PARTITION MYEMP_DEPTNO_40 VALUES (40) 
)

---只查询某一个具体的分区的名称进行查询

select * from MYEMP2 partition(MYEMP_DEPTNO_10);

---可以根据分区来删除数据, 清空分区10的数据

alter table MYEMP2 truncate partition MYEMP_DEPTNO_10;

---后续增加分区

alter table MYEMP2 add partition MYEMP_DEPTNO_10 VALUES (10);

---查询分区的数据

select * from MYEMP2 partition(MYEMP_DEPTNO_10); --直接查分区

--删除数据

delete from MYEMP2 partition(MYEMP_DEPTNO_10); ---删除指定分区数据
alter table MYEMP2 truncate partition(MYEMP_DEPTNO_10); --清空指定分区的数据
alter table MYEMP2 drop partition(MYEMP_DEPTNO_10); --删除分区

---2.创建一个分区表 类型为 range

CREATE TABLE emp_tag4 ( EMPNO NUMBER(4) , ENAME VARCHAR2(10),  sal number(7,2) )
PARTITION BY range(sal) 
( 
PARTITION p_1 VALUES less than (1000), 
PARTITION p_2 VALUES less than (2000), 
PARTITION p_3 VALUES less than (3000), 
PARTITION p_4 VALUES less than (4000), 
PARTITION p_5 VALUES less than (5000), 
PARTITION p_6 VALUES less than (MAXVALUE) ---如果有maxvalue,那么不可以再增加分区，无法再增加5000-6000，6000以上
);

 

----3.哈希（散列）分区:

CREATE TABLE 表名 (列名 数据类型 [,列名 数据类型]...)
PARTITION BY HASH (COL) 
( 
PARTITION PART01 TABLESPACE HASH_TS01, 
PARTITION PART02 TABLESPACE HASH_TS02, 
PARTITION PART03 TABLESPACE HASH_TS03 
)

简写：

CREATE TABLE 表名 (列名 数据类型 [,列名 数据类型]...)
PARTITION BY HASH (empno) PARTITIONS 8
STORE IN (emp1,emp2,emp3,emp4,emp5,emp6,emp7,emp8);

----4.组合分区(组合范围散列分区)
基于 范围分区 和 列表分区，表首先按某列进行范围分区，然后再按某列进行列表分区，分区之中的分区被称为子分区
【例】按入职日期进行范围分区，再按DEPTNO进行LIST子分区
CREATE TABLE 表名 (列名 数据类型 [,列名 数据类型]...)

PARTITION BY RANGE(HIREDATE) SUBPARTITION BY LIST (DEPTNO)
(
PARTITION P1 VALUES LESS THAN(TO_DATE('1981-01-01','YYYY-MM-DD'))
(
SUBPARTITION P1A VALUES (10) ,
SUBPARTITION P1B VALUES (20),
SUBPARTITION P1C VALUES (30),
SUBPARTITION P1D VALUES (40)
),
PARTITION P2 VALUES LESS THAN (TO_DATE('1982-01-01','YYYY-MM-DD'))
(
SUBPARTITION P2A VALUES (10) ,
SUBPARTITION P2B VALUES (20),
SUBPARTITION P2C VALUES (30),
SUBPARTITION P2D VALUES (40)
),
PARTITION P3 VALUES LESS THAN (TO_DATE('1983-01-01','YYYY-MM-DD'))
(
SUBPARTITION P3A VALUES (10) ,
SUBPARTITION P3B VALUES (20),
SUBPARTITION P3C VALUES (30),
SUBPARTITION P3D VALUES (40)
),
PARTITION P4 VALUES LESS THAN (TO_DATE('1988-01-01','YYYY-MM-DD'))
(
SUBPARTITION P4A VALUES (10) ,
SUBPARTITION P4B VALUES (20),
SUBPARTITION P4C VALUES (30),
SUBPARTITION P4D VALUES (40)
)
)

-----5.复合分区(复合范围散列分区)
基于 范围分区 和 散列分区，表首先按某列进行范围分区，然后再按某列进行散列分区

CREATE TABLE 表名 (列名 数据类型 [,列名 数据类型]...)
partition by range(transaction_date) subpartition by hash(transaction_id) 
subpartitions 3 store in (dinya_space01,dinya_space02,dinya_space03) 
( 
partition part_01 values less than(to_date(‘2006-01-01’,’yyyy-mm-dd’)), 
partition part_02 values less than(to_date(‘2010-01-01’,’yyyy-mm-dd’)), 
partition part_03 values less than(maxvalue) 
);

---如果有一个表开始的时候没有见分区，现在需要给表格加分区的解决办法
---1. 重新建表，有新的分区

CREATE TABLE emp_tag5 ( EMPNO NUMBER(4) , ENAME VARCHAR2(10), sal number(7,2) )
PARTITION BY range(sal) 
( 
PARTITION p_1 VALUES less than (1000), 
PARTITION p_2 VALUES less than (2000), 
PARTITION p_3 VALUES less than (3000), 
PARTITION p_4 VALUES less than (4000), 
PARTITION p_5 VALUES less than (5000), 
partition p_7 VALUES less than (6000),
PARTITION p_6 VALUES less than (MAXVALUE)
);

2. 插入原表中已有有的数据

insert into emp_tag5 select * from empt_tag4

3. 把新表名称，修改回去

alter table emp_tag4 rename to emp_tag4_bak;
alter table emp_tag5 rename to emp_tag4;

----索引和分区区别

1. 分区不用建额外的索引表，占空间
2. 分区的性能优于索引