Oracle分区表管理

–管理分区
应对哪种表应用分区功能；
1.大于2GB的表
2.含有1000万条记录以上的表，表中含有的数据越多，SQL操作的执行速度就会越慢。
3.将会含有大量数据的表。
4.强行拆分后可利于并行操作的表。
5.含有需要定期归档日志或删除部分的表。

  一条可靠的原则是大大于2GB的表就可以应用分区功能。运行下面的查询命令可以显示数据库中占用空间的排名情况：
   select * from (
   select owner,segment_name,segment_type,partition_name,sum(bytes)/1024/1024 meg_tot
   from dba_segments group by owner,segment_name,segment_type,partition_name
   order by sum(extents) desc)
   where rownum <=10 

–创建分区表
Oracle 提供了一系列强大的分区功能，使用他们可以将表和索引拆分为较小的子集。例如，可以根据日期范围拆分表中的数据。
–分区策略
分区类型 描述
范围 根据日期,数值或字符创建分区
列表 根据列表值创建分区
散列 在没有明显分区键的情况下，以均分方式创建分区
组合 组合使用多种分区方式
间隔 当新分区键值超出现存最大范围时，通过自动分配新分区扩展范围分区
引用 根据父表列为子表创建分区
虚拟列分区 在虚拟列上创建分区
系统 根据插入数据的应用程序创建分区

--按范围分区
使用 CREATE TABLE 语句中的 PARTITION BY RANGE 子句，可以定义居于范围的分区键。这可以确定用于控制分区划分的列。
使用 VALUES LESS THAN 子句可以定义范围分区的上边界。范围分区中的第一个分区没有下边界，所有小于VALUES LESS THAN 子句设置值的数据都会插入第一个分区。除

了第一个分区外，其他分区的下边界都由前一个分区的上边界确定。
还可以使用 MAXVALUE 子句创建范围分区表的最高分区。所有分区键值高于较低范围的数据都会插入最顶层的 MAXVALUE 分区。

 1.将分区键列设置为 number 类型
    create table f_sales ( sales_amt number, d_date_id number)
    partition by range (d_date_id) (
    partition p_2012 values less than (20130101),
    partition p_2013 values less than (20140101),
    partition p_max values less than (maxvalue));

–在创建范围分区表时，可以不设置MAXVALUE分区。然而，如果不设置MAXVALUE分区，当插入不在任何范围内时，系统就会提示下面错误：
ORA-14400:inserted partition key does not map to any partition ;
当该错误提示出现时，就必须添加一个能够容纳插入数据的范围分区。或者添加一个MAXVALUE分区。
–如果你使用Oracle Database 11g或更高的版本，可使用间隔分区策略。当插入数据超出范围值时，Oracle会自动添加分区/

 --查询分区表信息
    select table_name,partitioning_type,def_tablespace_name from user_part_tables where table_name='F_SALES';
    --查询表中分区的信息
    select table_name,partition_name,high_value from user_tab_partitions where table_name='F_SALES' order by table_name,partition_name;

 2.将分区键列设置为TIMESTAMP类型
    create table d_sales( sales_amt number,d_date_id date)
    partition by range (d_date_id) (
    partition  p_2012 values less than (to_date('01-01-2012','dd-mm-yyyy')),
    partition  p_2014 values less than (to_date('01-01-2013','dd-mm-yyyy')),
    partition  p_max values less than (maxvalue));

–使用表空间存储分区
每个分区创建一个独立得表空间可以获得提高可用性和降低管理成本等好处。使用独立表空间可以控制分区得独立性，可以单独将分区设置为联机/脱机状态，不依赖其他分区对某个分区执行备份恢复操作。

 要理解每个分区使用独立表空间的优点，可以考虑不用分区表的情况：
    create tablespace p13_tbsp
    datafile '/u02/datafile/p13_tbsp.dbf' size 100m
    extent management local 
    uniform size 128k
    segment space management auto;

--创建一个非分区表，存储到表空间
create table t_sales(
sales_amt number,
d_date_id number
) tablespace p1_tbsp;
--创建一个分区表，但是没有为分区设置表空间：
 create table f_sales(
sales_amt number,
d_date_id number
) tablespace p1_tbsp
partition by range(d_date_id)
( partition y2011 values less than (20120101),
  partition y2012 values less than (20130101),
  partition y2013 values less than (20140101));

