oracle 二次分区,oracle，分区表(二)

分区表的用意是使大型表 分而治之。但是分区表是否一定会加快访问呢。

可能更慢，可能更快，可能没变化。

好处就是

一： 提高可用性

因为分区后分区的损坏不影响其它分区的使用，除非你一定要查找损坏分区的内容

二： 加快索引及数据重建速度。

如果你有一个100G表，有部分数据行迁移，行连接。要重建会很麻烦，如果做为分区表，只重建其中一个分区会很方便

三：并行dml 也是分区表的重要好处

oracle 对分区表进行并行的 insert,update或delete 操作，会大大加快速度。

四：查询速度的提升

1.分区消除(partition elimination)，即不考虑其它分区的内容，如果以前10G的数据分为10个分区，现在就只找1G的数据。肯定会提高速度

Sql代码

SELECT*FROMt_par PARTITION(p1);

SELECT * FROM t_par PARTITION(p1);

2.并行操作(parallel operation),分区表是在物理上把大表分开，在多cpu并且io 充足的情况下，并行操作的好处不言而喻。

表分区机制

目前Oracle中有4种对表分区的方法：

1、区间分区：通过数据范围进行分区。例如，时间戳在Jan-2005内的所有记录都存储在分区1中，时间戳在Feb-2005内的所有记录都存储在分区2中，依此类推。这可能是Oracle中最常用的分区机制。

2、散列分区：这是指在一个列(或多个列)上应用一个散列函数，行会按这个散列值放在某个分区中。

3、列表分区：指定一个离散值集，来确定应当存储在一起的数据。例如，可以指定STATUS列值在(’A’,’M’,’Z’)中的行放在分区1中，STATUS值在(‘D’,’P’,’Q’)中的行放在分区2中，依此类推。

4、组合分区：这是区间分区和散列分区的一种组合，或者是区间分区与列表分区的组合。通过组合分区，你可以先对某些数据应用区间分区，再在区间中根据散列或列表来选择最后的分区。

创建语句其本都相同，以区间分区为例

Sql代码

/***********************************

desc:   创建一个按时间分区的分区表

time:   2010-1-6

created:benson

***********************************/

/***********************************

desc: 创建一个按时间分区的分区表

time: 2010-1-6

created:benson

***********************************/

查看用户分区表状态

Sql代码

select*fromuser_tab_partitions;

select * from user_tab_partitions;

创建分区表按照时间分区

Sql代码

createtablet_par(s_timedate,id number)

partitionbyrange(s_time)

(

partition pmaxvaluesless than(maxvalue) tablespace users

);

create table t_par(s_time date,id number)

partition by range(s_time)

(

partition pmax values less than(maxvalue) tablespace users

);

插入100条记录，100天的数据

Sql代码

insertintot_par

selectto_date('2010-12-31','yyyy-mm-dd')-mod(rownum,100),

rownum

fromdba_objects

whererownum<101;

insert into t_par

select to_date('2010-12-31','yyyy-mm-dd')-mod(rownum,100),

rownum

from dba_objects

where rownum<101;

按时间分为两个区p1,pmax

Sql代码

altertablet_par

split partition pmaxat(to_date('2010-10-1','yyyy-mm-dd'))

into

(

partition p1,

partition pmax

);

alter table t_par

split partition pmax at(to_date('2010-10-1','yyyy-mm-dd'))

into

(

partition p1,

partition pmax

);

查看p1分区，可以看到p1分区为 2010-10-1以前的数据

Sql代码

SELECT*FROMt_par PARTITION(p1);

SELECT * FROM t_par PARTITION(p1);

插入一条2010-10-1日以前的数据。验证是否插入到了 partition p1中

Sql代码

insertintot_parvalues(to_date('2010-9-1','yyyy-mm-dd'),222);

insert into t_par values(to_date('2010-9-1','yyyy-mm-dd'),222);

验证数据

Sql代码

SELECT*FROMt_par PARTITION(p1);

SELECT * FROM t_par PARTITION(p1);

我们一般会这样查看分区表

Java代码

SELECT table_name,partition_name,u.high_value from user_tab_partitions u WHERE table_name NOT LIKE'BIN%';

SELECT table_name,partition_name,u.high_value from user_tab_partitions u WHERE table_name NOT LIKE 'BIN%';

删除分区表

Sql代码

ALTERTABLEt_parDROPPARTITION p20100106;