Oracle 分区表

dyccsxg

浏览: 205236 次
性别:
来自: 青岛

最近访客更多访客>>

hbyufan

springdeng

belle-liang

doingzone

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

1. 分区表
分区表在逻辑上是一张完整的表，与普通表完全一样；
分区表在物理上将数据存储在多个表空间中（每个表空间可包含多个数据文件）；
一个普通表对应一个段，存储在一个表空间中；
将一个表分割成多个段称为分区表，其中每个段可以称为一个分区，分区表可以分布在多个表空间中；
数据库逻辑结构：
段(Segment)：属于同一对象的范围组成一个段，段可以跨越数据文件（由多个数据文件中的范围组成）；
|_ 范围(Extent)：一次连续分配的块，范围不能跨越数据文件；
|_ 块(Block)：最基本的存储单元

-- 查询 '段' 的信息
select tablespace_name, segment_name, partition_name, extents 
from user_segments 
where segment_name='DEPT';
-- 查询 '范围' 的信息
select tablespace_name, segment_name, extent_id, blocks 
from user_extents 
where segment_name='DEPT';

2. 分区表的优点
提高查询速度：可以只查询指定的分区，不用全表扫描；
提高可用性：如果表的某个分区出现问题，其他分区中的数据仍然可用；
均衡I/O ：可以把不同的分区映射到不同的磁盘，平衡整体性能；

3. 几种常用的分区表
# 查看数据库是否支持分区表

select * from v$option where parameter='Partitioning';

# 创建表空间

create tablespace ts1 datafile 'D:/Oracle/oradata/orcl/ts1.dbf' size 2m;
create tablespace ts2 datafile 'D:/Oracle/oradata/orcl/ts2.dbf' size 2m;
create tablespace ts3 datafile 'D:/Oracle/oradata/orcl/ts3.dbf' size 2m;
create tablespace ts4 datafile 'D:/Oracle/oradata/orcl/ts4.dbf' size 2m;
select tablespace_name, file_name from dba_data_files;
-- drop tablespace ts1 including contents and datafiles;

3.1 范围分区（将数据基于范围映射到某一个分区）
3.1.1 按数字大小进行划分

create table t_user (
    id            int                          ,
    user_name     varchar2(20)                 ,
    sex           varchar2(6)                  ,
    user_level    number(1)     default 1      ,
    register_time date          default sysdate
) 
partition by range(id)
(
    partition p1 values less than (1000) tablespace ts1,
    partition p2 values less than (2000) tablespace ts2
);
insert into t_user(id, user_name, sex) values(1, 'user1', 'female');
insert into t_user(id, user_name, sex) values(1999, 'user1999', 'male');

# 上面这种分区表，当 id 超出最大值时，将插入失败，如下所示

SQL> insert into t_user(id, user_name, sex) values(2000, 'user2000', 'male');
insert into t_user(id, user_name, sex) values(2000, 'user2000', 'male')
            *
第 1 行出现错误:
ORA-14400: 插入的分区关键字未映射到任何分区

# 为了解决这种超出最大值时无法插入的问题，需要使用到 MAXVALUE 关键字

drop table t_user;
create table t_user (
    id            int                          ,
    user_name     varchar2(20)                 ,
    sex           varchar2(6)                  ,
    user_level    number(1)     default 1      ,
    register_time date          default sysdate
) 
partition by range(id)
(
    partition p1 values less than (1000) tablespace ts1,
    partition p2 values less than (2000) tablespace ts2,
    partition p3 values less than (MAXVALUE) tablespace ts2
);
insert into t_user(id, user_name, sex) values(2000, 'user2000', 'male');

# 查询

-- 查询所有数据
select * from t_user;
-- 查询指定分区的数据
select * from t_user partition(p1);

3.1.2 按日期进行划分