这种情况下所有的分区都存储在一个表空间中。
这种方法优于使用非分区表的方法，使用该方法可以在不影响其他分区的情况下，对某个分区执行分区维护操作，从而确保分区的独立性。然而，该方法并没有完全利用分区功能的优势。

    将每个分区都存储到独立的表空间中：
    create table p_sales(
    sales_amt number,
    d_date_id number
    )tablespace p1_tbsp
    partition by range(d_date_id)(
    partition y11 values less than (20120101)
    tablespace p11_tbsp,
    partition y12 values less than (20130101)
    tablespace p12_tbsp,
    partition y13 values less than (20140101)
    tablespace p13_tbsp );

这样每个分区的数据库都存储到了它们单独拥有的表空间和响应的数据文件中。
--也可以设置其他存储功能，对表空间使用了PCTFREE,PCTUSED和NOLOGGING子句
create table p_sales(
sales_amt number,
d_date_id number
)tablespace p1_tbsp
partition by range(d_date_id)(
partition y11 values less than (20120101)
tablespace p11_tbsp pctfree 5 pctused 70 nologging,
partition y12 values less than (20130101)
tablespace p12_tbsp pctfree 5 pctused 70 nologging,
partition y13 values less than (20140101)
tablespace p13_tbsp pctfree 5 pctused 70 nologging);

– pctfree
为一个块保留的空间百分比，表示数据块在什么情况下可以被insert，默认是10，表示当数据块的可用空间低于10%后，就不可以被insert了，只能被用于update；

即：当使用一个block时，在达到pctfree之前，该block是一直可以被插入的，这个时候处在上升期。
– pctused
是指当块里的数据低于多少百分比时，又可以重新被insert，一般默认是40,即40%，即：当数据低于40%时，又可以写入新的数据，这个时候处在下降期。

    --根据散列值分区
    对无序和非关系数据使用列表分区策略的效果很好。
    使用 partition by list 进行散列值分区： 
    create table f_sales (
    sales_amt number,
    d_date_id number,
    state_code varchar2(3))
    partition by list (state_code)(
    partition reg_west values ('AZ','CA','CO','MT','OR','ID','UT'),
    partition reg_mid values ('IA','KS','MI','MN','MO','NE','OH'),
    partition reg_def values (default));

这个列表分区的分区键可以仅为一列。使用 DEFAULT列表可以设置存储不符合列表值得分区。如果你没有设置DEFAULT列表，当插入得数据不符合已定义得分区时，
系统就会显示错误提示。

–散列分区
有时大型表中没有明显能够用于分区的列，不论是根据范围还是根据列表。可以用序列为表添加代理主键，并且根据唯一主键以平均方式划分分区。这样做的原因是没有可用于划分分区的列，或者该表的需求重点是提高插入操作的效率。
使用散列分区可以根据内部算法，以平均方式划分分区。你无法控制散列算法和Oracle划分分区的方式。你只能设置分区的数量，而Oracle会根据散列键列均分数据。

使用 create table 语句的 partition by hash 子句，创建散列分区表： 
create table f_sales(
sales_id number primary key,
sales_amt number)
partition by hash(sales_id)
partitions 2 store in (p11_tbsp,p12_tbsp) ;
drop table f_sales purge;

命名表空间的散列分区表： 
create table f_sales(
sales_id number primary key,
sales_amt number)
partition by hash(sales_id)
(partition p1 /*tablespace p11_tbsp*/,
 partition p2 /*tablespace p12_tbsp*/);

散列分区可以提升性能。拥有相同散列键值的行会存储到相同的分区。这意味着插入操作的效率特别高，因为散列算法可以确保能够通过一致的方式为分区分配数据。
而且，如果你经常选择某个特定的键值，Oracle就不得不仅通过检索一个分区查询数据。然而，如果你搜索多个范围的值，Oracle可能需要搜索所有分区，才能确定需要检索的行。因此，在散列分区表中执行范围搜索操作的效率会很低。

--组合使用多种分区方法
Oracle 允许使用多种策略（组合分区）划分分区。例如，你有一个表并想要根据数值范围为其划分分区。但是你还想根据地区列表为每个分区划分子分区。
drop table f_sales purge; 
create table f_sales(
       sales_amt number,
       state_code varchar2(3),
       d_date_id number)
partition by range (d_date_id)
subpartition by  list(state_code)