Oracle表分区

     分区是将一个表或索引物理地分解为多个更小、更可管理的部分。从逻辑上讲分区后仍是只有一个表或一个索引，但在物理上这个表或索引可能由数十个物理分区组成。每个分区都是一个独立的对象，可以独自处理，也可以作为一个更大对象的一部分进行处理.

区别分表技术：分表技术顾名思义，就是把若干个存储相同类型数据的表分成几个表，分表进行存储。在提取数据的时候，不同的用户访问不同的表，互不冲突，减少锁表的概率。比如：目前保存用户分表，一个是user1表，一个是user2表，两个表保存了不同的用户信息，user1保存了前10W的用户信息；user2保存了后10W名用户的信息，现在如果同时查询用户XX1和XX2这两个用户，那么就是分表从不同的表提取出来，减少锁表的可能。

一、什么时候使用分区表

     什么时候使用分区表，并没有一个精确的界限，Oracle只有一些通用的建议，具体使用需要自行评估：

• Tables greater than 2GB should always be considered for partitioning.
• Tables containing historical data, in which new data is added into the newest partition. A typical example is a historical table where only the current month's data is updatable and the other 11 months are read only.

      参考：MySQL当数据库单表记录达到2000W左右（根据笔者的工作经验）,性能会下降到几乎无法忍受

    Tip: 计算表大小的SQL
      SELECT B.OWNER,B.TABLESPACE_NAME,B.TABLE_NAME,ROUND (SUM (BYTES) / 1024 / 1024 / 1024, 6) GIGS
      FROM   SYS.DBA_EXTENTS A, SYS.DBA_TABLES B
      WHERE ((B.TABLESPACE_NAME = A.TABLESPACE_NAME)
                AND (B.OWNER = UPPER ('&OWNER')) AND (B.TABLE_NAME = '&TABLE'))
                GROUP BY B.OWNER, B.TABLESPACE_NAME, B.TABLE_NAME; 

     检查分区是否创建正确
      SELECT TABLE_NAME, PARTITION_NAME, HIGH_VALUE, TABLESPACE_NAME
      FROM USER_TAB_PARTITIONS
      WHERE TABLE_NAME = 'PARTITION_BY_LIST'
       ORDER BY TABLESPACE_NAME;

二、如何分区

Partition Key

    分区表/索引是以一个或多个字段为依据来决定记录存储在哪个分区，被选用的分区字段称为Partition Key，Oracle会根据Partition Key自动找到对应的分区中做insert, update, delete操作。

   Oracle数据库提供对表或索引的分区方法包括如下：

   (1)、范围分区                  ----->语法：Partition by range();适合数值型或日期型 ；

   (2)、Hash分区（散列分区）---->语法：Partition by hash();

   (3)、列表分区                  ----->语法：Partition by list();适合字符型；

   (4)、复合分区                 ------>基于范围分区和散列分区复合而成；

1、【范围分区】

     范围分区就是对数据表中的某个值的范围进行分区，根据某个值的范围，决定将该数据存储在哪个分区上,如下所示：

     Create table XXX

      (

         column1 date,

         column2 varchar2(20)

       )

      Partition by Range(column1)

      (

          Partition part1 values less than (to_date('01/01/2005','dd/mm/yyyy')),

          Partition part2 values less than (to_date('01/01/2006','dd/mm/yyyy')),

          Partition part3 values less than (maxvalue))

     );

    实际物理意义如下：

      Create table test

      (

         transaction_id number primary key,
         item_id number(8) not null,
         item_description varchar2(300),

         transaction_date date not null

       )

      Partition by Range(transaction_id)

      (

          partition part_01 values less than(30000000) tablespace dinya_space01,
          partition part_02 values less than(60000000) tablespace dinya_space02,
          partition part_03 values less than(maxvalue) tablespace dinya_space03

      );

     建表成功，根据交易的序号，交易ID在三千万以下的记录将存储在第一个表空间dinya_space01中，分区名为:part_01;在三千万到六千万之间的记录存储在第二个表空间dinya_space02中，分区名为:part_02;而交易ID在六千万以上的记录存储在第三个表空间dinya_space03中，分区名为part_03.

      Create table test

      (

          transaction_id number primary key,         

          item_id number(8) not null,         

          item_description varchar2(300),

          transaction_date date not null

       )

      Partition by Range(transaction_date)

     (

          partition part_01 values less  than(to_date(’2006-01-01’,’yyyy-mm-dd’)) tablespace dinya_space01,
          partition part_02 values less  than(to_date(’2010-01-01’,’yyyy-mm-dd’)) tablespace dinya_space02,
          partition part_03 values less than(maxvalue) tablespace dinya_space03

     );

     这样我们就分别创建了以交易序号和交易日期来分区的分区表。每次插入数据时，系统将根据指定的字段值来自动的将记录存储到指定的分区(表空间)中去。

