Oracle查询优化--分区表建立/普通表转分区表
本文介绍了Oracle表分区的方法，将已有的非分区表转化为分区表，也可以直接建立新的分区表，从而实现大表查询的优化。主要通过DBMS_REDEFINITION 和 alter table xxx modify 方法，DBMS_REDEFINITION 适用于所有版本，操作较为繁琐，alter table xxx modify 适用于12.2+版本，操作较为简单。
一、表分区思想
1、表分区核心思想
表分区可以将一张大表存储在不同的物理空间或者不同磁盘，操作和物理层面关联。最终实现逻辑层面的查询sql语句不变，但物理查询时优化，扫描更少的物理空间，可以实现分散减轻BIOS操作系统及物理层级的IO读写压力。
2、表分区设计方法
表分区时，合理的设置分区键（更有效区分数据）、将表分区分配关联到不同磁盘空间，可以更有效的提高查询效率。
如下为oracle表存储数据大致逻辑：
逻辑Sql语句 ==> 逻辑表 ==> 表空间 ==> 关联存储文件 ==> 不同物理磁盘
通常不设置表分区时，一张表甚至一个数据库的所有表都在默认表空间USERS，表空间时关联.dbf文件，dbf文件位置即为表空间的磁盘存储位置。计算机对一个磁盘的IO读写通常串行，只有一个读写头，因此我们可以在不同的磁盘里建立不同的表空间，然后让一张表分区，不同的分区关联到不同表空间，就可以实现一张表存储在不同的磁盘里，实现IO并行，减轻读写压力，提高查询速度。
建议查询该表占据2G以上再进行分区。
3、分区类型
分区通常有范围分区、间隔分区、hash分区等，根据实际业务指定易区分表数据的分区键，指定分区键后，建立的索引首先与分区键重叠，可以加速查询。
二、表分区前置操作
1、查看文件系统目录挂载的磁盘
Df -th
LVM文件系统分区挂载操作命令查看或更改文件夹挂载到不同磁盘（fdisk命令）
2、建立表空间关联到不同磁盘
在不同磁盘建立表空间
找到挂载在不同磁盘的目录，或者将文件夹用LVM命令挂载在不同磁盘。Eg：/dev1 /dev2 /dev3 3个文件系统目录挂载在不同的磁盘。
在不同磁盘建立表空间FIT_PART_03、FIT_PART_09、FIT_PART_12。
create tablespace FIT_PART_03
logging
datafile '/dev1/oraDataJtg/XE/10DFE005CF4A2D21E063020011AC8573/datafile/myjtg.dbf'
size 2G
autoextend on
next 1G maxsize unlimited
extent management local;create tablespace FIT_PART_09
logging
datafile '/dev2/oraDataJtg/XE/10DFE005CF4A2D21E063020011AC8573/datafile/myjtg.dbf'
size 2G
autoextend on
next 1G maxsize unlimited
extent management local;create tablespace FIT_PART_12
logging
datafile '/dev3/oraDataJtg/XE/10DFE005CF4A2D21E063020011AC8573/datafile/myjtg.dbf'
size 2G
autoextend on
next 1G maxsize unlimited
extent management local;三、alter table xxx modify（oracle12.2+版本）
1、间隔分区（同一个表空间）
如下为间隔分区2023-05-01之前的数据作为一个分区，之后的数据每隔一个月自动分区。Local关键字表明索引和表的分区在同一空间。
ALTER TABLE JTG.FACTORY_CHECK_TASK MODIFY
PARTITION BY RANGE (create_time)
interval (numtoyminterval(1, 'MONTH'))
(
PARTITION FCTI_PART_01 VALUES LESS THAN (TO_DATE('2023-05-01','yyyy-mm-dd')) tablespace USERS
) ONLINE
UPDATE INDEXES
(
JTG.FCTI1 LOCAL,
JTG.FCTI2 LOCAL,
JTG.FCTI3 LOCAL,
JTG.FCTI4 LOCAL
);2、间隔分区（同一表在不同表空间）
store表分区指定表空间，可以将一张表分区在不同的磁盘中。每隔一个月的数据，自动创建分区，并轮流分配到三个表空间。
ALTER TABLE JTG.FACTORY_CHECK_TASK_ITEM MODIFY
PARTITION BY RANGE (create_time)
interval (numtoyminterval(1, 'MONTH'))
(
PARTITION FCTI_PART_01 VALUES LESS THAN (TO_DATE('2023-05-01','yyyy-mm-dd'))
Store in ( FIT_PART_03, FIT_PART_09, FIT_PART_12) ) ONLINE
UPDATE INDEXES;3、范围分区（一张表在不同表空间）
表分区指定表空间，可以将一张表分区在不同的磁盘中。
如下'2023-10-01之前的数据在FIT_PART_09表空间，2023-10-01~2024-01-01的数据在FIT_PART_12表空间，2024-01-01~2024-04-01的数据在FIT_PART_12表空间, 之后的数据在USERS表空间。
ALTER TABLE JTG.factory_Check_task MODIFY
PARTITION BY RANGE (create_time)
(
partition FIT_PART_23_09 values less than (to_date('2023-10-01', 'yyyy-mm-dd')) tablespace FIT_PART_09,
partition FIT_PART_23_12 values less than (to_date('2024-01-01', 'yyyy-mm-dd')) tablespace FIT_PART_12,
partition FIT_PART_24_03 values less than (to_date('2024-04-01', 'yyyy-mm-dd')) tablespace FIT_PART_03,
partition FIT_PART_DEFAULT values less than (maxvalue) tablespace USERS
ONLINE
UPDATE INDEXES
(
JTG.FIT1 LOCAL,
JTG.FIT2 LOCAL,
JTG.FIT3 LOCAL
);四、DBMS_REDEFINITION在线重定义方法
SET TIMING ON begin
dbms_redefinition.can_redef_table('JTG','FACTORY_CHECK_PLAN',1);--重定义前检查*
end;-- 查询分区
select partition_name from user_tab_partitions where table_name='FACTORY_CHECK_PLAN';-- 表置换
create table mem_ext for exchange with table members;-- 允许行移动
enable row movement--建立临时分区表
create table FACTORY_CHECK_PLAN_NEW
(
id NUMBER(15) not null,
// 和原来的表结构一样
) PARTITION BY RANGE (create_time)
interval (numtoyminterval(1, 'MONTH'))
(
PARTITION FCTI_PART_01 VALUES LESS THAN (TO_DATE('2023-05-01','yyyy-mm-dd'))
Store in ( FIT_PART_03, FIT_PART_09, FIT_PART_12) )
UPDATE INDEXES;-- 迁移数据
exec DBMS_REDEFINITION.start_redef_table('HSADM', 'FACTORY_CHECK_PLAN', 'FACTORY_CHECK_PLAN_NEW');--结束
begin
dbms_redefinition.finish_redef_table('HSADM', 'FACTORY_CHECK_PLAN', 'FACTORY_CHECK_PLAN_NEW');
end;五、验证
-- 验证
select partition_name from user_tab_partitions where table_name = 'FACTORY_INSPECTION_TASK';select a.segment_name,a.tablespace_name,b.table_name,a.bytes/1024/1024 "大小(M)",a.blocks
from user_segments a, user_indexes b
where a.segment_name = b.index_name
and a.segment_type = 'INDEX' --索引d
-- and a.tablespace_name='APPINDEX' --表空间
and b.table_name = 'FACTORY_CHECK_TASK_ITEM' --索引所在表
order by table_name,a.bytes/1024/1024 desc