oracle优化之表分区

一、首先介绍下oracle表分区的优缺点：

优点：

1.改善查询性能：对分区对象的查询只搜索自己相关的分区

2.增强可用行：如果有其中一个分区出现故障，并不影响其它分区的使用

3.维护方便：如果有其中一个分区出现故障，需要修复，只修复故障区域就可

缺点：

1.如果已经存在的表不能进行直接转化为分区表

二、表分区的类型以及操作方法：

1.范围分区：

范围分区是将数据按照范围映射不同的分区，这个范围是在创建分区时指定的分区键决定的，并且这种分区方式也是最常用的，分区键常采用日期。

规则：

a.必须使用VALUES LESS THEN字句来指定一个上限值

b.除了第一个分区，其它都会隐身有一个下限值（上一个的上限值）

c.在最高的分区中。MAXVALUE被定义。它代表一个不确定的值，这个值高于其它分区中的任何分区键的值，即高于（VALUES LESS THEN 指定的值），同时包括空值

例子：

--以 CUSTOMER_ID 字段来进行分区

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 
)
--此处的200000可以用MAXVALUE，代表一个不确定的值

--下面是一个按照时间分区的例子：

PARTITION BY RANGE (ORDER_DATE) 
(

  PARTITION ORD_ACT_PART01 VALUES LESS THAN (TO_DATE('01- MAY -2003','DD-MON-YYYY'))        TABLESPACE ORD_TS01,

  PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE('01-JUN-2003','DD-MON-YYYY'))  TABLESPACE ORD_TS02,

  PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE('01-JUL-2003','DD-MON-YYYY'))  TABLESPACE ORD_TS03

)

 

 

2.列表分区：某列只用几种值（即：枚举值）

PARTITION  BY  LIST (area) 
( 
    PARTITION  part1 VALUES ('guangdong','beijing') TABLESPACE  Part1_tb, 
    PARTITION  part2 VALUES ('shanghai','nanjing')  TABLESPACE  Part2_tb 
);

 3.散列分区：

PARTITION BY HASH (COL) 
( 
  PARTITION PART01 TABLESPACE HASH_TS01, 
  PARTITION PART02 TABLESPACE HASH_TS02, 
  PARTITION PART03 TABLESPACE HASH_TS03 
)

 

4.组合分区：

 

 三、oracle分区表的查询：

 1.sql中不指定分区：

select*from 分区表 t where createdate > to_date('2009-07-12','yyyy-mm-dd') and createdate < to_date('2009-08-12','yyyy-mm-dd')

 

 如果查询字段是分区键，会自动在数据存在的区进行检索；如果不是没有使用分区键进行索引，则会查询所有的表分区，这样也就增大了数据库压力。

2.sql中指定分区：

 

select * from 分区表  PARTITION(P200907) t where sms_report_date > to_date('2009-07-12','yyyy-mm-dd') and sms_report_date < to_date('2009-08-12','yyyy-mm-dd')

 sql查询中指定在哪个分区上面，使用partition关键字指定表分区的名称

 

 3.使用truncate删除分区表

alter table range_part_tabl truncate partition p9;

 4.分区交换(将分区表中的数据和mid_table的数据进行交换)

alter table range_part_tab exchange partition p8 with table mid_table

 5.分区切割

alter table range_part_tab split partition p_max at(to_date('2013-02-01','yyyy-MM-dd')) 
into (partition p2013_01,partition p_max);

 6.分区的新增与删除

alter table range_part_tab add partition p2013_01 values less than (to_date('2013-02-01','yyyy-MM-dd'));
alter table range_part_tab drop partition p2013_01;