数据库分区策略详解-优快云博客

分区可以把大的表或索引拆分成小表和索引。好处，便于管理。

其他好处：

增加可用性：一个partition损坏了，不影响其他的partion 的查询

便于管理：便于对象管理，partition对象可以集中管理或分开管理，DDL（创建索引/move表/drop表）语句可以作用在每个分区上，而不是整个表

减少资源竞争：因为分区的存储可以分开存储，所以DML可以分开在各个segments ，而不是一个sement。

在数据仓库里增加查询性能：例如销售表里包含几亿条记录，可以以季度为分界线来创建分区表。

分区特性：

每个分区的表或者索引，必须具有相同的逻辑属性，比如：列名称，数据类型，约束等。所有的partition必须有相同的列定义和约束定义。所有分区索引都是相同的索引列。

但是每个分区可以拥有不同的物理属性，例如可以归属于不同的表空间。

分区建

是一列或者多列的组合，用来决定每行数据应该放在哪个分区表里。每一行数据都被明确的指定放在哪一个分区表里。

列如：在一个销售表里，你可以指定time_id列作为分区建，数据库会跟据time_id，把行放在相应的分区范围。

分区策略

数据库用什么方法决定把哪些数据放哪个分区表里，基本策略：范围(rang)，罗列（list），哈希（hash partition）

range partition :

根据数据值的范围来决定放在哪个分区表里。通常以时间来分区。

SQL> create table time_range_sales
  2  (prod_id number(6),
  3  cust_id number,
  4  time_id date,
  5  channel_id char(1)
  6  ,promo_id number(6)
  7  ,quantity_sold number(3)
  8  ,amount_sold number(10,2)
  9  )
 10  partition by range (time_id)
 11  (partition sales_1998 values less than (to_date('01_JAN_1999','DD-MON-YYYY')),
 12  partition sales_1999 values less than (to_date('01_JAN_2000','DD-MON-YYYY')),
 13  partition sales_2000 values less than (to_date('01_JAN_2001','DD-MON-YYYY')),
 14  partition sales_2001 values less than (MAXVALUE)       
 15  );

Table created.

SQL> desc time_range_sales
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 PROD_ID					    NUMBER(6)
 CUST_ID					    NUMBER
 TIME_ID					    DATE
 CHANNEL_ID					    CHAR(1)
 PROMO_ID					    NUMBER(6)
 QUANTITY_SOLD					    NUMBER(3)
 AMOUNT_SOLD					    NUMBER(10,2)

list partition

CREATE TABLE list_sales
   ( prod_id        NUMBER(6)
   , cust_id        NUMBER
   , time_id        DATE
   , channel_id     CHAR(1)
   , promo_id       NUMBER(6)
   , quantity_sold  NUMBER(3)
   , amount_sold    NUMBER(10,2)
   )
PARTITION BY LIST (channel_id)
 (PARTITION even_channels VALUES (2,4),
  PARTITION odd_channels VALUES (3,9)
 );

hash Partitioning

数据库使用内部hash值，来分布数据，hash partiition的目的是使数据平均的分配在所有的分区表里。常用于把大的表分成很多小表，便于维护管理。

CREATE TABLE hash_sales
   ( prod_id        NUMBER(6)
   , cust_id        NUMBER
   , time_id        DATE
   , channel_id     CHAR(1)
   , promo_id       NUMBER(6)
   , quantity_sold  NUMBER(3)
   , amount_sold    NUMBER(10,2)
   )
PARTITION BY HASH (prod_id)
PARTITIONS 2;

之后数据会平均分到两个分区表里，分区表的名字由数据库自动产生。

分区索引

全局索引：全局分区索引不依赖底层的表。和表的分区情况没有关系

本地所以：本地分区索引，是根据底层表的分区情况，自动分区索引。

shows the creation statement for the partitioned hash_sales table,

CREATE INDEX hash_sales_idx ON hash_sales(time_id) LOCAL;

create a global partitioned index on the time_range_sales table

CREATE INDEX time_channel_sales_idx ON time_range_sales (channel_id)
   GLOBAL PARTITION BY RANGE (channel_id)
      (PARTITION p1 VALUES LESS THAN (3),
       PARTITION p2 VALUES LESS THAN (4),
       PARTITION p3 VALUES LESS THAN (MAXVALUE));

示例：创建range分区表。

SQL> create tablespace test_space01 datafile '+DATA' size 10M autoextend on next 4M;

Tablespace created.

SQL> create tablespace test_space02 datafile '+DATA' size 10M autoextend on next 4M;

Tablespace created.

SQL> create tablespace test_space03 datafile '+DATA' size 10M autoextend on next 4M;

Tablespace created.

SQL> create tablespace test_space04 datafile '+DATA' size 10M autoextend on next 4M;

Tablespace created.

SQL> create table range_example(
  2  range_key_column date,data varchar2(20),id integer 
  3  )
  4  partition by range(range_key_column)
  5  (
  6  PARTITION part01 VALUES LESS THAN(TO_DATE('2008-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE test_space01,
  7  partition part02 values less than(to_date('2008-08-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) tablespace test_space02,
  8  PARTITION part03 VALUES LESS THAN(TO_DATE('2008-09-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE test_space03,
  9  PARTITION part04 VALUES LESS THAN (MAXVALUE)TABLESPACE test_space04
 10  );

Table created.

SQL> insert into range_example values(TO_DATE('2008-06-10 00:00:00','yyyy-mm-dd hh24:mi:ss'), '1111', 1);

1 row created.

SQL> insert into range_example values(TO_DATE('2008-07-20 00:00:00','yyyy-mm-dd hh24:mi:ss'), '2222', 2);

1 row created.

SQL> insert into range_example values(TO_DATE('2008-08-25 00:00:00','yyyy-mm-dd hh24:mi:ss'), '3333', 3);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from range_example;

RANGE_KEY DATA			       ID
--------- -------------------- ----------
10-JUN-08 1111				1
20-JUL-08 2222				2
25-AUG-08 3333				3

在分区表上查询

SQL> select * from range_example partition(part01);

RANGE_KEY DATA			       ID
--------- -------------------- ----------
10-JUN-08 1111				1

分区表/分区索引