oracle分区表

官方文档：http://docs.oracle.com/cd/E11882_01/server.112/e40540/schemaob.htm#CNCPT1514

1. oracle最大支持1024K-1个分区，列的数据类型为LONG或者LONG RAW的表不允许创建分区。CLOB和BCLOB可以。

　什么时候需要对表进行分区：

　① 大于2GB的表

　② 历史数据只是用来查询，而且不停在插入新数据的表

　1.1 range分区

　　　range分区是最常用的分区类型，通常用于对日期进行分区，必须声明values less than子句，maxvalue代表最大值。　

create table test_range_table(
       ename varchar2(30),
       deptno number,
       hire_date date
) partition by range(hire_date)(
  partition before_2014 values less than (to_date('2014-01-01','yyyy-mm-dd')),
  partition before_2015 values less than (to_date('2015-01-01','yyyy-mm-dd')),
  partition before_forever values less than (maxvalue)
);

　　如上SQL新建了分区表test_range_table,通过对hire_date分区将表分成了三个分区：before_2014(hire_date在2014年之前),before_2015(hire_date在2014和2015年之间),before_forever(hire_date在2015年之后).分区表以及分区可以通过user_tab_partitions,dba_tab_partitions,all_tab_partitions查询。

　1.2 list分区可以对看似无序的数据按照某种规则进行整理，list分布不支持对多列进行分区，单支持为一个分区设定多个不同的值。如果往list分区表插入未在list分区定义的值，会报错的，default关键字可以避免这个问题。对于分区表test_list_table,假设不指定other_region分区，执行

insert into test_list_table values('张三',10,'张三');

会报错："ORA-14400：插入的分区关键字未映射到任何分区"

--list分区表
create table test_list_table(
       ename varchar2(30),
       deptno number,
       region varchar2(30)
) partition by list(region)(
  partition china_region values('china'),
  partition japan_region values('japan'),
  partition europe_region values('france','germany'),
  partition other_region values(default)
)

　1.3 hash分区新建的时候只需要指定分区列和分区个数即可，也可以指定分区的表空间。

create table test_hash_table(first_name varchar2(30),
       last_name varchar2(30),
       hire_date date) 
       partition by hash(last_name)
       partitions 4
       store in (tb_01,tb_02,tb_03,tb_04);

　1.4 range-hash分区，子分区信息可以通过user_tab_subpartitions,all_tab_subpartitions,dba_tab_subpartitions查询。

--range-hash分区
create table test_range_hash_table(
       first_name varchar2(30),
       last_name varchar2(30),
       hire_date date
) partition by range(hire_date)
subpartition by hash(last_name)
subpartition template(
subpartition sp1 ,
subpartition sp2,
subpartition sp3,
subpartition sp4)
(
  partition before_2014 values less than (to_date('2014-01-01','yyyy-mm-dd')),
  partition before_2015 values less than (to_date('2015-01-01','yyyy-mm-dd')),
  partition before_forever values less than (maxvalue)
)

　 1.5 range-list分区

create table test_range_list_table(
       ename varchar2(30),
       region varchar2(30),
       hire_date date
) 
partition by range(hire_date)
subpartition by list(region)
subpartition template(
  subpartition china_region values('china'),
  subpartition japan_region values('japan'),
  subpartition europe_region values('france','germany'),
  subpartition other_region values(default)            
)
( partition before_2014 values less than (to_date('2014-01-01','yyyy-mm-dd')),
  partition before_2015 values less than (to_date('2015-01-01','yyyy-mm-dd')),
  partition before_forever values less than (maxvalue));

 

2. 分区索引

　　分区索引分类两类global indexes(基于整个表) 和 local indexes(基于表的分区)，通常来说OLTP系统应该使用global indexes,数据仓库或者DSS应该使用local indexes。可以按照如下顺序决定采用哪种分区索引：

　　Step-1:如果分区列是被索引列的子集，用local index，结束；否则Step-2

　　Step-2:如果索引唯一，用global index,结束；否则Step-3

　　Step-3:如果更看重维护性能，用local index，结束；否则Step-4

　　Step-4:如果是OLTP用户看重response time用global index；如果是数据仓库用户看重吞吐量用local index

　　2.1 LOCAL PARTITIONED INDEX

　　　　local index和表分区是一一对应的

　　　　local nonprefixed index : 索引列为非分区列的简单索引，索引前置列为非分区列的复合索引，不一定允许分区修剪

　　　　local prefixed index: 索引列为分区列的简单索引，索引前置列为分区列的复合索引，允许分区修剪

　　　　local index的优点：

　　　　① 当一个分区的数据失效时，不会影响其他分区

　　　　② 当移动表分区或者数据被移出分区时，只有相关分区的索引需要被rebuilt，对global来说，所有的分区索引都要被rebuilt

　　　　③ 当基于时间点的恢复发生时，可以只恢复相关分区的索引，不需要rebuilit所有分区索引。　

create index test_range_table_local_n1 on test_range_table(hire_date) local;

　　2.2 GLOBAL PARTITIONED INDEX

　　　　global range partitioned index：基于范围的global index

　　　　global hash partitioned index：基于hash的global index

　　　　global index是独立于分区表的，假设表基于hire_date的分区为3个（before_2014,before_2015,before_forever），可以基于hire_date创建四个分区的global_index(before_2013,before_2014,before_2015,before_forever),global index也可以对不是分区列的column进行随意的分区。　

--global range index
--drop index test_range_table_local_n1
create index test_range_table_gl_range on test_range_table(hire_date) 
global partition by range(hire_date)(
  partition before_2013 values less than (to_date('2013-01-01','yyyy-mm-dd')),
  partition before_2014 values less than (to_date('2014-01-01','yyyy-mm-dd')),
  partition before_2015 values less than (to_date('2015-01-01','yyyy-mm-dd')),
  partition before_forever values less than (maxvalue)      
)

--global hash index
--drop index test_range_table_gl_range;
create index test_range_table_gl_hash on test_range_table(hire_date)
global partition by hash(hire_date)
partitions 4;

　　2.3 GLOBAL NONPARTITIONED INDEX

 

　　2.4 关于分区索引

　　　　可以通过user_ind_partitions，user_dba_partitions，user_all_partitions查询分区索引。

　　　　只能给分区表建立local类型的位图索引。

　　　　global index可以是唯一索引，只有当索引列是分区列的时候local index才可以是唯一索引。

　　　　分区索引在OLTP系统中的应用：

　　　　① global index和unique local index性能更优

　　　　② 当存在对分区表的分区或者子分区维护时，local index表现更好

　　　　③ 索引单调增长的时候，hash-partitioned global index更好，因为大部分的索引插入都是在索引右侧。

　　　　分区索引在DSS或者数据仓库中的应用：

　　　　① local index在数据加载或者分区维护的时候更方便

　　　　② local index可以并行的对多个索引分区进行扫描

 

3.分区对系统性能的提升

　　3.1 PARTITION PRUNING(分区修剪)

　　　　当谓词条件中包含分区列时，oracle会指定把不需要的分区剪掉。当对分区列施加函数时，分区修剪失效；同样，但对索引列施加函数是，索引失效，除非该索引是基于数据的。

　　3.2 PARTITION-WISE JOINS 

　　　　当两个分区表做连接，连接列恰好是这个两个分区表的分区列，则连接操作会变成并发的进行多个分区和多个分区之间的连接操作？？？

　　3.3 PARALLEL DML

转载于:https://www.cnblogs.com/yasun/p/5204123.html