oracle分区表

1.表空间及分区表的概念
 表空间：是一个或多个数据文件的集合，所有的数据对象都存放在指定的表空间中，但主要存放的是表，所以称作表空间。
  
 分区表： 
 当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。
 表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表。


 

 Oracle提供了分区技术以支持VLDB(Very Large DataBase)。分区表通过对分区列的判断，把分区列不同的记录，放到不同的分区中。分区完全对应用透明。

       Oracle的分区表可以包括多个分区，每个分区都是一个独立的段（SEGMENT），可以存放到不同的表空间中。查询时可以通过查询表来访问各个分区中的数据，也可以通过在查询时直接指定分区的方法来进行查询。

 

When to Partition a Table什么时候需要分区表，官网的2个建议如下：

（1）Tables greater than 2GB should always be considered for partitioning.（表的大小超过2GB）

（2）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.（表中包含历史数据，新的数据被增加到新的分区中）

2.表分区的具体作用

     1、改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。
     2、增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用；（减少了数据损坏的可能性

）
     3、维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可； （可以对单独的分区进行备份和恢复；

）
     4、均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能。 

    (5)一个分区后的对象具有多个段，这些段既可进行集体管理，也可单独管理

 

 此外,分区还可以极大简化常见的管理任务，分区是构建千兆字节数据系统或超高可用性系统的关键工具。 
 分区功能能够将表、索引或索引组织表进一步细分为段，这些数据库对象的段叫做分区。
 每个分区有自己的名称，还可以选择自己的存储特性。

   缺点： 
  分区表相关：已经存在的表没有方法可以直接转化为分区表。不过 Oracle 提供了在线重定义表的功能。 
   注意：

从应用程序的角度来看，分区后的表与非分区表完全相同，使用 SQL DML 命令访问分区后的表时，无需任何修改。

 

Oracle 10g提供了以下几种分区类型：

       （1）范围分区（range）；

       （2）哈希分区（hash）；

       （3）列表分区（list）；

       （4）范围－哈希复合分区（range-hash）；

       （5）范围－列表复合分区（range-list）。


4.1 范围分区： Range分区： 
      范围分区将数据基于范围映射到每一个分区，这个范围是你在创建分区时指定的分区键决定的。这种分区方式是最为常用的，并且分区键经常采用日期。
          举个例子：你可能会将销售数据按照月份进行分区。
      当使用范围分区时，请考虑以下几个规则：
 1、每一个分区都必须有一个VALUES LESS THEN子句，它指定了一个不包括在该分区中的上限值。
           分区键的任何值等于或者大于这个上限值的记录都会被加入到下一个高一些的分区中。
 2、所有分区，除了第一个，都会有一个隐式的下限值，这个值就是此分区的前一个分区的上限值。
 3、在最高的分区中，MAXVALUE被定义。MAXVALUE代表了一个不确定的值。这个值高于其它分区中的任何分区键的值，
           也可以理解为高于任何分区中指定的VALUE LESS THEN的值，同时包括空值。
    例一：
           假设有一个customer表，表中有数据200000行，我们将此表通过customer_id进行分区，每个分区存储100000行，
           我们将每个分区保存到单独的表空间中，这样数据文件就可以跨越多个物理磁盘。下面是创建表和分区的代码，如下：
 create table customer 
 ( 
     customer_id number not null primary key, 
     first_name  varchar2(30) not null, 
     last_name   varchar2(30) not null, 
     phone        varchar2(15) not null, 
     email        varchar2(80), 
     status       char(1) 
 ) 
 partition by range (customer_id) 
 ( 
     partition cus_part1 values less than (100000) tablespace cus_ts01, 
     partition cus_part2 values less than (200000) tablespace cus_ts02 
 )
 例二：按时间划分
 create table order_activities 
 ( 
     order_id      number(7) not null, 
     order_date    date, 
     total_amount number, 
     custotmer_id number(7), 
     paid           char(1) 
 ) 
 partition by range (order_date) 
 ( 
   partition ord_act_part01 values less than (to_date('01-1-2003','dd-mm-yyyy')) tablespace ord_ts01,
   partition ord_act_part02 values less than (to_date('01-2-2003','dd-mm-yyyy')) tablespace ord_ts02, 
   partition ord_act_part02 values less than (to_date('01-3-2003','dd-mm-yyyy')) tablespace ord_ts03 
 )
 例三：maxvalue（如果某些记录暂无法预测范围，可以创建maxvalue分区，所有不在指定范围内的记录都会被存储到maxvalue所在分区中）

 create table rangetable
 ( 
   idd   int primary key , 
   iname varchar(10), 
   grade int  
 ) 
 partition  by  range (grade) 
 ( 
       partition  part1 values  less  than (1000) tablespace  part1_tb, 
       partition  part2 values  less  than (maxvalue) tablespace  part2_tb 
 );

