基于oracle下航空系统之qar数据库应用实例开发13管理分区表

1、管理分区表管理分区表- indiana jones北风网LIST 概述 分区类型 范围分区 列表分区 Hash分区 复合分区 访问分区表的数据 管理维护分区表北风网分区表概述分区表概述单表多段，每个段是一个分区普通表的局限性,存储海量数据会带来性能问题。没有分区表之前，海量数据存储采用的方法使用分区表能加速海量数据查询与维护的性能 访问数据时一般会在某个分区中找数据。不会对表的所有分区进行查找【例如qar事件的超限信息按年分区】 分区可以存储在不同的磁盘，提高访问性能和安全性 可以独立地备份和恢复每个分区 选择合适的分区方法，正确的使用才能有用北风网分区的类型分区的类型北风网分区方法分区方法范

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

3、, . PARTITION partN VALUES LESS THAN(MAXVALUE);Maxvalue：如果数据范围是确定的：如果数据范围是确定的(例如月份例如月份12)，不用设置，不用设置maxvalue，但是有的需求范围不确定但是有的需求范围不确定(薪水薪水)。范围分区范围分区 范围分区示例范围分区示例北风网SQL CREATE TABLE Sales( Product_ID varchar2 (5), Sales_Cost number (10)PARTITION BY RANGE (Sales_Cost)( PARTITION P1 VALUES LESS THAN (1000

4、), PARTITION P2 VALUES LESS THAN (2000), PARTITION P3 VALUES LESS THAN (3000);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 (TO_DATE(2003-01-01), PARTITION P2 VALUES LESS THAN (TO_DATE(2004

5、-01-01), PARTITION P3 VALUES LESS THAN (MAXVALUE);范围分区注意范围分区注意 分区的关键字能够是最多16个列的复合的关键字。 范围一定要连续，例如不允许, 13, 56, 810 如果没有maxvalue的设置，当插入的值大于最大关键字的分区时会报14400错误。 如果分区的字段是date类型。那么一定要用to_date来使年份的表示正确。北风网散列分区散列分区散列分区散列分区 【hashhash分区分区】允许用户对不具有逻辑范围的数据进行分区允许用户对不具有逻辑范围的数据进行分区 例如：数据分布不均匀，例如：数据分布不均匀，1月份月份10条记录

6、，条记录，2月份月份1千万记录。这时使千万记录。这时使用范围分区没有意义。用范围分区没有意义。 Oracle利用自己的利用自己的hash算法，能够保证数据在预先确定数目的分区算法，能够保证数据在预先确定数目的分区中平均分布散列分区语法中平均分布散列分区语法 特点：能把数据平均分配到各个分区特点：能把数据平均分配到各个分区北风网PARTITION BY HASH (column_name)PARTITIONS number_of_partitions;或PARTITION BY HASH (column_name)( PARTITION part1 TABLESPACE tbs1, PARTIT

7、ION part2 TABLESPACE tbs2, . PARTITION partN TABLESPACE tbsN);散列分区散列分区 散列分区散列分区 示例示例北风网SQL CREATE TABLE Employee( Employee_ID varchar2 (5), Employee_Name varchar2(20), Department varchar2 (10)PARTITION BY HASH (Department)( Partition D1, Partition D2, Partition D3); SQL CREATE TABLE EMPLOYEE( EMP_ID

8、 NUMBER(4), EMP_NAME VARCHAR2(14), EMP_ADDRESS VARCHAR2(15), DEPARTMENT VARCHAR2(10)PARTITION BY HASH (DEPARTMENT)PARTITIONS 4; 散列分区散列分区 不必清楚不必清楚hash如何分区，所以只如何分区，所以只要设置要设置2的的n次方个分区就可以了。次方个分区就可以了。 等值运算时，等值运算时，hash算法很快能找算法很快能找到对应的分区。到对应的分区。 Where column_name = &var1 有大量的等值运算的时候适合建立有大量的等值运算的时候适合建立h

9、ash 如果有范围运算就应该避免使用如果有范围运算就应该避免使用hash 散列分区的关键字应该尽量是唯散列分区的关键字应该尽量是唯一或者接近唯一的字段。一或者接近唯一的字段。北风网列表分区列表分区北风网n列表分区列表分区当需要在如部门、城市这种列进行分区时，难以按照范围进行分区，此时可以用列表分区列中的值是有限的若干个n列表分区的语法：列表分区的语法：PARTITION BY LIST (column_name)( PARTITION part1 VALUES (values_list1), PARTITION part2 VALUES (values_list2), . PARTITION

10、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 (纽约, 波斯顿),

11、 Partition other values (default);列表分区列表分区 需要明确的写出列表值。需要明确的写出列表值。 在列表分区中在列表分区中DEFAULT 代表所有代表所有在其他分区中不包括的值在其他分区中不包括的值 北风网复合分区复合分区 复合分区复合分区 范围分区与散列分区或列表分区的组合，范围分区与散列分区或列表分区的组合，range+hash、range+list 复合分区的语法：复合分区的语法：先根据先根据范围范围对数据进行分区，然后再这些对数据进行分区，然后再这些分区内分区内创建创建散列散列子分区子分区。北风网PARTITION BY RANGE (column_n

12、ame1)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);复合分区复合分区复合分区示例复合分区示例北风网SQL CREATE TABLE SALES( PRODUCT_ID VARCHAR2 (5), SALES_DATE DATE NOT NULL,

13、SALES_COST NUMBER (10)PARTITION BY RANGE (SALES_DATE)SUBPARTITION BY HASH (PRODUCT_ID)SUBPARTITIONS 5( PARTITION S1 VALUES LESS THAN (TO_DATE(01/4/2001, DD/Mm/YYYY), PARTITION S2 VALUES LESS THAN (TO_DATE(01/7/2001, DD/Mm/YYYY), PARTITION S3 VALUES LESS THAN (TO_DATE(01/9/2001, DD/Mm/YYYY), PARTITIO

14、N S4 VALUES LESS THAN (MAXVALUE)先按照先按照Sales_Date进行范围分区，共创建进行范围分区，共创建4个分区个分区s1,s2,s3,s4；再在每；再在每个分区中分别创建个分区中分别创建5个个Hash子分区，共子分区，共4*5=20个分区个分区访问已分区的表访问已分区的表 在在sales表上对表上对sales_cost进行范围分区进行范围分区select * from sales where sales_cost =2 ;select * from sales where sales_cost between 2000 and 3000;select * fr

15、om sales partition (P2);INSERT INTO SALES VALUES (P004, 1080);DELETE FROM SALES PARTITION (P2);分区表的方便之处在于，所有的分区表的方便之处在于，所有的select与与dml操作不需要指定分区名，操作不需要指定分区名，只要知道表名就可以。只要知道表名就可以。Oracle会自动地判断需要的数据在那个分区中。会自动地判断需要的数据在那个分区中。查询、修改和删除分区表时可以显式指定要操作的分区查询、修改和删除分区表时可以显式指定要操作的分区 北风网分区维护操作分区维护操作 分区维护操作修改已分区表的分区。分

16、区维护操作修改已分区表的分区。 分区维护的类型：分区维护的类型：计划事件计划事件 定期删除最旧的分区定期删除最旧的分区非计划事件非计划事件 最初分区的不够合理，比如，范围分区时把分区最初分区的不够合理，比如，范围分区时把分区弄得太大，我们需要拆分一下。或者分区太小，需要合并多个分弄得太大，我们需要拆分一下。或者分区太小，需要合并多个分区。区。 分区维护操作有：分区维护操作有：添加分区添加分区删除分区删除分区截断分区截断分区合并分区合并分区拆分分区拆分分区北风网维护分区维护分区 2-1 添加分区添加分区 在最后一个分区之后添加新分在最后一个分区之后添加新分区区北风网SQL ALTER TABLE SALES ADD PARTITION P4 VALUES LESS THAN (4000);n删除分区删除分区 删除一个指定的分区，分区的数据也随之删删除一个指定的分区，分区的数据也随之删除除SQL ALTER TABLE SALES DROP PARTITION P4;n截断分区截断分区 删除指定分区中的所有记录删除指定分区中的所有记录 SQL ALTER TABLE SALES TRUNCATE PARTITION P3;维护分