Oracle 19C学习 - 23. 分区表

Oracle默认表结构

Oracle数据库默认的表类型是堆表。堆表就是将所有的数据按照表的结构和约束统一保存到表段所对应的数据文件。

Oracle的分区

将一个大表按照一定的规则划分为很多物理上的小表，而逻辑上仍然是一个大表，对应用是透明的。

分区技术是针对表、索引组织表和索引的技术。

OLTP可以利用分区提高管理型和可用性。
OLAP可以利用分区提高性能及可管理性。

使用分区的情况：

  • 表的大小超过2G.
  • 历史数据和新增数据进行分离。
  • 表中数据分别存储在不同设备，将需要性能好的数据放在IO高的设备上。

分区的特点：

  • 大数据对象(表、索引)分成小的物理段。
  • 当分区表建立时，数据记录基于分区字段值被存储到相应地分区。
  • 分区字段值可以修改。
  • 分区可以存储在不同的表空间。
  • 分区可以有不同的物理存储参数。
  • 分区支持IOT表、对象表、LOB字段、Varrays等。

分区的优势

性能

  1. SELECT和DML操作只访问指定的分区。
  2. 并行的DML操作。
  3. Partition-Wise Join

可管理性

  1. 历史数据清除。
  2. 体高备份性能。
  3. 对指定分区进行数据维护操作。

可用性

  1. 将故障限定在分区中。
  2. 缩短恢复时间。

分区表的类型

主要类型

  1. 范围分区Range Partition
  2. 哈希分区 Hash Partition
  3. 列表分区 List Partition
  4. 复合分区 Composite Partition

次要类型

  1. 间隔分区
  2. 参考分区
  3. 虚拟字段分区
  4. 系统分区

范围分区 Range

范围分区的应用范围比较广，它是以列值的范围作为分区的条件。需要指定分区的基础列和分区的范围值，如果暂时无法预测范围，可以指定maxvalue分区，所有不在指定范围内的记录存储到maxvalue分区。

范围分区特别适合按照时间进行数据的存储。

数据管理能力较强：

  • 数据迁移
  • 数据备份
  • 数据交换

范围分区的数据可能不均匀。
范围分区与记录的值相关，实施难度和可维护性较差。

CREATE TABLE sales_parts
(
  order_no number,
  order_header varchar2(40),
  order_price number,
  order_date date
)
PARTITION BY RANGE(order_date)
(
  PARTITION p2022 VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD')),
  PARTITION P2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')),
  PARTITION p2024 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD')),
  PARTITION maxvalue VALUES LESS THAN (MAXVALUE)
);

哈希分区 HASH

对于无法有效划分范围的表，可以使用hash分区，对于提升效能还是有帮助的。
哈希分区会将表中的数据平均分配到几个分区中，依据分区的列的哈希值自动分区，不知道也不能控制数据放到那个分区中。

根据分区字段的哈希值，自动将记录插入到相应的分区中。
分区数量一般是2的幂。
总体性能最佳。
适合静态数据。
适合于数据的均匀存储。
支持Hash local index。
哈希分区对数据值无法控制数据。

CREATE TABLE customer
(
  id number,
  name varchar2(50)
)
PARTITION BY HASH(id)
(
  PARTITION cn_hash_p01 TABLESPACE customer_p01,
  PARTITION cn_hash_p02 TABLESPACE customer_p02,
  PARTITION cn_hash_p03 TABLESPACE customer_p03,
  PARTITION cn_hash_p04 TABLESPACE customer_p04
);

列表分区 List

分区列只有一个，必须明确指定分区值。单个分区可以对应多个分区值。
如果插入、更新记录的列值不在列表分区内，就会失败，因此需要创建一个default分区存储不在范围内的记录。

列表分区根据分区的离散值进行分区。
列表分区适合对数据离散值进行控制。
列表分区的数据分布不均匀。
List分区与记录值相关，实施难度与可维护性较差。

CREATE TABLE sales_list (
  Salesman_id number,
  Salesman_name varchar2(40),
  salesman_productivity number,
  salesman_province varchar2(40)
)
PARTITION BY LIST(Salesman_province)
(
  PARTITION northen_china VALUES ('Beijing', 'Qinhuangdao'),
  PARTITION middle_china VALUES ('Wuhan'),
  PARTITION southern_china VALUES ('Guangzhou', 'Dongguan'),
  PARTITION other_china VALUES (DEFAULT)
);

复合分区 Composite

如果某表按照某列分区之后仍然较大，可以通过分区内在建子分区，成为复合分区。
复合分区可以更好的控制数据粒度。

支持的复合分区类型：

  • List - List
  • List - Range
  • List - Hash
  • Range - Range
  • Range - List
  • Range - Hash

既适合历史数据，又适合数据均匀分布。
与范围分区一样提供高的可用性和管理型。
更好的PDML和Partition-wise join功能。
实现颗粒度更细的操作。
支持复合的local indexes.

CREATE TABLE orders
(
  order_no number,
  product_id number,
  order_date date
)
PARTITION BY RANGE(order_date) SUBPARTITION BY HASH(product_id)
(
  PARTITION p2022q1 VALUES LESS THAN (TO_DATE('2022-04-01', 'YYYY-MM-DD')),
  PARTITION p2022q2 VALUES LESS THAN (TO_DATE('2022-08-01', 'YYYY-MM-DD')),
  PARTITION p2022q3 VALUES LESS THAN (TO_DATE('2022-10-01', 'YYYY-MM-DD')),
  PARTITION p2022q4 VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD')),
  PARTITION maxvalue VALUES LESS THAN (MAXVALUE)
);

posted on 2022-11-13 21:31  LeoZhangJing  阅读(499)  评论(0)  编辑  收藏  举报

导航