Oracle分区

2018-07-29 本文已影响0人点点渔火

分区

1) 增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用；

2) 维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可；

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

4) 改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。

Oracle数据库提供对表或索引的分区方法有三种：

范围分区

create table AAA
(
 id number primary key,
 indate date not null
)
partition by range(indate)
(
 partition part_01 values less than(to_date('2006-01-01','yyyy-mm-dd'))tablespace space01,
 partition part_02 values less than(to_date('2010-01-01','yyyy-mm-dd'))tablespace space02,
 partition part_03 values less than(maxvalue)tablespace space03
);

多个字段

create table AAA
(
id number,
retail_date date
)
partition by range(id, retail_date)
(
paritition part_01 values less than(1000, to_date('2006-01-01','yyyy-mm-dd')) tablespace space01,
partition part_02 values less than(2000, to_date('2010-01-01','yyyy-mm-dd'))tablespace space02,
)

Hash分区（散列分区）

在列的取值难以确定的情况下采用的分区方法，Hash函数算法来均匀分布，降低磁盘压力，对范围查询或不等式查询没有优化效果

create table AAA
(
id number,
retail_date date
)
partition by hash(id)
(
paritition part_01 tablespace space01,
partition part_02 tablespace space02
)

存储于不同表空间

create table AAA
(
id number,
retail_date date
)
partition by hash(id)
partitions 2
store in (tbsp1, tbsp2)

列表分区

create table AAA
(
id number,
retail_date date
)
partition by list(id)
(
paritition part_01 values(1),
paritition part_02 values(2)
)

组合分区

二次分区

先id范围分区，在对子分区按照hash分区，根据name列的hash值确定该行分布在tbsp1 或 tbsp2的某个表空间上

create table AAA
(
id number primary key,
name varchar2(20),
sex varchar2(2)
)
partition by range(id)   -- id 做范围分区
subpartition by hash(name) -- name做hash分区
subpartitions 2 store in(tbsp1, tbsp2) -- hash分区有两个
(
    partition par1 values less than(5000),
    partition par2 values less than(10000),
    partition par3 values less than(maxvalue)
)

Interval 分区

Oracle11g版本引进，实现自动化分区

create table saleRecod
(
 id number primary key,
 goodsname varchar2(50),
 saoedata date
)
partition by range(saledate)
interval(numtoyminterval(1, 'year'))
(
 partition par_first values less than(to_date('2012-01-01', 'yyyy-mm-dd'))
)

* 按年分区： numtoyminterval(1, 'year')
* 按月分区： numtoyminterval(1, 'month')
* 按天分区:  numtodsinterval(1, 'day')

分区脚本

create or replace procedure add_partition(v_date_id number, v_table_name varchar2, v_part_name varchar2)
as
  flag int;
begin
 select count(*)
      into flag
      from user_tab_partitions
     where table_name = upper(v_table_name)
       and partition_name = upper(v_part_name) || v_date_id;
    if nvl(flag, 0) > 0 then
      execute immediate 'alter table ' || v_table_name || ' truncate partition ' || v_part_name || v_date_id;
    else
      execute immediate 'alter table ' || v_table_name || ' add partition ' || v_part_name || v_date_id || ' values less than (' || v_date_id || ')' ;
    end if;
end;

索引分区

如果索引对应的表数据量非常大，比如几百万到上千万条数据，索引也会占用很大空间，索引高分区能够优化系统性能，可以通过DBA_IND_PARTITIONS来查看索引分区信息

索引分区氛围i本地索引分区和全局索引分区两种：

局部索引分区的建立：

索引分区的列和表的分区列一致

create index idx_t on AAA(id) 
 local 
( 
partition idx_1 tablespace space01, 
partition idx_2 tablespace space02, 
partition idx_3 tablespace space03 
);

全局索引建立时global 子句允许指定索引的范围值，这个范围值为索引字段的范围值：

create index idx_t on AAA(id)
global partition by range(id) 
( 
partition idx_1 values less than (1000) tablespace space01, 
partition idx_2 values less than (10000) tablespace space02, 
partition idx_3 values less than (maxvalue) tablespace space03 
);

当然也可以不指定索引分区名直接对整个表建立索引:

create index idx_t on AAA(id);

分区操作

四、分区表操作

1、插入记录：insert into AAA values(1 ,sysdate);

2、查询分区表记录：select * from AAA partition(part_01);

3、更新分区表的记录：update AAA partition(part_01) t set indate=’’where id=1; 但是当更新的时候指定了分区，而根据查询的记录不在该分区中时，将不会更新数据

4、删除分区表记录：delete from AAA partition(part_02) t where id=4; 如果指定了分区，而条件中的数据又不在该分区中时，将不会删除任何数据。

5、增加一个分区：alter table AAA add partition part_04 values less than(to_date(’2012-01-01’,’yyyy-mm-dd’)) tablespace dinya_spa ce03; 增加一个分区的时候，增加的分区的条件必须大于现有分区的最大值，否则系统将提示ORA-14074 partition bound must collate higher than that of the last partition 错误。

6、合并一个分区：alter table AAA merge partitions part_01,part_02 into partition part_02; ,如果在合并的时候把合并后的分区定为part_01的时候，系统将提示ORA-14275 cannot reuse lower-bound partition as resulting partition 错误。

7、删除分区：alter table AAA drop partition part_01; 删除分区表的一个分区后，查询该表的数据时显示，该分区中的数据已全部丢失，所以执行删除分区动作时要慎重，确保先备份数据后再执行，或将分区合并。

8、分区索引重建： alter inde ware_index rebuild

Oracle分区

分区

猜你喜欢

热点阅读