4.2 列表分区： List分区： 
   该分区的特点是某列的值只有几个，基于这样的特点我们可以采用列表分区。
 例一
 create table problem_tickets 
 ( 
     problem_id   number(7) not null primary key, 
     description  varchar2(2000), 
     customer_id  number(7) not null, 
     date_entered date not null, 
     status       varchar2(20) 
 ) 
 partition by list (status) 
 ( 
       partition prob_active   values ('active') tablespace prob_ts01, 
       partition prob_inactive values ('inactive') tablespace prob_ts02
  )     
 例二:
 create  table  listtable
 ( 
     id    int  primary  key , 
     name  varchar (20), 
     area  varchar (10) 
 ) 
 partition  by  list (area) 
 ( 
     partition  part1 values ('guangdong','beijing') tablespace  part1_tb, 
     partition  part2 values ('shanghai','nanjing')  tablespace  part2_tb 
 );


4.3 散列分区： Hash分区： 
 这类分区是在列值上使用散列算法，以确定将行放入哪个分区中。

当列的值没有合适的条件时，建议使用散列分区。
 散列分区为通过指定分区编号来均匀分布数据的一种分区类型，因为通过在I/O设备上进行散列分区，使得这些分区大小一致。
 例一： 指定分区
 create table hash_table 
 ( 
   col number(8), 
   inf varchar2(100) 
 ) 
 partition by hash (col) 
 ( 
   partition part01 tablespace hash_ts01, 
   partition part02 tablespace hash_ts02, 
   partition part03 tablespace hash_ts03 
 )
 简写：（按照分区数量）
 create table emp
 (
     empno number (4),
     ename varchar2 (30),
     sal   number 
 )
 partition by  hash (empno) partitions 8
 store in (emp1,emp2,emp3,emp4,emp5,emp6,emp7,emp8);
 hash分区最主要的机制：

根据hash算法来计算具体某条纪录应该插入到哪个分区中,
 hash算法中最重要的是hash函数，Oracle中如果你要使用hash分区，只需指定分区的数量即可。
 建议分区的数量采用2的n次方，这样可以使得各个分区间数据分布更加均匀。


4.4 组合范围散列分区:(范围+列表分区)
表首先按某列进行范围分区，然后再按某列进行列表分区，分区之中的分区被称为子分区。
 create table sales 
 ( 
 product_id varchar2(5), 
 sales_date date, 
 sales_cost number(10),
 status varchar2(20) 
 )
 partition by range(sales_date) subpartition by list (status) 
 (
    partition p1 values less than(to_date('2003-01-01','yyyy-mm-dd'))tablespace rptfact2009 
           ( 
               subpartition p1sub1 values ('active') tablespace rptfact2009, 
               subpartition p1sub2 values ('inactive') tablespace rptfact2009 
           ), 
    partition p2 values less than (to_date('2003-03-01','yyyy-mm-dd')) tablespace rptfact2009 
           ( 
               subpartition p2sub1 values ('active') tablespace rptfact2009, 
               subpartition p2sub2 values ('inactive') tablespace rptfact2009 
           ) 
 )
 
  4.5 复合范围散列分区：(范围+散列分区)
表首先按某列进行范围分区，然后再按某列进行散列分区。
 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 3store in (dinya_space01,dinya_space02,dinya_space03) 
  ( 
      partition part_01 values less than(to_date(‘2006-01-01’,’yyyy-mm-dd’)), 
      partition part_02 values less than(to_date(‘2010-01-01’,’yyyy-mm-dd’)), 
      partition part_03 values less than(maxvalue) 
  );
 
5.有关表分区的一些维护性操作： 
  5.1 添加分区 
 以下代码给sales表添加了一个p3分区 
 alter table sales add partition p3 values less than(to_date('2003-06-01','yyyy-mm-dd')); 
  注意：以上添加的分区界限应该高于最后一个分区界限。 
 以下代码给sales表的p3分区添加了一个p3sub1子分区 
 alter table sales modify partition p3 add subpartition p3sub1 values('complete');
  5.2 删除分区 
 以下代码删除了p3表分区： 
 alter table sales drop partition p3;
 在以下代码删除了p4sub1子分区： 
 alter table sales drop subpartition p4sub1;
  注意：如果删除的分区是表中唯一的分区，那么此分区将不能被删除，要想删除此分区，必须删除表。 
  5.3 截断分区 