插入记录：

     insert into dinya_test values(1,12,’BOOKS’,sysdate);

    insert into dinya_test values(3,12, ’BOOKS’,to_date(’2006-05-30’,’yyyy-mm-dd’));

查询分区记录：
    select * from dinya_test partition(part_01);-----查询分区的记录;

    从查询的结果可以看出，插入的数据已经根据交易时间范围存储在不同的分区中。这里是指定了分区的查询,当然也可以不指定分区

    select * from dinya_test-------查询全部记录

更新分区记录：

   update dinya_test partition(part_01) t set t.item_description=’DESK’ where t.transaction_id=1;

   这里将第一个分区中的交易ID=1的记录中的item_description字段更新为“DESK”，可以看到已经成功更新了一条记录。

  但是当更新的时候指定了分区，而根据查询的记录不在该分区中时，将不会更新数据，请看下面的例子：

  SQL>update dinya_test partition(part_01) t set t.item_description=’DESK’ where t.transaction_id=6;
  0 rows
  updated.
  SQL> commit;
  指定了在第一个分区中更新记录，但是条件中限制交易ID为6，而查询全表，交易ID为6的记录在第三个分区中，这样该条语句将不会更新记录。

删除分区记录：

  SQL> delete from dinya_test partition(part_02) t where t.transaction_id=4;
  1 row deleted.
  SQL> commit;

  删除了第二个分区part_02中的交易记录ID为4的一条记录，和更新数据相同，如果指定了分区，而条件中的数据又不在该分区中时，将不会删除任何数据。

2、【散列分区】

     在一个列（或多个列）上应用一个散列函数，行会按这个散列值放在某个分区中。使用散列分区时要确保分区键所在列值唯一性，不能有重复，否则可能造成分区不均匀。如果使用散列分区，你将无从控制一行最终会放在哪个分区中。Oracle会应用散列函数，并根据散列的结果来确定行会放在哪里。如果你由于某种原因希望将某个特定行放在分区PART_1中，就不应该使用散列分区。在散列分区中，分区数应该是2的幂，否则会造成分区分布不均匀。举例，如：

   Create table XXX

      (

         column1 date,

         column2 varchar2(20)

       )

      Partition by Hash(column1)

     (

          partition part_1 tablespace p1,

         partition part_2 tablespace p2

     );

如将物料交易表的数据根据交易ID散列地存放在指定的三个表空间中:

   Create table test

      (

          transaction_id number primary key,         

          item_id number(8) not null,         

          item_description varchar2(300),

          transaction_date date not null

       )

      Partition by hash(transaction_id)

     (

         partition part_01 tablespace dinya_space01,

         partition part_02 tablespace dinya_space02,
         partition part_03 tablespace dinya_space03

     );

建表成功，此时插入数据，系统将按transaction_id将记录散列地插入三个分区中，这里也就是三个不同的表空间中。

3、【列表分区】

       指定一个离散值集，来确定应当存储在一起的数据。例如，可以指定STATUS列值在（’A’,’M’,’Z’）中的行放在分区1中，STATUS值在（'D',’P’,’Q’）中的行放在分区2中，依此类推。该分区的特点是某列的值只有几个，如下：

     Create table XXX

      (

         column1 date,

         column2 varchar2(20)

       )

      Partition by List(column1)

      (

          Partition part1 values ('A','M','Z'),

          Partition part2 values ('D','P','Q')

     );

       如果我们想插入列表分区中未指定的一个值，Oracle会向客户应用返回一个合适的错误。这很类似于一个检查约束，若想改变这个情况可以加一个DEFAULT分区，如：

   alter table table_name add partition part3 values (DEFAULT);

     但是，需要注意的是一旦列表分区表有一个DEFAULT分区，就不能再向这个表中增加更多的分区了。若要增加分区，则必须删除DEFAULT分区，然后增加PART4，再加回DEFAULT分区。

