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)