分区表学习总结

分区partition

1.       知识点

Any table can be partitioned into a million separate partitions except those tables containing columns withLONG or LONGRAW data types. You can, however, use tables containing columns withCLOB or BLOB data types.

When to Partition a Table

Here are some suggestions for when to partition a table:

·         Tables greater than 2 GB should always be considered as candidates 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.

·         When the contents of a table must be distributed across different types of storage devices.

 

数据的分割方式

水平分割，oracle的分区表，share-nothing结构的自动分割到不同的节点。

垂直分割，例如CLOB太大，需要手动分割到单独的表空间。

分区表和索引

分区表/分区索引，可以单独处理分区，也可以整体对待。

本地分区 local:索引和表的分区一样。

分区的好处：

1.       降低IO

2.       可以并发读书。

3.       可以单独join某个分区。

4.       分区可以放在不同的物理设备上，各个物理设备可以负载均衡。

分区的方式：

  能列出取值范围时，一般用list，不太清楚的用Hash

Hash会将写请求尽量分配到相远的块上，以避免热块。

subpartiton

分区命名习惯，以P开头，子分区以S开头

在Oracle 11g中，

组合分区功能这块有所增强，又增加了range-range,list-range,list-list,list-hash，并且 11g里面还支持Interval分区和虚拟列分区

相关视图：

select table_name,PARTITION_count from user_part_tables

select TABLE_NAME,PARTITION_NAME from user_tab_partitions;

select table_name,partition_name,subpartition_name from user_tab_subpartitions;

select * from user_part_key_columns

select * from user_subpart_key_columns

select TABLE_NAME,PARTITION_NAME,COLUMN_NAME,HISTOGRAM from user_part_col_statistics;

select TABLE_NAME,SUBPARTITION_NAME,COLUMN_NAME,HISTOGRAM from user_subpart_col_statistics;

2.       subpartition template

普通的分区表在增加一个分区时，会将分区增加到system表空间，需要手动修改，如果分区创建成subpartition template，则不会出现这个问题。

 

create table emp_sub_template (deptno number, empname varchar(32), grade number)  

partition by range(deptno) subpartition by hash(empname)

subpartition template

(subpartition st1 tablespace data01,

 subpartition st2 tablespace data02,

 subpartition st3 tablespace data03,

 subpartition st4 tablespace data04

)

(partition p1 values less than (1000),

 partition p2 values less than (2000),

 partition p3 values less than (maxvalue)

);

详细学习测试：http://blog.csdn.net/tanqingru/article/details/8767416

3.       分区表转换

具体的方法有下面几种：

1. Export/import method

2. Insert with a subquery method

3. Partition exchange method

4. DBMS_REDEFINITION

1.逻辑导出/入不做说明

2.insert方法：

是DDL操作，不会产生大量UNDO

首先创建

Create table test

……

As

Select * from dba:

然后改名

rename dba to dba_old;

SQL> rename intervalsales to INTERVAL_SALES;

3交换分区：Partition exchange method

这种方法只是对数据字典中分区和表的定义进行了修改，没有数据的修改或复制，效率最高。适用于包含大数据量的表转到分区表中的一个分区的操作。尽量在闲时进行操作。

例1：

SQL> alter table p_dba exchange partition p1 with table dba_p1;

例2：

SQL> alter table big_table2

 2  exchange partition big_table_2013

 3  with table big_table

 4  without validation

 5  update global indexes;

可以参考：

http://blog.csdn.net/tanqingru/article/details/8770901

http://www.oracle-base.com/articles/misc/partitioning-an-existing-table-using-exchange-partition.php

4使用在线重定义：DBMS_REDEFINITION

在线重定义能保证数据的一致性，在大部分时间内，表都可以正常进行DML操作。只在切换的瞬间锁表，具有很高的可用性。这种方法具有很强的灵活性，对各种不同的需要都能满足。而且，可以在切换前进行相应的授权并建立各种约束，可以做到切换完成后不再需要任何额外的管理操作。

详细的操作可参考：

http://blog.csdn.net/tanqingru/article/details/8777337

还可以参考：

http://www.oracle-base.com/articles/misc/partitioning-an-existing-table.php

 

4.       Partition Pruning

优化器分析SQL中的WHERE和FROM字句，在查询中消除不不必要分区。

分区修剪技术能减少从磁盘上读取的数据量，缩短运行时间，改善查询性能，减少资源浪费。即使你的索引分区和表分区不同，分区修剪也可以在索引上生效（global partition index），从而消除不必要的索引分区。

分区修剪的特性依赖SQL语句，Oracle有两种分区修剪：动态修剪和静态修剪。静态修剪发生在编译时期，在执行计划指定的时候，已经知道那些分区会被使用。而动态修剪发生在运行时，也就是说在运行的时候，才会知道那些分区会被用到。例如，WHERE字句里面包含一个函数或者子查询用于返回分区键的值

具体参考：