4、【间隔分区】

     间隔分区是Oracle 11g增加的新特性，它与区间分区很类似。间隔分区不需要预先为数据创建分区，而是在插入数据时让数据自己创建分区。要使用间隔分区，首先从一个没有MAXVALUE分区的时间分区表开始，指定一个要增加至上界的间隔（上界是一个最大值，如果达到这个最大值，分区表就要创建一个新的区间）。这个分区表要按某一列进行区间分区，而且这一列应当能增加NUMBER或INTERVAL类型的值（例如，按VARCHAR2字段分区的表就不能进行间隔分区，因为无法让VARCHAR2与某个数字间隔相加）。对任何合适的现有区间表都可以使用间隔分区，也就是说，可以使用ALTER将一个现有的区间表修改为间隔分区，也可以使用CREATE TABLE命令创建一个间隔分区表。

    数据库并不是预先创建所有可能的分区，而是随着各个数据行的到来，数据库会查看该月的分区是否存在。如果需要，数据库会创建相应的分区。举例，如：

    Create table table_name

    (

     column1 date,

     column2 varchar2(20)

    )

   Partition by Range(column1)

   Interval (numtoyminterval(1,’mouth’)) store in (users,example)

   (

    Partition part1 values less than(to_date('01/01/2005','dd/mm/yyyy')),

   );

5、【引用分区】

      引用分区是Oracle 11g新增的特性，它处理的是父/子对等分区的问题。也就是说，要以某种方式对子表分区，使得各个子表分区分别与一个父表分区存在一对一的关系。采用引用分区，子表会继承父表的分区机制，而不必对分区键逆规范化。而且，更重要的是，它会让数据库了解子表与父表存在对等分区特点。也就是说，截除或删除相应的子表分区时，也能删除或截除父表分区。

6、【组合分区】

     组合分区是区间分区、散列分区以及列表分区的组合。使用组合分区时，并没有分区段，而只用子分区段。分区本身并没有段（这就是类似于分区表没有段）。数据物理存储在子分区段上，分区成为一个逻辑容器，或者是一个指向实际子分区的容器。

     有时候我们需要根据范围分区后，每个分区内的数据再散列地分布在几个表空间中，这样我们就要使用复合分区。复合分区是先使用范围分区，然后在每个分区内再使用散列分区的一种分区方法，如将物料交易的记录按时间分区，然后每个分区中的数据分三个子分区，将数据散列地存储在三个指定的表空间中：

   create table dinya_test
   (
      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) subpartitions  store in (dinya_space01,dinya_space02,dinya_space03)
  (
   partition part01 values less than(to_date(’2006-01-01’,’yyyy-mm-dd’)),
   partition part02 values less than(to_date(’2010-01-01’,’yyyy-mm-dd’)),
   partition part03 values less than(maxvalue)
   );

   表首先进行范围分区，然后再进行散列分区

二、分区表索引的使用

     分区表和一般表一样可以建立索引，分区表可以创建局部索引和全局索引。当分区中出现许多事务并且要保证所有分区中的数据记录的唯一性时采用全局索引。

 1、局部索引分区的建立

    Create index dinya_idx_t on dinya_test(item_id)
    local
    (
    partition idx1 tablespace dinya_space01,
    partition idx2 tablespace dinya_space02,
    partition idx3 tablespace dinya_space03
    );

查看查询的执行计划，从下面的执行计划可以看出，系统已经使用了索引：
SQL> select * from dinya_test
partition(part_01) t where t.item_id=12;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=187)
1 0 TABLE ACCESS (BY
LOCAL INDEX ROWID) OF ’DINYA_TEST’ (Cost=
2 Card=1 Bytes=187)
2 1 INDEX
(RANGE SCAN) OF ’DINYA_IDX_T’ (NON-UNIQUE)
(Cost=1
Card=1)
Statistics
----------------------------------------------------------
0
recursive calls
0 db block gets
4 consistent gets
0 physical reads
0
redo size
334 bytes sent via SQL*Net to client
309 bytes received via
SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts
(memory)
0 sorts (disk)
2 rows processed

