分区表

1.      表分区的概念

表空间：

  是一个或多个数据文件的集合，所有的数据对象都存放在表空间中，但主要存放的是表，所以称表空间。

分区表：

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

2．表分区的作用

表分区的作用体现在三个方面：

a.      可管理性：简化常见的管理任务

b.      性能：使某些查询及维护操作的性能大大提高

c.      可用性：分区是构建千兆字节数据系统的或超高可用性系统的关键工具

 

分区表能够将表、索引或索引组织表进一步细分为段，这些数据库对象的段叫做分区。每个分区有自己的名称，还可以选择自己的存储特性。从dba的角度看，一个分区后的对象具有多个段，这些段既可集体管理，也可单独管理，就使得在管理分区后的对象时具有较大的灵活性。而从应用程序的角度看，分区后的表与非分区表完全相同，使用sql dml命令访问分区表示也无需做任何修改。

使用分区表的场景：

表中的数据超过2G

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

总结起来就是:当表中的数据量已经非常庞大，或者能够被预测到有大数据量时

3.      表分区的优缺点

优点：

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

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

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

d.      均衡I/O:可以把不同分区映射到不同磁盘，以平衡I/O

缺点：

已经存在的表没办法直接转换为分区表，不过可以使用oracle在线重定义表的方式来把普通表改为分区表。

4.      表分区的类型及及操作方法

1).范围分区

范围分区将数据基于范围映射到每一个分区，范围是在创建分区时由指定的分区键决定的。是最常用的分区方法，而且分区键常采用日期，如销售数据按照月份进行分区。

范围分区有以下规则

a.      每一个分区都必须由一个values less than字句，指定一个不包括在该分区的上限值。

b.      所有分区都有一个隐式的下限值，即前一个分区的上限值。

c.      在最高的分区中，maxvalue被定义。Maxvalue代表一个不确定的值，这个值高于其他任何分区的键值(values less than的值)，同时包括控制。

CASE1：

CREATE TABLE C_CUST(

cust_IDNUMBER NOT NULL PRIMARYKEY,

first_nameVARCHAR2(30)NOTNULL,

last_nameVARCHAR2(30)NOTNULL,

phoneVARCHAR2(15)NOTNULL,

emailVARCHAR2(80),

statusCHAR(1)

)

PARTITION BY RANGE(cust_id)

(

PARTITION part_1 VALUES LESS THAN(1000)TABLESPACEUSERS,

PARTITION part_2 VALUES LESS THAN(2000)TABLESPACEUSERS

)

Case2(maxvalue):

CREATE TABLE ORDER_ACTIVITIES(

ORDER_DATEDATE,

TOTAL_MOUNTNUMBER,

CUST_IDNUMBER,

PAID CHAR(1))

PARTITION BY RANGE(ORDER_DATE)

(

PARTITION PART_1 VALUES LESS THAN(TO_DATE('2012/1/1','YYYY/MM/DD')),

PARTITION PART_2 VALUES LESS THAN(TO_DATE('2014/1/1','YYYY/MM/DD')),

PARTITION PART_3 VALUES LESS THAN(TO_DATE('2016/1/1','YYYY/MM/DD')),

PARTITION part_4 VALUES LESS THAN(MAXVALUE)

)

2).列表分区：常使用与某列的值只有几个，也常用于大数据量的流水表，以年月作为键值，如201401.

CASE1:

create TABLE A_RCVBL_GAS_FLOW_H