http://docs.oracle.com/cd/E11882_01/server.112/e25523/part_avail.htm

http://www.cnblogs.com/wenjiewang/archive/2012/08/05/2624008.html

 

5.       Interval分区(Oracle11g)

在11 g 中，interval分区会自动创建合适的分区：

CREATE TABLE intervalsales

PARTITION BY RANGE (trans_date)

  INTERVAL ( NUMTOYMINTERVAL (1,'MONTH'))

   (PARTITION part1

       VALUES LESS THAN (TO_DATE ('01/12/2010','MM/DD/YYYY')))

as

select * from sales;

 

SQL>  select table_name,partition_name

from user_tab_partitions where table_name='INTERVALSALES';

 

TABLE_NAME                     PARTITION_NAME

------------------------------ ------------------------------

INTERVALSALES                  PART1

INTERVALSALES                  SYS_P70

INTERVALSALES                  SYS_P71

INTERVALSALES                  SYS_P72

INTERVALSALES                  SYS_P73

INTERVALSALES                  SYS_P74

INTERVALSALES                  SYS_P75

INTERVALSALES                  SYS_P76

INTERVALSALES                  SYS_P77

INTERVALSALES                  SYS_P78

INTERVALSALES                  SYS_P79

INTERVALSALES                  SYS_P80

INTERVALSALES                  SYS_P81

INTERVALSALES                  SYS_P82

 

14 rows selected.

6.       虚拟列分区(Oracle11g)

A column that is not stored on disk. The database derives the values in virtual columns on demand by computing a set ofexpressions or functions.

      虚拟列不会存储在磁盘，它的值由相关表达式或函数决定。

1. 含有虚拟列的表：

SQL> create table vir_col_tab

  2  (id number(6) not null,

  3  first_name varchar2(10),

  4  last_name varchar2(10),

  5  sex char(1),

  6  name as (first_name||last_name)

  7* )

 

不能直接虚拟列插入数据，如下：

 

SQL> insert into vir_col_tab values(1,'tan','qr',1,'tanqr');

insert into vir_col_tab values(1,'tan','qr',1,'tanqr')

            *

ERROR at line 1:

ORA-54013: INSERT operation disallowed on virtual columns

可以像下面的方法插入：

SQL> insert into vir_col_tab(id,first_name,last_name,sex) values(1,'tan','qr',1);

1 row created.

SQL> select * from vir_col_tab;

 

        ID FIRST_NAME LAST_NAME  S NAME

---------- ---------- ---------- - --------------------

         1 tan        qr         1 tanqr

 

还能用下面的语法创建带有虚拟列的表：

SQL> create table vircoltest

  2  (id number,

  3  name varchar2(10),

  4  sex varchar2(1),

  5  virtual_name varchar(10)generated always as (upper(name)) virtual);

 

Table created.

2.创建虚拟列分区表：

SQL> create table vircol_partition

 ( prod_id       NUMBER(6) NOT NULL

  , cust_id       NUMBER NOT NULL

  , time_id       DATE NOT NULL

  5    , channel_id    CHAR(1) NOT NULL

  6    , promo_id      NUMBER(6) NOT NULL

  7    , quantity_sold NUMBER(3) NOT NULL

  8    , amount_sold   NUMBER(10,2) NOT NULL

  9    , total_amount AS (quantity_sold * amount_sold)

 10    )

 11  PARTITION BY RANGE (time_id) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))

 12   SUBPARTITION BY RANGE(total_amount)

 13   SUBPARTITION TEMPLATE

 14     ( SUBPARTITION p_small VALUES LESS THAN (1000)

 15     , SUBPARTITION p_medium VALUES LESS THAN (5000)

 16     , SUBPARTITION p_large VALUES LESS THAN (10000)

 17     , SUBPARTITION p_extreme VALUES LESS THAN (MAXVALUE)

 18     )

 19   (PARTITION sales_before_2007 VALUES LESS THAN

 20     (TO_DATE('01-JAN-2007','dd-MON-yyyy'))

 21  )

 22  ENABLE ROW MOVEMENT

 23  PARALLEL NOLOGGING;

 

Table created.

SQL> select table_name,partitioning_type from user_part_tables;

 

TABLE_NAME                     PARTITION

------------------------------ ---------

VIRCOL_PARTITION               RANGE

 

SQL> select table_name,partition_name,subpartition_name from user_tab_subpartitions;

 

TABLE_NAME                     PARTITION_NAME                 SUBPARTITION_NAME

------------------------------ ------------------------------ ------------------------------

VIRCOL_PARTITION               SALES_BEFORE_2007              SALES_BEFORE_2007_P_SMALL

VIRCOL_PARTITION               SALES_BEFORE_2007              SALES_BEFORE_2007_P_MEDIUM

VIRCOL_PARTITION               SALES_BEFORE_2007              SALES_BEFORE_2007_P_LARGE

VIRCOL_PARTITION               SALES_BEFORE_2007              SALES_BEFORE_2007_P_EXTREME