2、全局索引分区的建立

   全局索引建立时global 子句允许指定索引的范围值，这个范围值为索引字段的范围值： 

    Create index dinya_idx_t on dinya_test(item_id)
    global partition by range(item_id)
    (

      partition idx_1 values less than (1000) tablespace dinya_space01,
      partition idx_2 values less than (10000) tablespace dinya_space02,
      partition idx_3 values less than (maxvalue) tablespace dinya_space03
    );

同样的，对全局索引根据执行计划可以看出索引已经可以使用：

SQL> select * from dinya_test t where t.item_id=12;

Execution Plan ----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=3 Bytes=561)

1 0 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF ’DINYA_TEST’ (Cost =2 Card=3 Bytes=561)

2 1 INDEX (RANGE SCAN) OF ’DINYA_IDX_T’ (NON-UNIQUE) (Cost=1 Card=3)

Statistics ----------------------------------------------------------

5 recursive calls 0 db block gets 10 consistent gets 0 physical reads
0 redo size 420 bytes sent via SQL*Net to client 309 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

3 sorts (memory)

0 sorts (disk)

5 rows processed

三、分区表维护

     了解了分区表的建立、索引的建立、表和索引的使用后，在应用的还要经常对分区进行维护和管理。日常维护和管理的内容包括：增加一个分区，合并一个分区及删除分区等等。下面以范围分区为例说明增加、合并、删除分区的一般操作：

增加一个分区:

alter table 表名 add partition part04 values less than(to_date(’2012-01-01’,’yyyy-mm-dd’)) tablespace dinyapa03;

   增加一个分区的时候，增加的分区的条件必须大于现有分区的最大值，否则系统将提示ORA-14074 partition bound must collate higher than that of the last partition 错误;

合并一个分区:

alter table 表名 merge partitions part01,part02 into partition part02;

   在本例中将原有的表的part01分区和part02分区进行了合并，合并后的分区为part02,如果在合并的时候把合并后的分区定为part01的时候，系统将提示ORA-14275 cannot reuse lower-bound partition as resulting partition 错误。

删除一个分区:

alter table 表名drop partition part_01;
   删除分区表的一个分区后，查询该表的数据时显示，该分区中的数据已全部丢失，所以执行删除分区动作时要慎重，确保先备份数据后再执行，或将分区合并。

[Query]如何知道一个表或索引是否有被分区

SELECT * FROM dba_part_tables;

SELECT * FROM dba_part_indexes;

[Query]如何列出分区表或索引的分区对象

SELECT * FROM dba_tab_partitions WHERE table_name = '<table_name>';

SELECT * FROM dba_ind_partitions WHERE index_name = '<index_name>';

全表数据导出

exp system/welcome@ptian file=c:\test.dmp tables=Invoices

分区表导出

exp system/welcome@ptian file=c:\test_part.dmp tables=Invoices:part03

全表导入

imp system/welcome@ptian file=test.dmp full=y

分区表导入

imp system/welcome@ptian file=test_part.dmp full=y

四、升级策略

 如果初次开发时没有进行表分区，随着业务的扩展需要进行表分区，此时的升级策略为：备份原表，建表并分区，然后进行数据插入

 另外：Oracle 11g提供在线分区功能

五、分区常见问题

  对于一亿甚至几亿以下的数据规模来说，拆表能够解决很多问题，常见的二种经典的拆法如下：

 1、按ID取模

  把数据拆分到N个表当中去，这个方案的悲催是：扩展性不好，不好加表，数据一旦满了，加起来就很郁闷。虽然可以预先多分一些表，但是对于快速增长的业务来说，严重影响了业务的快速增长需求；

 2、按ID的时间来分段拆表

  按ID的时间来分段拆表，满了就建新表。这个方案的悲剧是：冷热不均，最近的数据肯定是被访问最频繁的，而老的库又基本是没有访问，可以通过冷热库混合部署的方案来缓解，但是部署和维护成本非常大。