当前位置：首页 > news >正文

oracle表分区--范围分区

news 来源：原创 2025/5/11 5:00:40

文章目录

oracle表分区
- 分区的原因
- 分区的优势
- oracle表分区的作用
- oracle表分区类型
- - 一、范围分区
  - 二、创建分区表和使用：
  - - 1、按照数值范围划分
    - 2、按照时间范围
    - 3、MAXVALUE
    - 2. 向现有表添加新的分区
    - 3、分区维护和重新组织（合并/删除）

oracle表分区

oracle 11g 支持自动分区，不过得在创建表时就设置好分区。

分区的原因

单表的数据量如果太大，会影响到读写性能。我们可以使用分库分表来解决单表的性能问题。Oracle的分区表是将一张大表在物理上分成几张较小的表，从逻辑上来看仍然是一张完整的表。这样每次DML操作可以只考虑其中一张分区表。oracle建议单表大小超过2GB时就使用分区表。
分区功能能够将表、索引或索引组织表进一步细分为段，从而能够更精确地管理和访问这些数据库对象。这些数据库对象的段叫做分区。
分区表应用在大表更合适，至少要大于100万条的记录才可以考虑使用分区表。
现实中的场景：图书馆的图书分区

分区的优势

（IO速度、删除、查询、维护、安全）

由于Oracle数据库可以将分区指定为不同的表空间,而不同的表空间是可以指向不同的磁盘设备的,在很多时候,磁盘设备的I/O速度是系统进行数据操作的瓶颈,而分区并行,则起到了优化物理硬件资源,从而缩短了执行的时间的作用。

分区，就类似于我们将图书分类放到了不同的区间中，这个时候，我们就可以只针对其中的某一类图书进行操作，比如进行该类书籍的查询、上架、下架、甚至将该类图书付之一炬，这带来了两个特别大的优势，第一，我们无视其他分区的数据；第二，我们可以很方便的对本区的数据进行删除操作。

提高了特定的查询速度，比如我们现在只需要查找IT类图书的数量，因为我们能够在第一意识中，排除其他非IT类书籍，必然会提高查询的性能。

节约维护的成本，传统的维护操作，例如重建索引等，因为有锁机制的存在，是会影响到其他人的读取和更新删除操作的，但是由于有了分区，我们在维护IT类书籍的时候，就不会对其他分类的数据造成影响，这个时候，可以降低因为维护数据而对其他系统数据产生的各种影响。

从安全的角度上而言，我们将不同的分区放在不同的表空间当中，就类似于我们没有将所有的鸡蛋放到一个篮子里面，必然会带来安全方面的提升。

oracle表分区的作用

oracle表分区类型

在这里插入图片描述

一、范围分区

范围分区将数据基于范围映射到每一个分区，这个范围是你在创建分区时指定的分区键决定的。这种分区方式是最为常用的，并且分区键经常采用日期。当使用范围分区时，请考虑以下几个规则：
1）每一个分区都必须有一个VALUES LESS THEN子句，它指定了一个不包括在该分区中的上限值。分区键的任何值等于或者大于这个上限值的记录都会被加入到下一个高一些的分区中。
2）所有分区，除了第一个，都会有一个隐式的下限值，这个值就是此分区的前一个分区的上限值。
3）在最高的分区中，MAXVALUE被定义。MAXVALUE代表了一个不确定的值。这个值高于其它分区中的任何分区键的值，也可以理解为高于任何分区中指定的VALUE LESS THEN的值，同时包括空值。

在这里插入图片描述

二、创建分区表和使用：

1、按照数值范围划分

--1.创建表CREATE TABLE sales (sale_id NUMBER,amount NUMBER
)
-- 2.创建范围分区
PARTITION BY RANGE (sale_id) (
--范围分区根据分区的范围添加相应的数据到指定的范围分区之中PARTITION p1 VALUES LESS THAN (100),--范围<100PARTITION p2 VALUES LESS THAN (200),--范围100-200PARTITION p3 VALUES LESS THAN (300),-- 200-300PARTITION p4 VALUES LESS THAN (400)--300-400
);--VALUES LESS THAN限制的分区的范围上限和下限-- 3.根据范围分区进行数据添加
insert into sales values(100,200)
insert into sales values(200,200);
insert into sales values(300,301);
insert into sales values(50,401);-- 查看分区表：可以根据查看分区查询各范围分区相应的数据
select * from sales partition(p1);--partition 分区
select * from sales partition(p2);
select * from sales partition(p3);
--查看表
select * from sales

2、按照时间范围

CREATE TABLE orders1 (order_id NUMBER,order_date DATE,amount NUMBER
)
PARTITION BY RANGE (order_date) (PARTITION p1 VALUES LESS THAN (TO_DATE('2022-01-01', 'YYYY-MM-DD')),PARTITION p2 VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD')),PARTITION p3 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')));INSERT INTO orders1 (order_id, order_date, amount) VALUES (1, TO_DATE('2021-12-31', 'YYYY-MM-DD'), 100);
INSERT INTO orders1 (order_id, order_date, amount) VALUES (2, TO_DATE('2022-06-01', 'YYYY-MM-DD'), 200);select *  from  orders1-- 查看表数据select *  from  orders1 partition(p2)--查看表分区数据（指定分区）

3、MAXVALUE


CREATE TABLE RANGETABLE  
(   idd   INT PRIMARY KEY ,   iNAME VARCHAR(10),   grade INT    
)   
PARTITION  BY  RANGE (grade)   
(   PARTITION  p1 VALUES  LESS  THEN (1000) TABLESPACE  Part1_tb,   PARTITION  p2 VALUES  LESS  THEN (MAXVALUE) TABLESPACE  Part2_tb  --超过p1范围区的数据，都可以向新增到p2中
);  --——在表上执行查询  
select * from RANGETABLE;  --——在表分区上执行查询  
select * from RANGETABLE partition(p1);

2. 向现有表添加新的分区

如果你已经有一个分区表，并且希望添加新的分区，可以使用ALTER TABLE … ADD PARTITION语句。例如，如果我们想在上面的RANGETABLE 表中添加一个新分区p3：

ALTER TABLE RANGETABLE  PARTITION  p3 VALUES  LESS  THEN (2000)

3、分区维护和重新组织（合并/删除）

随着时间的推移，你可能需要重新组织或合并分区以优化性能或管理空间。例如，合并两个分区：

ALTER TABLE employees_partitioned
MERGE PARTITIONS p1, p2 INTO PARTITION p_new;

或者，你可以删除不再需要的分区：

ALTER TABLE employees_partitioned
DROP PARTITION p_old;

在执行分区操作之前，确保有足够的备份和测试，因为错误的分区操作可能会导致数据丢失或查询性能下降。

分区类型（范围、列表、哈希等）和策略应该根据你的具体需求和数据特性来选择。例如，如果你知道某些列的唯一值非常多，那么使用列表分区可能更合适。

使用Oracle的自动分区功能（例如，自动范围分区）可以自动管理分区的创建，这可以减少手动干预的需要。

通过合理使用分区，你可以有效地管理和查询大型数据集，同时提高数据库的性能和可管理性。