Oracle创建表分区

在数据库性能需要保障的时候，可以使用Oracle进行分区操作
一 表分区
    1.范围分区：一般使用部门字段，地区字段，时间字段

CREATE TABLE USER
(user_id number(20) primary key,
user_name varchar2(100) not null,
regist_time varcahr(200) not null
)
partition by range(regist_time)
(
partition part_01 values less than(to_date('2013-01-01','yyyy-mm-dd') ) tablespace space_01,
partition part_02 values less than(to_date('2014-01-01','yyyy-mm-dd') ) tablespace space_02,
partition part_03 values less than(maxvalue) tablespace space_03
);

    关键字:PARTITION BY RANGE(RANGE_COLUMN)
    上面为按照注册日期为表分三个区，最后一个分区不指定日期值，均设定为MAXVALUE。
    创建分区的同时为分区指定使用的表空间。

    2.散列分区：一般使用编号字段

CREATE TABLE USER
(user_id number(20) primary key,
user_name varchar2(100) not null,
regist_time varcahr(200) not null
)
partition by hash(user_id)
(
partition part_01 tablespace space_01,
partition part_02 tablespace space_02,
partition part_03 tablespace space_03
);

 

二.enable row movement

    一般用于分区表，把表设置成enable row_movement允许修改分区键，并自动根据修改后的的值，决定记录是否被move到其它表分区。即某一行更新时，如果更新的是分区列，并且更新后的列值不属于原来的这个分区，如果开启了这个选项，就会把这行从这个分区中delete 掉，并加到更新后所属的分区。相当于一个隐式的delete+insert，但是不会触发insert/delete触发器。如果没有开启这个选项，就会在更新时报错。

    实例：

create table TARGET_IDENTITY_INFO
(
  CREATED_BY     VARCHAR2(100) default USER not null,
  DATE_CREATED   DATE default SYSDATE not null,
  UPDATED_BY     VARCHAR2(100) default USER not null,
  DATE_UPDATED   DATE default SYSDATE not null,
  USER_ID        NUMBER(20) PRIMARY KEY,
  USER_NAME      VARCHAR2(100) not null,
  REGIST_TIME    VARCAHR(200) not null
)
partition by range (REGIST_TIME)
(
  partition TARGET_201311_PT values less than (to_date('2013-12-01','YYYY-MM-DD')) ,
  partition TARGET_201312_PT values less than (to_date('2014-01-01','YYYY-MM-DD')) ,
  partition TARGET_201401_PT values less than (to_date('2014-02-01','YYYY-MM-DD')) ,
  partition TARGET_201402_PT values less than (to_date('2014-03-01','YYYY-MM-DD')) ,
  partition TARGET_201403_PT values less than (to_date('2014-04-01','YYYY-MM-DD')) ,
  partition TARGET_201404_PT values less than (to_date('2014-05-01','YYYY-MM-DD')) ,
  partition TARGET_201405_PT values less than (to_date('2014-06-01','YYYY-MM-DD')) ,
  partition TARGET_201406_PT values less than (to_date('2014-07-01','YYYY-MM-DD')) ,
  partition TARGET_201407_PT values less than (to_date('2014-08-01','YYYY-MM-DD')) ,
  partition TARGET_201408_PT values less than (to_date('2014-09-01','YYYY-MM-DD')) ,
  partition TARGET_201409_PT values less than (to_date('2014-10-01','YYYY-MM-DD')) ,
  partition TARGET_201410_PT values less than (to_date('2014-11-01','YYYY-MM-DD')) ,
  partition TARGET_201411_PT values less than (to_date('2014-12-01','YYYY-MM-DD')) ,
  partition TARGET_201412_PT values less than (to_date('2015-01-01','YYYY-MM-DD')) ,
  partition TARGET_201501_PT values less than (to_date('2015-02-01','YYYY-MM-DD')) ,
  partition TARGET_201502_PT values less than (to_date('2015-03-01','YYYY-MM-DD')) ,
  partition TARGET_201503_PT values less than (to_date('2015-04-01','YYYY-MM-DD')) ,
  partition TARGET_201504_PT values less than (to_date('2015-05-01','YYYY-MM-DD')) ,
  partition TARGET_201505_PT values less than (to_date('2015-06-01','YYYY-MM-DD')) ,
  partition TARGET_201506_PT values less than (to_date('2015-07-01','YYYY-MM-DD')) ,
  partition TARGET_201507_PT values less than (to_date('2015-08-01','YYYY-MM-DD')) ,
  partition TARGET_201508_PT values less than (to_date('2015-09-01','YYYY-MM-DD')) ,
  partition TARGET_201509_PT values less than (to_date('2015-10-01','YYYY-MM-DD')) ,
  partition TARGET_201510_PT values less than (to_date('2015-11-01','YYYY-MM-DD')) ,
  partition TARGET_201511_PT values less than (to_date('2015-12-01','YYYY-MM-DD')) ,
  partition TARGET_201512_PT values less than (to_date('2016-01-01','YYYY-MM-DD')) ,
  partition TARGET_DEFAULT_PT values less than (maxvalue)
)enable row movement;

 

三.查看表分区信息

    1.DBA查看表分区

select * from dba_tab_partitions

    2.USER查看表分区

select * from user_tab_partitions

    3.DBA查看表分区类型

select * from dba_part_tables

    4.USER查看表分区类型

select * from user_part_tables

    5.查询分区表信息

select * from table partition(part_01)