Oracle 分区表

最新推荐文章于 2025-01-26 23:56:18 发布

dyccsxg

最新推荐文章于 2025-01-26 23:56:18 发布

阅读量1.2k

点赞数 1

CC 4.0 BY-SA版权

分类专栏： J2EE Java 数据库文章标签： oracle 数据库分区表普通表转分区表

本文链接：https://blog.youkuaiyun.com/dyccsxg/article/details/10359413

Java 同时被 3 个专栏收录

30 篇文章

订阅专栏

J2EE

28 篇文章

订阅专栏

数据库

13 篇文章

订阅专栏

本文深入探讨了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;