Oracle 分区表

本文深入探讨了Oracle分区表的原理、优点及多种常见分区类型(如范围分区、列表分区、Hash分区、混合分区)。文章还介绍了如何将普通表转换为分区表,并提供了创建、查询、维护分区表的操作指南,旨在提升数据库查询效率与可用性。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

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;
6. 普通表转分区表
   # 创建一个普通表
create table t_user (
    id            int      not null primary key,
    user_name     varchar2(20)                 ,
    sex           varchar2(6)                  ,
    user_level    number(1)     default 1      ,
    register_time date          default sysdate
);
create index idx_t_user_name on t_user(user_name);
insert into t_user(id, user_name, sex) values(1, 'user1', 'female');
insert into t_user(id, user_name, sex) values(1999, 'user1999', 'male');
6.1 在线重定义( DBMS_REDEFINITION )
-- 1. 授权
conn / as sysdba
-- grant execute_catalog_role to scott;
grant execute on dbms_redefinition to scott;
grant create any table, alter any table, drop any table, lock any table, select any table to scott;
conn scott/tiger
-- 2. 创建临时分区表
create table pt_user (
   id            int      not null primary key,
   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
);
-- 3. 收集普通表的统计信息
execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname => 'T_USER', cascade => true);
-- 4. 检查普通表是否允许重定义
execute dbms_redefinition.can_redef_table(uname => 'SCOTT', tname => 'T_USER');
-- 5. 开始重定义
execute dbms_redefinition.start_redef_table(uname => 'SCOTT', orig_table => 'T_USER', int_table => 'PT_USER');
-- 6. 同步普通表和分区表
execute dbms_redefinition.sync_interim_table(uname => 'SCOTT', orig_table => 'T_USER', int_table => 'PT_USER');
-- 7. 创建分区表的索引
create index idx_pt_user_name on pt_user(user_name);
-- 8. 收集分区表的统计信息
execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname => 'PT_USER', cascade => true);
-- 9. 结束重定义(这里普通表 T_USER 变成了分区表,分区表 PT_USER 变成了普通表)
execute dbms_redefinition.finish_redef_table(uname => 'SCOTT', orig_table => 'T_USER',int_table => 'PT_USER');
-- 10. 验证
select partition_name, tablespace_name from all_tab_partitions where table_name='T_USER';
select * from t_user;
select * from t_user partition(p1);
select index_name, table_name from user_indexes;
-- 11. 删除临时分区表 
drop table PT_USER;
-- 12. 重命名索引
alter index idx_pt_user_name rename to idx_t_user_name;
6.2 创建时直接插入数据( Insert with a subquery method )
   # 通过子查询来建表会丢失主键、外键、索引、默认值等信息,详情请参见
   # http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_7002.htm#sthref7537
   # 注:也可以先创建一个分区表,再通过 insert 语句导入所有数据
   # insert into pt_user select * from t_user;
-- 1. 创建临时分区表并导入普通表数据
create table pt_user 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
)
as (select * from t_user);
-- 2. 创建分区表的主键、索引和默认值
alter table pt_user add constraint pk_pt_user primary key(id);
create index idx_pt_user_name on pt_user(user_name);
alter table pt_user modify (user_level default 1, register_time default sysdate);
-- 3. 将普通表重命名
rename t_user to old_t_user;
-- 4. 将分区表重命名
rename pt_user to t_user;
-- 5. 验证
select partition_name, tablespace_name from all_tab_partitions where table_name='T_USER';
select * from t_user;
select * from t_user partition(p1);
select index_name, table_name from user_indexes;
select column_name, nullable, data_default from user_tab_columns where table_name='T_USER';
-- 6. 删除普通表
drop table old_t_user;
-- 7. 重命名主键和索引
alter table t_user rename constraint pk_pt_user to pk_t_user;
alter index idx_pt_user_name rename to idx_t_user_name;
6.3 分区交换( Partition exchange method )
   # 这种方法是对数据字典中分区和表的定义进行了修改,没有数据复制或修改,效率最高
   # 适用于将普通表中的数据转移到分区表中的一个分区
-- 1. 创建分区表
create table pt_user (
    id            int      not null primary key,
    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 (3000) tablespace ts1,
    partition p2 values less than (6000) tablespace ts2,
    partition p3 values less than (MAXVALUE) tablespace ts2
);
-- 2. 将普通表与分区表的一个分区进行交换
alter table pt_user exchange partition p1 with table t_user;
select * from pt_user partition(p1);
-- 3. 删除普通表
drop table t_user;
-- 4. 将分区表重命名
rename pt_user to t_user;
   

# 相关链接
  [创建分区表 10gR2] http://docs.oracle.com/cd/B19306_01/server.102/b14231/partiti.htm#i1106387
  [创建分区表 11gR1] http://docs.oracle.com/cd/B28359_01/server.111/b32024/part_admin.htm#sthref81
  [DBMS_REDEFINITION] http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_redefi.htm
  [DBMS_REDEFINITION 授权] http://docs.oracle.com/cd/B19306_01/server.102/b14231/tables.htm#sthref2365
  [分区表总结1] http://tianzt.blog.51cto.com/459544/171759
  [分区表总结2] http://blog.youkuaiyun.com/tianlesoftware/article/details/4717318
  [表分析] http://blog.youkuaiyun.com/mack415858775/article/details/10967965

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值