本文介绍了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命令)Oracle查询优化--分区表建立/普通表转分区表-LMLPHP

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 modifyoracle12.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

Oracle查询优化--分区表建立/普通表转分区表-LMLPHP

09-06 12:30