表分区机制

--表分区机制

--Oracle目前有9中对表分区的方法

--区间分区（range partitioning）：你可以指定某个分区的数据应该放在一起。比如时间戳一月二月存放在分区一分区二中。
--散列分区（hash partitioning）：数据库会在一个或多个列上应用一个散列函数，每行数据都按散列值放在相应的分区中。
--列表分区（list partitioning）：指定一个离散值集，来确定应当存储在一起的数据。例如指定STATUS列值在（'A','B'）中的行放在分区1中，值（'C','D'）放在分区2中。
--间隔分区（interval partitioning）：它与区间分区非常相似，区别在于数据库可以在数据到来时创建新的分区。
--引用分区（reference partitioning）：对于一对外键约束的父/子表，如果父表已分区，子表可以通过外键约束来继承父表的分区方式，子表上的这种分区方法称为引用分区。
--间隔引用分区（interval reference partitioning）：这种分区就是间隔分区和引用分区的组合。Oracle12c才有这种分区。
--虚拟列分区（virtual column partitioning）：这是区间分区、散列分区和列表分区的组合。通过组合分区我们可以先按照某种方式分区（区间、散列、列表），再按照另外某种方式对每个分区的数据划分子分区。
--系统分区（system partitioning）：应用来决定将数据写入哪一个分区。这种分区类型比较少见。

--区间分区

EODA@PROD1> CREATE TABLE range_example      --创建一个使用RANGE_KEY_COLUMN分区的区间分区表，严格小于01-JAN-2014的数据放在part_1，严格小于01-JAN-2015的数据放在part_2
  2    ( range_key_column date NOT NULL,
  3  	 data		  varchar2(20)
  4    )
  5    PARTITION BY RANGE (range_key_column)
  6    ( PARTITION part_1 VALUES LESS THAN
  7  	      (to_date('01/01/2014','dd/mm/yyyy')),
  8  	 PARTITION part_2 VALUES LESS THAN
  9  	      (to_date('01/01/2015','dd/mm/yyyy'))
 10    )
 11  /

Table created.

EODA@PROD1> 
EODA@PROD1> insert into range_example   --插入数据
  2  ( range_key_column, data )
  3  values
  4  ( to_date( '15-dec-2013 00:00:00',
  5  		'dd-mon-yyyy hh24:mi:ss' ),
  6    'application data...' );

1 row created.

EODA@PROD1> 
EODA@PROD1> insert into range_example
  2  ( range_key_column, data )
  3  values
  4  ( to_date( '31-dec-2013 23:59:59',
  5  		'dd-mon-yyyy hh24:mi:ss' ),
  6    'application data...' );

1 row created.

EODA@PROD1> 
EODA@PROD1> insert into range_example
  2  ( range_key_column, data )
  3  values
  4  ( to_date( '01-jan-2014 00:00:00',
  5  		'dd-mon-yyyy hh24:mi:ss' ),
  6    'application data...' );

1 row created.

EODA@PROD1> 
EODA@PROD1> insert into range_example
  2  ( range_key_column, data )
  3  values
  4  ( to_date( '31-dec-2014 23:59:59',
  5  		'dd-mon-yyyy hh24:mi:ss' ),
  6    'application data...' );

1 row created.

EODA@PROD1> 
EODA@PROD1> -- should throw an error
EODA@PROD1> insert into range_example   --尝试插入超过分区的值失败
  2  ( range_key_column, data )
  3  values
  4  ( to_date( '01-jan-2015 00:00:00',
  5  		'dd-mon-yyyy hh24:mi:ss' ),
  6    'application data...' );
insert into range_example
            *
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition


EODA@PROD1> 
EODA@PROD1> select to_char(range_key_column,'dd-mon-yyyy hh24:mi:ss')  --查询part_1
  2  from range_example partition (part_1);

TO_CHAR(RANGE_KEY_COLUMN,'DD-
-----------------------------
15-dec-2013 00:00:00
31-dec-2013 23:59:59

EODA@PROD1> 
EODA@PROD1> select to_char(range_key_column,'dd-mon-yyyy hh24:mi:ss')  --查询part_2
  2  from range_example partition (part_2);

TO_CHAR(RANGE_KEY_COLUMN,'DD-
-----------------------------
01-jan-2014 00:00:00
31-dec-2014 23:59:59

EODA@PROD1> 
EODA@PROD1> drop table range_example purge;

Table dropped.

EODA@PROD1> 
EODA@PROD1> CREATE TABLE range_example    --这样可以保证所有数据都能被插入
  2    ( range_key_column date ,
  3  	 data		  varchar2(20)
  4    )
  5    PARTITION BY RANGE (range_key_column)
  6    ( PARTITION part_1 VALUES LESS THAN
  7  	      (to_date('01/01/2014','dd/mm/yyyy')),
  8  	 PARTITION part_2 VALUES LESS THAN
  9  	      (to_date('01/01/2015','dd/mm/yyyy')),
 10  	 PARTITION part_3 VALUES LESS THAN
 11  	      (MAXVALUE)
 12    )
 13  /

Table created.

--散列分区

对于散列分区，Oracle会使用一个散列函数对每一条插入数据的分区列值计算出起散列值，以此确定数据应当放在N个分区中的哪一个分区中。
Oracle建议N是2一个幂，这样表中的数据才能最好的分布在所有的分区上。
表中的散列键值应当各不相同，这样数据才能均匀的分布在各个分区上。

EODA@PROD1> set echo on
EODA@PROD1> 
EODA@PROD1> CREATE TABLE hash_example  --创建两个分区的分区表
  2    ( hash_key_column   date,
  3  	 data		   varchar2(20)
  4    )
  5    PARTITION BY HASH (hash_key_column)
  6    ( partition part_1 tablespace p1,
  7  	 partition part_2 tablespace p2
  8  )
  9  /

Table created.

EODA@PROD1> 
EODA@PROD1> insert into hash_example
  2  ( hash_key_column, data )
  3  values
  4  ( to_date( '25-jun-2014' ),
  5    'application data...' );

1 row created.

EODA@PROD1> 
EODA@PROD1> insert into hash_example
  2  ( hash_key_column, data )
  3  values
  4  ( to_date( '27-feb-2015' ),
  5    'application data...' );

1 row created.

EODA@PROD1> 
EODA@PROD1> select 'part_1', hash_key_column from hash_example partition(part_1) union all
  2  select 'part_2', hash_key_column from hash_example partition(part_2);

'PART_ HASH_KEY_
------ ---------
part_2 25-JUN-14
part_2 27-FEB-15

--散列分区数需要使用2的幂

--首先自动创建一个有N个分区的散列分区表，这个过程会构造一个动态查询，按分区获取其中的行数，再按分区显示行数，最后给出一个简单直方图。
EODA@PROD1> create or replace
  2    procedure hash_proc
  3  		 ( p_nhash in number,
  4  		   p_cursor out sys_refcursor )
  5    authid current_user
  6    as