oracle表分区的用法

大风向北吹

已于 2023-04-27 15:20:13 修改

阅读量7.6k

点赞数 4

文章标签： oracle 数据库

于 2022-09-13 11:17:37 首次发布

本文链接：https://blog.csdn.net/zhongchunmei/article/details/126829135

版权

一、为什么要对表进行分区

1. 一张表超过2G的大小，ORACLE推荐使用分区表。

2. 这张表主要是查询，而且可以按分区查询，只会修改当前最新分区的数据，对以前的不怎么做删除和修改。

3. 数据量大时查询慢。

二、创建分区表

按时间范围分区

1. 按年分区

create table test.test_r1
(
id NUMBER(20) not null primary key,
name VARCHAR2(20) not null,
joindate DATE not null
) tablespace test
partition by range(joindate) interval (numtoyminterval(1,'year'))
(partition p_before_2021 values less than (to_date('2022-01-01','YYYY-MM-DD'))
);

2. 按季度分区

create table test.test_r1
(
id NUMBER(20) not null primary key,
name VARCHAR2(20) not null,
joindate DATE not null
) tablespace test
partition by range(joindate) interval (numtoyminterval(3,'month'))
(partition p_before_2021 values less than (to_date('2022-01-01','YYYY-MM-DD'))
);

3. 按月分区

create table test.test_r1
(
id NUMBER(20) not null primary key,
name VARCHAR2(20) not null,
joindate DATE not null
) tablespace test
partition by range(joindate) interval (numtoyminterval(1,'month'))
(partition p_before_2021 values less than (to_date('2022-01-01','YYYY-MM-DD'))
);

4. 按周分区

create table test.test_r1
(
id NUMBER(20) not null primary key,
name VARCHAR2(20) not null,
joindate DATE not null
) tablespace test
partition by range(joindate) interval (numtodsinterval(7,'day'))
(partition p_before_2021 values less than (to_date('2022-01-01','YYYY-MM-DD'))
);

5. 按天分区

create table test.test_r1
(
id NUMBER(20) not null primary key,
name VARCHAR2(20) not null,
joindate DATE not null
) tablespace test
partition by range(joindate) interval (numtodsinterval(1,'day'))
(partition p_before_2021 values less than (to_date('2022-01-01','YYYY-MM-DD'))
);

按数值范围分区

create table test.test_r1
(
  id NUMBER(20) not null primary key,
  name VARCHAR2(20) not null,
  joindate DATE not null
) partition by range(id)(
  partition p1 values less than(10000) tablespace space1,
  partition p2 values less than(20000) tablespace space2,
  partition p3 values less than(maxvalue) tablespace space3
) enable row movement;

三、对已有表进行分区

如果表已经存在，需要先备份原表，然后创建新表，再将备份表数据拷贝到新表，最后删除备份表。

-- 1. 重命名
alter table test_r1 rename to test_r1_temp;

-- 2. 创建分区表
create table test.test_r1
(
id NUMBER(20) not null primary key,
name VARCHAR2(20) not null,
joindate DATE not null
) tablespace test
partition by range(joindate) interval (numtoyminterval(1,'year'))
(partition p_before_2021 values less than (to_date('2022-01-01','YYYY-MM-DD'))
) enable row movement;

enable row movement的作用是允许分区表的分区键可更新

-- 4. 将 test_r1_temp 表里的数据迁移到 test_r1 表中
insert into test_r1 select * from test_r1_temp;

-- 5. 为分区表设置索引
-- Create/Recreate indexes 
create index test_r1_create_time_1 on test_r1 (joindate); 

-- 6. 删除老的 test_r1_temp 表
drop table test_r1_temp purge;

-- 7. 作用是：允许分区表的分区键是可更新。
-- 当某一行更新时，如果更新的是分区列，并且更新后的列植不属于原来的这个分区，
-- 如果开启了这个选项，就会把这行从这个分区中 delete 掉，并加到更新后所属的分区，此时就会发生 rowid 的改变。
-- 相当于一个隐式的 delete + insert ，但是不会触发 insert/delete 触发器。
alter table test_r1 enable row movement;

