Oracle 分区表

背景

我们知道在数据库中，当一张表的数据量增多时，数据的查询就会变慢，从而影响应用程序的性能。这时我们应该考虑将表分区，表分区后在逻辑上仍然属于一张表，只是在物理上存储在多个文件中。

分区的优势

• 改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度；
• 方便数据管理：因为分区表的数据存储在多个部分中，所以按分区加载和删除数据比在大表中加载和删除数据更容易；
• 方便备份恢复：因为分区比被分区的表要小，所以针对分区的备份和恢复方法要比备份和恢复整个表的方法多；
• 均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能。

分区的方式

• 范围分区

范围分区将数据基于范围映射到每一个分区，这个范围是你在创建分区时指定的分区键决定的。这种分区方式是最为常用的，并且分区键经常采用日期。举个例子：你可能会将销售数据按照月份进行分区。

-- 按行分区
CREATE TABLE STUDENT 
( 
    ID NUMBER NOT NULL PRIMARY KEY, 
    NAME  VARCHAR2(30) NOT NULL, 
    SEX  CHAR(1) NOT NULL,  
    PHONEVARCHAR2(15) NOT NULL, 
    EMAILVARCHAR2(80), 
    STATUS       CHAR(1) 
) 
PARTITION BY RANGE (ID) 
( 
    PARTITION STUDENT_PART1 VALUES LESS THAN (100000) TABLESPACE TEST_SPACE, 
    PARTITION STUDENT_PART2 VALUES LESS THAN (200000) TABLESPACE TEST_SPACE 
)

-- 按时间分区
CREATE TABLE ORDER_ACTIVITIES 
( 
    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 p1 VALUES LESS THAN (TO_DATE('2014-10-1', 'yyyy-mm-dd')) TABLESPACE TEST_SPACE ,
PARTITION p2 VALUES LESS THAN (TO_DATE('2015-10-1', 'yyyy-mm-dd')) TABLESPACE TEST_SPACE,
PARTITION p3 VALUES LESS THAN (TO_DATE('2016-10-1', 'yyyy-mm-dd')) TABLESPACE TEST_SPACE)

• Hash 分区

这类分区是在列值上使用hash算法，以确定将行放入哪个分区中。当列的值没有合适的条件时，建议使用散列分区。
散列分区为通过指定分区编号来均匀分布数据的一种分区类型，因为通过在I/O设备上进行散列分区，使得这些分区大小一致

CREATE TABLE HASH_TABLE 
( 
  COL NUMBER(8), 
  INF VARCHAR2(100) 
) 
PARTITION BY HASH (COL) 
( 
  PARTITION PART01 TABLESPACE TEST_SPACE, 
  PARTITION PART02 TABLESPACE TEST_SPACE, 
  PARTITION PART03 TABLESPACE TEST_SPACE 
)

• list 分区

该分区的特点是某列的值只有几个，基于这样的特点我们可以采用列表分区

CREATE TABLE STUDENT 
( 
    ID NUMBER NOT NULL PRIMARY KEY, 
    NAME  VARCHAR2(30) NOT NULL, 
    SEX  CHAR(1) NOT NULL,  
    PHONEVARCHAR2(15) NOT NULL, 
    EMAILVARCHAR2(80), 
    STATUS       CHAR(1) 
) 
PARTITION BY RANGE (ID) 
( 
    PARTITION STUDENT_0 VALUES (0) TABLESPACE TEST_SPACE, 
    PARTITION STUDENT_1 VALUES (1) TABLESPACE TEST_SPACE 
)

• 组合分区

采用以上多种分区方式的组合

-- 这种分区是基于范围分区和散列分区，表首先按某列进行范围分区，然后再按某列进行散列分区
CREATE TABLE HASHTABLE (
	TRANSACTION_ID NUMBER PRIMARY KEY,
	ITEM_ID number(8) NOT NULL,
	ITEM_DESCRIPTION VARCHAR2(300),
	TRANSACTION_DATE DATE
)
PARTITION BY RANGE (TRANSACTION_DATE)
SUBPARTITION BY HASH (TRANSACTION_ID) (
	PARTITION PART_01 VALUES LESS THAN (TO_DATE('2014-10-1', 'yyyy-mm-dd')),
	PARTITION PART_02 VALUES LESS THAN (TO_DATE('2015-10-1', 'yyyy-mm-dd')),
	PARTITION PART_03 VALUES LESS THAN (MAXVALUE)
);

参考

• 详解Oracle partition分区表
• oracle表分区详解（按天、按月、按年等）
• oracle表空间表分区详解及oracle表分区查询使用方法(转+整理)