oracle表分区理解和语法(有助于初学)

当表越来越大时，可以考虑使用分区表，分区表一般用于大型数据库
普通表是单表单段，分区表是单表多段，每个段称为一个分区。分区表可以把不同段放在不同的表空间中。
访问数据时一般会在某个分区中找数据。不会对表的所有分区进行查找【例如计费信息按月分区，当查12月的分区时不会从1-11月的分区找数据。】
分区可以存储在不同的磁盘，提高访问性能和安全性
可以独立地备份和恢复每个分区




1  分区方法


范围分区    散列分区   列表分区    复合分区


1.1 范围分区
以表中的一个列或一组列的值的范围分区
用于分区的字段可以是数值型、日期型、字符型
例如：移动通话记录能够按照月份来分区
优点：便于管理
范围分区的语法：
PARTITION BY RANGE (column_name)
(
  PARTITION part1 VALUES LESS THAN(range1),
  PARTITION part2 VALUES LESS THAN(range2),
  ...
  [PARTITION partN VALUES LESS THAN(MAXVALUE)]
);




eg1:
SQL> CREATE TABLE Sales
(    Product_ID varchar2 (5),    Sales_Cost number (10)
)
PARTITION BY RANGE (Sales_Cost)   //根据 Sales_Cost 创建分区
(
    PARTITION P1 VALUES LESS THAN (1000),    //包含销售成本低于1000 
的所有产品的值 
    PARTITION P2 VALUES LESS THAN (2000),
    PARTITION P3 VALUES LESS THAN (3000)   //p1 p2 p3 是分区的名称


);


eg2:
SQL> CREATE TABLE SALES2 (
PRODUCT_ID VARCHAR2(5),SALES_DATE DATE NOT NULL,
SALES_COST NUMBER(10))
PARTITION BY RANGE (SALES_DATE)
(
  PARTITION P1 VALUES LESS THAN (DATE ‘2003-01-01’),
  PARTITION P2 VALUES LESS THAN (DATE ‘2004-01-01’), 
  PARTITION P3 VALUES LESS THAN (MAXVALUE)
);






1.2 散列分区[hash分区]
允许用户对不具有逻辑范围的数据进行分区
例如:数据分布不均匀，1月份10条记录，2月份1千万记录。这时使用范围分区没有意义.
Oracle利用自己的hash算法[未公布]，能够保证数据在预先确定数目的分区中平均分布散列分区语法
特点：能把数据平均分配到各个分区
PARTITION BY HASH (column_name)
PARTITIONS number_of_partitions;
或
PARTITION BY HASH (column_name)
( PARTITION part1 [TABLESPACE tbs1],
  PARTITION part2 [TABLESPACE tbs2],
  ...
  PARTITION partN [TABLESPACE tbsN]);


eg1:


sql> create table employee
(
employee_id varchar2(5),
employee_name varchar2(20),
department varchar2(10)
)
partition by hash(department)  //在表employee上创建分区键department
(
partition D1,        //D1,D2,D3是分区的名字，创建了三个分区
partition D2,
partition D3,
);
)






2 表分区的类型


2.1  列表分区
允许用户将不相关的数据组织在一起
列中的值是有限的若干个，例如：部门、城市


列表分区的语法：
PARTITION BY LIST (column_name)
(
  PARTITION part1 VALUES (values_list1),
  PARTITION part2 VALUES (values_list2),
  ...
  PARTITION partN VALUES (DEFAULT)
);


SQL> CREATE TABLE Employee1
(
    Emp_ID number (4),
    Emp_Name varchar2 (14),
    Emp_Address varchar2 (15)
)
PARTITION BY LIST (Emp_Address)   //根据职员住址在表上创建的列表分区
(
    Partition north values ('芝加哥'),         //包含住在芝加哥的职员的记录
    Partition west values ('旧金山', '洛杉矶'),
    Partition south values ('亚特兰大', '达拉斯', '休斯顿'),
    Partition east values ('纽约', '波斯顿')
);


north,west,south,east是分区的名称