四、查询分区信息

1. 检查表是否是分区表
select * from user_tables where table_name ='TEST_R1';
tablespace_name为空表示是分区表，不为空表示不是分区表。

2. 查询表数据
select * from test_r1;

3. 如果知道分区名称，可按分区名称查询数据
select * from test_r1 partition(p_before_2021);

4. 如果不知道分区名称，可按分区字段查询数据
select * from test_r1 partition where JOINDATE>=to_date('2021-01-01','yyyy-mm-dd') and JOINDATE<to_date('2024-01-01','yyyy-mm-dd');

5. 查询详细分区信息
select * from  DBA_TAB_PARTITIONS;

6. 查询数据库中不同用户的分区表的数目
select owner,count(1) from dba_tables where partitioned='YES' group By owner; 
 
7. 查询数据库中用户的分区表
select * from dba_tables where partitioned='YES'  and owner='数据库用户名' ;
 
8. 查询数据库中 该用户下的对应表的分区字段
select * from dba_part_key_columns where name='表名' and owner ='数据库用户名';
 
9. 查看该数据库中 所有用户的 所有分区表的和对应分区字段
SELECT * FROM all_PART_KEY_COLUMNS;
 
10.查询数据库中，该用户下对应的分区表的表名 和分区表所对应的分区字段 
SELECT * FROM all_PART_KEY_COLUMNS t where  t.owner='数据库用户名'  and  t.name  in（select table_name from dba_tables where partitioned='YES'  and owner='数据库用户名' ）; 

11.查询具体某张表的分区情况
select * from  DBA_TAB_PARTITIONS WHERE TABLE_OWNER='SANDISK4' AND TABLE_NAME='T_TURNTABLE_TASK_PORT_LOG';

查询更多分区表信息：
　　显示数据库所有分区表的信息：DBA_PART_TABLES
　　显示当前用户可访问的所有分区表信息：ALL_PART_TABLES
　　显示当前用户所有分区表的信息：USER_PART_TABLES
　　显示表分区信息 显示数据库所有分区表的详细分区信息：DBA_TAB_PARTITIONS
　　显示当前用户可访问的所有分区表的详细分区信息：ALL_TAB_PARTITIONS
　　显示当前用户所有分区表的详细分区信息：USER_TAB_PARTITIONS
　　显示子分区信息 显示数据库所有组合分区表的子分区信息：DBA_TAB_SUBPARTITIONS
　　显示当前用户可访问的所有组合分区表的子分区信息：ALL_TAB_SUBPARTITIONS
　　显示当前用户所有组合分区表的子分区信息：USER_TAB_SUBPARTITIONS
　　显示分区列 显示数据库所有分区表的分区列信息：DBA_PART_KEY_COLUMNS
　　显示当前用户可访问的所有分区表的分区列信息：ALL_PART_KEY_COLUMNS
　　显示当前用户所有分区表的分区列信息：USER_PART_KEY_COLUMNS
　　显示子分区列 显示数据库所有分区表的子分区列信息：DBA_SUBPART_KEY_COLUMNS
　　显示当前用户可访问的所有分区表的子分区列信息：ALL_SUBPART_KEY_COLUMNS
　　显示当前用户所有分区表的子分区列信息：USER_SUBPART_KEY_COLUMNS

五、表分区的更多用法

1. 将不同分区的表保存到不同表空间，表空间创建时可设置保存的磁盘路径，这样数据文件就可以跨越多个物理磁盘进行保存。

CREATE TABLE CUSTOMER
(
CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY,
FIRST_NAME VARCHAR2(30) NOT NULL,
LAST_NAME VARCHAR2(30) NOT NULL,
PHONEVARCHAR2(15) NOT NULL,
EMAILVARCHAR2(80),
STATUS CHAR(1)
)
PARTITION BY RANGE (joindate)
(
partition p_before_2020 values less than (to_date('2021-01-01','YYYY-MM-DD')) TABLESPACE space1,
partition p_before_2021 values less than (to_date('2022-01-01','YYYY-MM-DD')) TABLESPACE space2,
)