drop table t_user;
create table t_user (
    id            int                          ,
    user_name     varchar2(20)                 ,
    sex           varchar2(6)                  ,
    user_level    number(1)     default 1      ,
    register_time date          default sysdate
) 
partition by range(register_time)
(
    partition p1 values less than (to_date('2013-09-01', 'yyyy-MM-dd')) tablespace ts1,
    partition p2 values less than (to_date('2013-10-01', 'yyyy-MM-dd')) tablespace ts2,
    partition p3 values less than (MAXVALUE) tablespace ts2
);
insert into t_user(id, user_name, sex) values(1, 'user1', 'female');

3.2 列表分区（这种分区的特点是某列的值只有几个）

drop table t_user;
create table t_user (
    id            int                          ,
    user_name     varchar2(20)                 ,
    sex           varchar2(6)                  ,
    user_level    number(1)     default 1      ,
    register_time date          default sysdate
) 
partition by list(sex)
(
    partition p1 values ('female') tablespace ts1,
    partition p2 values ('male') tablespace ts2
);
insert into t_user(id, user_name, sex) values(1, 'user1', 'female');
insert into t_user(id, user_name, sex) values(1999, 'user1999', 'male');

3.3 Hash分区（这种分区的特点是对某列使用 hash 算法以确定数据放入哪个分区）

drop table t_user;
create table t_user (
    id            int                          ,
    user_name     varchar2(20)                 ,
    sex           varchar2(6)                  ,
    user_level    number(1)     default 1      ,
    register_time date          default sysdate
) 
partition by hash(id)
(
    partition p1 tablespace ts1,
    partition p2 tablespace ts2,
    partition p3 tablespace ts1,
    partition p4 tablespace ts2
);
insert into t_user(id, user_name, sex) values(1, 'user1', 'female');
insert into t_user(id, user_name, sex) values(1999, 'user1999', 'male');

4. 混合分区
4.1 范围列表分区（这种分区是先按某列进行范围分区，然后再按某列进行列表分区）

drop table t_user;
create table t_user (
    id            int                          ,
    user_name     varchar2(20)                 ,
    sex           varchar2(6)                  ,
    user_level    number(1)     default 1      ,
    register_time date          default sysdate
) 
partition by range(id) subpartition by list(sex)
(
    partition p1 values less than (1000) tablespace ts1(
        subpartition p1_1 values('female'),
        subpartition p1_2 values('male')
    ),
    partition p2 values less than (2000) tablespace ts2(
        subpartition p2_1 values('female'),
        subpartition p2_2 values('male')
    ),
    partition p3 values less than (MAXVALUE) tablespace ts2
);
insert into t_user(id, user_name, sex) values(1, 'user1', 'female');
insert into t_user(id, user_name, sex) values(1999, 'user1999', 'male');

4.2 范围 Hash 分区（这种分区是先按某列进行范围分区，然后再按某列进行 hash 分区）

-- 下面的代码创建了3个范围分区，每个范围分区包含8个子分区，同时将数据存放在4个表空间中
drop table t_user;
create table t_user (
    id            int                          ,
    user_name     varchar2(20)                 ,
    sex           varchar2(6)                  ,
    user_level    number(1)     default 1      ,
    register_time date          default sysdate
) 
partition by range(id) 
subpartition by hash(user_name) subpartitions 8 store in(ts1, ts2, ts3, ts4)
(
    partition p1 values less than (1000),
    partition p2 values less than (2000),
    partition p3 values less than (MAXVALUE)
);
insert into t_user(id, user_name, sex) values(1, 'user1', 'female');
insert into t_user(id, user_name, sex) values(1999, 'user1999', 'male');

5. 常用操作

-- 查询所有分区表
select table_name from all_part_tables;

-- 增加分区(增加分区时不能与已有分区相冲突)
alter table t_user add partition p4 values less than(3000);
-- 删除分区
alter table t_user drop partition p4;
-- 查询表的分区信息
select partition_name, tablespace_name from all_tab_partitions where table_name='T_USER';
select partition_name, subpartition_name, tablespace_name 
from all_tab_subpartitions 
where table_name='T_USER';

-- 查询某个分区中的数据
select * from t_user partition(p1);
-- 查询某个子分区中的数据
 select * from t_user subpartition(p1_1);
-- 删除某个分区中的数据
alter table t_user truncate partition p2;

分享到：