2.2 复合分区
范围分区与散列分区或列表分区的组合，range+hash、range+list
复合分区的语法：
先根据范围对数据进行分区，然后再这些分区内创建散列子分区。


PARTITION BY RANGE (column_name1)
SUBPARTITION BY HASH (column_name2)
SUBPARTITIONS number_of_partitions
(
  PARTITION part1 VALUE LESS THAN(range1),
  PARTITION part2 VALUE LESS THAN(range2),
  ...
  PARTITION partN VALUE LESS THAN(MAXVALUE)
);




eg:
SQL> CREATE TABLE SALES
(
    PRODUCT_ID VARCHAR2 (5),    SALES_DATE DATE NOT NULL,
    SALES_COST NUMBER (10)
)
PARTITION BY RANGE (SALES_DATE)         //在表的 Sales_Date列中创建范围分区
SUBPARTITION BY HASH (PRODUCT_ID)       //在表的 Product_ID列创建散列子分区
SUBPARTITIONS 5                        //在每个范围分区中创建 5 个散列子分区


(
    PARTITION S1 VALUES LESS THAN (TO_DATE('01/4月/2001', 'DD/MON/YYYY')),
    PARTITION S2 VALUES LESS THAN (TO_DATE('01/7月/2001', 'DD/MON/YYYY')),
    PARTITION S3 VALUES LESS THAN (TO_DATE('01/9月/2001', 'DD/MON/YYYY')),
    PARTITION S4 VALUES LESS THAN (MAXVALUE)
);


s1,s2,s3,s4 是创建的四个范围分区的名称
先按照Sales_Date进行范围分区，共创建4个分区s1,s2,s3,s4；再在每个分区中分别创建5个Hash子分区，共4*5=20个分区










3 操纵已分区的表 
分区表的方便之处在于，所有的select与dml操作不需要指定分区名，只要知道表名就可以。Oracle会自动地判断需要的数据在那个分区中。
INSERT INTO SALES VALUES ('P001',  2000);
INSERT INTO SALES VALUES ('P002',  2508);
INSERT INTO SALES VALUES ('P003',  780);
INSERT INTO SALES VALUES ('P004',  1080);


查询、修改和删除分区表时可以显式指定要操作的分区,不过一般这样做没有什么意义。 
SELECT * FROM SALES3 PARTITION (P3);
DELETE FROM SALES3 PARTITION (P2);












4. 分区维护操作
分区维护操作修改已分区表的分区。
分区维护的类型：
计划事件 － 定期删除最旧的分区,如定期删除通话记录中最旧的记录
非计划事件 － 最初分区的不够合理，比如，范围分区时把分区弄得太大，我们需要拆分一下。或者分区太小，需要合并多个分区。
分区维护操作有：
添加分区
删除分区
截断分区
合并分区
拆分分区


创建好的分区
SQL> CREATE TABLE Sales
(    Product_ID varchar2 (5),    Sales_Cost number (10)
)
PARTITION BY RANGE (Sales_Cost)   //根据 Sales_Cost 创建分区
(
    PARTITION P1 VALUES LESS THAN (1000),    //包含销售成本低于1000 
的所有产品的值 
    PARTITION P2 VALUES LESS THAN (2000),
    PARTITION P3 VALUES LESS THAN (3000)   //p1 p2 p3 是分区的名称


);


4.1 维护分区
添加分区 – 在最后一个分区之后添加新分区
SQL> ALTER TABLE SALES
     ADD PARTITION P4 VALUES LESS THAN (4000);


删除分区 – 删除一个指定的分区，分区的数据也随之删除
SQL> ALTER TABLE SALES DROP PARTITION P4;


截断分区 – 删除指定分区中的所有记录 
SQL> ALTER TABLE SALES TRUNCATE PARTITION P3;


合并分区 - 将范围分区或复合分区的两个相邻分区连接起来
SQL> ALTER TABLE SALES 
MERGE PARTITIONS P1, P2 INTO PARTITION P12;


拆分分区 - 将一个大分区中的记录拆分到两个分区中
SQL> ALTER TABLE SALES SPLIT PARTITION P12 AT (1500)
INTO (PARTITION P1, PARTITION P2);