指删除某个分区中的数据，并不会删除分区，也不会删除其它分区中的数据。当表中即使只有一个分区时，也可以截断该分区。通过以下代码截断分区： 
 alter table sales truncate partition p2;
 通过以下代码截断子分区： 
 alter table sales truncate subpartition p2sub2;
  5.4 合并分区 （MERGE）
是将相邻的分区合并成一个分区，结果分区将采用较高分区的界限，值得注意的是，不能将分区合并到界限较低的分区。以下代码实现了p1 p2分区的合并： 
 alter table sales merge partitions p1,p2 into partition p2;
  5.5 拆分分区 (SPLIT)
 拆分分区将一个分区拆分两个新分区，拆分后原来分区不再存在。注意不能对hash类型的分区进行拆分。 
 alter table sales split partition p2 at(to_date('2003-02-01','yyyy-mm-dd')) into (partition p21,partition p22);
  5.6 接合分区(coalesca) ----- 只能用于散列分区中 
是将散列分区中的数据接合到其它分区中，当散列分区中的数据比较大时，可以增加散列分区，然后进行接合。
 alter table sales coalesca partition;
  5.7 重命名表分区 ---rename 
 alter table sales rename partition p21 to p2;(以下代码将p21更改为p2)

  5.8 相关查询
 --跨分区查询 
 select sum( *) from 
  (select count(*) cn from t_table_ss partition (p200709_1) 
  union all 
  select count(*) cn from t_table_ss partition (p200709_2)
  ); 
 --查询表上有多少分区 
 select * from user_tab_partitions where table_name='tablename'; 
 --查询索引信息 
 select object_name,object_type,tablespace_name,sum(value) 
   from v$segment_statistics 
   where statistic_name IN ('physical reads','physical write','logical reads') and object_type='INDEX' 
  group by object_name,object_type,tablespace_name 
  order by 4 desc   
  
 --显示分区表信息：
 select * from dba_part_tables; 
 select * from all_part_tables;
 select * from user_part_tables; 
 --显示分区信息： 
 select * from dba_tab_partitions 
 select * from all_tab_partitions 
 select * from user_tab_partitions 
 --显示子分区信息
 select * from dba_tab_subpartitions 
 select * from all_tab_subpartitions 
 select * from user_tab_subpartitions 
 --显示分区列信息： 
 select * from dba_part_key_columns 
 select * from all_part_key_columns 
 select * from user_part_key_columns 
 --显示子分区列信息： 
 select * from dba_subpart_key_columns 
 select * from all_subpart_key_columns 
 select * from user_subpart_key_columns 
 --怎样查询出oracle数据库中所有的的分区表 
 select * from user_tables a where a.partitioned='YES' 
 --删除一个表的数据是 
 truncate table table_name; 
 --删除分区表一个分区的数据是 
 alter table table_name truncate partition p5;


相关补充：

 

查询分区:Select *From user_extents WHERE partition_name='分区名';

1）创建表空间

create tablespace HRPM0

datafile '/oradata/misdb/HRPM0.DBF' size 5m autoextend on next 10m maxsize unlimited

2）删除表空间（同时把数据文件也删除）

DROP TABLESPACE data01 INCLUDING CONTENTS AND DATAFILES;

   如果不想删除数据文件：Drop tablespace tablespace_name;

3) 修改表空间大小

alter database datafile '/path/NADDate05.dbf' resize 100M

4)添加数据文件（在建立表空间时，若是约束了表空间的大小，那么一段时间后，这个表空间就会被装满，无法再添加其他对象。则需要给表空间添加数据文件）:

Alter tablespace tablespace_name add datafile’ '/path/NADDate06.dbf’ size 100M;

4) 备注：

4.1).--. 禁止undo tablespace 自动增长

alter database datafile 'full_path\undotbs01.dbf' autoextend off;

4.2).-- 创建一个新的小空间的undo tablespace

create undo tablespace undotBS2 datafile 'full_path\UNDOTBS02.DBF' size 100m;

4.3).-- 设置新的表空间为系统undo_tablespace

alter system set undo_tablespace=undotBS2;

4.4).-- Drop 旧的表空间

drop tablespace undotbs1 including contents;

4.5).-- 查看所有表空间的情况

select * from dba_tablespaces

5) 查到一个最好用的表：dict

5.1)select * from dict where table_name like '%PART%'

5.2)ALL_TAB_PARTITIONS : 可以查出表所对应的分区内容；

5.3) dab_tab_partitons ：与上2);

5.4)dba_ind_partitons: 查询分区的索引；

5.5) 子分区也是一样的(dba_tab_subpartitons,dba_ind_partitons)

 

http://blog.sina.com.cn/s/blog_6bd7d94301014ki6.html