(

  rcvbl_gas_id    NUMBER(16)default0 not null,

  mr_sect_no      VARCHAR2(32),

  mr_mode         VARCHAR2(8),

  cons_no         VARCHAR2(16)notnull,

  gas_sort        VARCHAR2(8)notnull,

  t_gq            NUMBER(16)default0,

  rcvbl_ym        VARCHAR2(6)notnull,

  rcvbl_amt       NUMBER(18,2)default0 not null,

  rcved_amt       NUMBER(18,2)default0,

  rcvbl_penalty   NUMBER(18,2)default0,

  rcved_penalty   NUMBER(18,2)default0,

  fee_stat        VARCHAR2(8)notnull,

  penalty_date    DATE,

  deadline_date   DATE,

  settle_flag     VARCHAR2(8)notnull,

  pay_mode        VARCHAR2(8),

  rele_app_no     VARCHAR2(16),

  calc_id         NUMBER(16)default0 not null,

  calc_date       VARCHAR2(8),

  org_no          VARCHAR2(16)notnull,

  this_read       NUMBER(18,6),

  last_read       NUMBER(18,6),

  mr_epm_no       VARCHAR2(32),

  discount_amt    NUMBER(18,2),

  meter_id        NUMBER(16),

  service_cust_no VARCHAR2(16),

  gas_narure      VARCHAR2(16)

)

PARTITION BY LIST(rcvbl_ym)

(

PARTITION p201401 VALUES('201401')TABLESPACEUSERS,

PARTITION p201402 VALUES('201402')TABLESPACEUSERS,

PARTITION pmax VALUES(DEFAULT)

)

3).散列分区

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

Case1:

CREATE TABLE t_hash(

col NUMBER(8),

col2 VARCHAR2(32)

)

PARTITION BY HASH (col)

(

PARTITION part_1 TABLESPACE hash_tbs1,

PARTITION part_2 TABLESPACE hash_tbs2

)

Case2:简写

a.

CREATE TABLE t_hash1(

col NUMBER(8),

col2 VARCHAR2(32)

)

PARTITION BY HASH (col)PARTITIONS4

;

b.

CREATE TABLE t_hash2(

col NUMBER(8),

col2 VARCHAR2(32)

)

PARTITION BY HASH (col)PARTITIONS4 STORE IN(USERS,USERS,USERS,USERS)

;

Hash分区最主要的机制是根据hash算法来计算某条记录应该存放到哪个分区中，hash算法中最重要的是hash函数，oracle中使用散列分区只需要指定分区的个数即可，如case1,建议分区的个数为2的n次方，可以是各个分区间数据分布更加均匀。

4).组合范围列表分区

基于范围分区和列表分区，先按某列进行范围分区，再按某列进行列表分区，分区之中的分区成为子分区。

Case1:

-- Create table

create table SALES

(

  product_id VARCHAR2(5),

  sales_date DATE,

  sales_cost NUMBER(10),

  status    VARCHAR2(6)

)

partition by range (SALES_DATE)

subpartition by list (STATUS)

(

  partition DATE2014 values less than(TO_DATE(' 2014-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN'))

    tablespace USERS

    pctfree 10

    initrans 1

    maxtrans 255

  (

    subpartition STATUS_20141 values ('1')tablespaceUSERS,

    subpartition STATUS_20142 values ('2')tablespaceUSERS

  ),

  partition DATE2015 values less than(TO_DATE(' 2015-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN'))

    tablespace USERS

  (

    subpartition STATUS_20151 values ('1')tablespaceUSERS,

    subpartition STATUS_20152 values ('2')tablespaceUSERS

  ),

  partition DATEMAX values less than(MAXVALUE)

    tablespace USERS

    pctfree 10

    initrans 1

    maxtrans 255

  (

    subpartition STATUS_MAX1 values ('1')tablespaceUSERS,

    subpartition STATUS_MAX2 values ('2')tablespaceUSERS

  )

);           

4).复合范围散列分区

 CREATE TABLE dinya_test(transaction_id NUMBER PRIMARY KEY,item_id NUMBER(8) NOT NULL,item_desc VARCHAR2(300) NOT NULL,transation_date DATE)PARTITION BY RANGE(transation_date) SUBPARTITION BY HASH(transation_date) STORE IN(USERS,USERS,USERS)(PARTITION part_01 VALUES LESS THAN (to_date('2015/01/01','YYYY/MM/DD')),PARTITION PART_02 VALUES LESS THAN (TO_DATE('2016/01/01','YYYY/MM/DD')),PARTITION PART_MAX VALUES LESS THAN (MAXVALUE))