Oracle的表分区

当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表。

 

表分区的优点

        改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度

        增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用

        维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可

        均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能

 

缺点： 

        分区表相关：已经存在的表没有方法可以直接转化为分区表。不过Oracle提供了在线重定义表的功能

 

使用时机

        表的大小超过2GB

        表中包含历史数据，新的数据被增加都新的分区中

 

表分区的类型

范围分区

CREATE TABLE ORDERS 
( 
    ORDER_ID      NUMBER(7) NOT NULL, 
    ORDER_DATE    DATE, 
    TOTAL_AMOUNT NUMBER, 
    CUSTOTMER_ID NUMBER(7), 
    PAID           CHAR(1) 
) 
PARTITION BY RANGE (ORDER_DATE) 
(
  PARTITION PART01 VALUES LESS THAN (TO_DATE('01-MAY-2003','DD-MON-YYYY')) TABLESPACE ORD_TS01,
  PARTITION PART02 VALUES LESS THAN (TO_DATE('01-JUN-2003','DD-MON-YYYY')) TABLESPACE ORD_TS02,
  PARTITION PART02 VALUES LESS THAN (TO_DATE('01-JUL-2003','DD-MON-YYYY')) TABLESPACE ORD_TS03
);

#MAXVALUE的使用
PARTITION  BY  RANGE (grade) 
( 
      PARTITION  part1 VALUES  LESS  THEN (1000) TABLESPACE  Part1_tb, 
      PARTITION  part2 VALUES  LESS  THEN (MAXVALUE) TABLESPACE  Part2_tb 
);

 

列表分区

PARTITION  BY  LIST (area) 
( 
    PARTITION  part1 VALUES ('guangdong','beijing') TABLESPACE  Part1_tb, 
    PARTITION  part2 VALUES ('shanghai','nanjing')  TABLESPACE  Part2_tb 
);

散列分区

根据hash算法来计算具体某条纪录应该插入到哪个分区中。建议分区的数量采用2的n次方，这样可以使得各个分区间数据分布更加均匀。

PARTITION BY HASH (COL) 
( 
  PARTITION PART01 TABLESPACE HASH_TS01, 
  PARTITION PART02 TABLESPACE HASH_TS02, 
  PARTITION PART03 TABLESPACE HASH_TS03 
);

PARTITION BY  HASH (empno) PARTITIONS 8
STORE IN (emp1,emp2,emp3,emp4,emp5,emp6,emp7,emp8);

 

组合范围散列分区

这种分区是基于范围分区和列表分区，表首先按某列进行范围分区，然后再按某列进行列表分区，分区之中的分区被称为子分区。 

PARTITION BY RANGE(SALES_DATE) SUBPARTITION BY LIST (STATUS)
(
	PARTITION P1 VALUES LESS THAN(TO_DATE('2003-01-01','YYYY-MM-DD'))TABLESPACE rptfact2009 
		( 
		  SUBPARTITION P1SUB1 VALUES ('ACTIVE') TABLESPACE rptfact2009, 
		  SUBPARTITION P1SUB2 VALUES ('INACTIVE') TABLESPACE rptfact2009 
		), 
	PARTITION P2 VALUES LESS THAN (TO_DATE('2003-03-01','YYYY-MM-DD')) TABLESPACE rptfact2009 
		( 
		  SUBPARTITION P2SUB1 VALUES ('ACTIVE') TABLESPACE rptfact2009, 
		  SUBPARTITION P2SUB2 VALUES ('INACTIVE') TABLESPACE rptfact2009 
		) 
)

 

复合范围散列分区

这种分区是基于范围分区和散列分区，表首先按某列进行范围分区，然后再按某列进行散列分区。 

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) 
);

 

表分区的维护

添加分区

        ALTER TABLE SALES ADD PARTITION P3 VALUES LESS THAN(TO_DATE('2003-06-01','YYYY-MM-DD'));

        ALTER TABLE SALES MODIFY PARTITION P3 ADD SUBPARTITION P3SUB1 VALUES('COMPLETE');

 

删除分区

        ALTER TABLE SALES DROP PARTITION P3;

        ALTER TABLE SALES DROP SUBPARTITION P4SUB1;

 

截断分区

        指删除某个分区中的数据，并不会删除分区，也不会删除其它分区中的数据。

        ALTER TABLE SALES TRUNCATE PARTITION P2;

        ALTER TABLE SALES TRUNCATE SUBPARTITION P2SUB2;

 

合并分区

        将相邻的分区合并成一个分区，结果分区将采用较高分区的界限。

        ALTER TABLE SALES MERGE PARTITIONS P1,P2 INTO PARTITION P2;

 

拆分分区

        将一个分区拆分两个新分区，拆分后原来分区不再存在。注意不能对HASH类型的分区进行拆分。

        ALTER TABLE SALES SBLIT PARTITION P2 AT(TO_DATE('2003-02-01','YYYY-MM-DD')) INTO (PARTITION P21,PARTITION P22);

 

接合分区(coalesca) 

        将散列分区中的数据接合到其它分区中，当散列分区中的数据比较大时，可以增加散列分区，然后进行接合，值得注意的是，接合分区只能用于散列分区中。

        ALTER TABLE SALES COALESCA PARTITION;

 

重命名表分区

        ALTER TABLE SALES RENAME PARTITION P21 TO P2;

 

查询表上有多少分区

        SELECT * FROM useR_TAB_PARTITIONS WHERE TABLE_NAME='tableName'

查询出所有的分区表

        select * from user_tables a where a.partitioned='YES'

显示数据库所有分区表的信息

        select * from DBA_PART_TABLES