oracle-分区表-rang分区

Oracle 的 Range 分区（范围分区）解释
范围分区（Range Partitioning） 是一种根据列的值范围将数据分配到不同的分区中的分区类型。在 Oracle 数据库中，RANGE 分区允许将表数据基于某个列的范围（通常是数字或日期）进行分割。

范围分区的特性：
分区依据：

RANGE 分区是基于列值的范围进行分区的。可以选择一个字段（通常是数字型或日期型），将这个字段的值划分为多个区间，每个区间的数据放到一个独立的分区中。
分区边界（Partition Boundaries）：

每个分区都有一个上限，称为 分区上边界。数据的范围将根据分区的上边界来确定。Oracle 会根据该列值是否小于或等于指定的边界值来将数据分配到相应的分区。
例如，按日期进行范围分区时，可以指定每年一个分区，数据会根据日期自动分配到每个年份的分区中。
适用于区间化数据：

这种分区方式最适合数据有明确的区间或范围（如日期、数字等），每个区间（或时间段）包含的数据量相对稳定。例如，按年份、月份、季度等进行分区。
支持默认分区：

如果插入的数据超出了已定义的分区范围，Oracle 可以将这些数据插入到一个 默认分区 中。默认分区的设置适用于数据不符合任何预设分区边界的情况。
跨分区查询：

当执行查询时，Oracle 会根据查询条件智能地选择访问相应的分区，从而减少不必要的数据扫描。例如，如果查询条件中包含日期字段，Oracle 只会扫描匹配日期范围的分区，而不是扫描整个表。
数据加载和维护：

使用范围分区时，向表中插入数据更加高效，因为数据会直接插入到合适的分区中。还可以轻松地管理旧数据，例如通过 分区交换、分区删除 等操作来高效地处理大量数据。
分区间独立：

每个分区内部的数据是独立存储的。即使不同分区的数据使用相同的列名或数据类型，数据存储在物理上是分开的，可以独立地进行管理和优化。

一下是创建分区表示例：

创建用户、表空间、授权

 

 

 

 

-- 创建分区表，包含默认分区，并指定每个分区使用 tbs01 表空间

CREATE TABLE anshen.tab_p1 (
id NUMBER PRIMARY KEY,
name VARCHAR2(50),
created_at DATE,
status VARCHAR2(20),
amount NUMBER,
description VARCHAR2(200)
)
PARTITION BY RANGE (created_at)
(
PARTITION p_2020 VALUES LESS THAN (TO_DATE('2021-01-01', 'YYYY-MM-DD'))
TABLESPACE tbs01,
PARTITION p_2021 VALUES LESS THAN (TO_DATE('2022-01-01', 'YYYY-MM-DD'))
TABLESPACE tbs01,
PARTITION p_2022 VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD'))
TABLESPACE tbs01,
PARTITION p_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD'))
TABLESPACE tbs01,
PARTITION p_2024 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD'))
TABLESPACE tbs01,
PARTITION p_2025 VALUES LESS THAN (TO_DATE('2026-01-01', 'YYYY-MM-DD'))
TABLESPACE tbs01,
PARTITION p_2026 VALUES LESS THAN (TO_DATE('2027-01-01', 'YYYY-MM-DD'))
TABLESPACE tbs01,
PARTITION p_2027 VALUES LESS THAN (TO_DATE('2028-01-01', 'YYYY-MM-DD'))
TABLESPACE tbs01,
PARTITION p_2028 VALUES LESS THAN (TO_DATE('2029-01-01', 'YYYY-MM-DD'))
TABLESPACE tbs01,
PARTITION p_2029 VALUES LESS THAN (TO_DATE('2030-01-01', 'YYYY-MM-DD'))
TABLESPACE tbs01,
-- 默认分区处理超过所有范围的记录，指定表空间
PARTITION p_default VALUES LESS THAN (MAXVALUE)
TABLESPACE tbs01
);

 

 

-- 插入数据
BEGIN
FOR i IN 1..2000 LOOP
INSERT INTO anshen.tab_p1 (id, name, created_at, status, amount, description)
VALUES (i, 'Name ' || i, TO_DATE('2020-01-01', 'YYYY-MM-DD'), 'Active', i * 10, 'Description for row ' || i);
END LOOP;
COMMIT;
END;
/

-- 插入数据到默认分区
BEGIN
FOR i IN 2001..3000 LOOP
INSERT INTO anshen.tab_p1 (id, name, created_at, status, amount, description)
VALUES (i, 'Name ' || i, TO_DATE('2035-01-01', 'YYYY-MM-DD'), 'Inactive', i * 10, 'Description for row ' || i);
END LOOP;
COMMIT;
END;
/

 

 

使用 DBMS_STATS 收集统计信息：

 

查看列的选择性：

• num_distinct：列中不同值的数量。
• density：是列的选择性，值越低，选择性越高。

 

查看索引情况，看到ID列有个unique，所以已经不适合再创建一个全局索引。

 

 在其他的列上创建一个全局索引：

 

创建本地索引：可以使用 AUTO 选项来自动创建与表分区一致的索引分区。

 

查看分区表索引的情况

SELECT
t.owner AS table_owner,
t.table_name,
t.partitioned,
i.index_name,
i.index_type,
i.status AS index_status,
i.table_owner AS index_owner,
i.uniqueness AS index_uniqueness,
col.column_name AS index_column,
p.partition_name AS partition_name,
i.tablespace_name AS index_tablespace
FROM
all_tables t
JOIN all_indexes i ON t.owner = i.table_owner AND t.table_name = i.table_name
JOIN all_ind_columns col ON i.owner = col.index_owner AND i.index_name = col.index_name
LEFT JOIN all_tab_partitions p ON t.owner = p.table_owner AND t.table_name = p.table_name
WHERE
t.owner = 'ANSHEN' -- 替换为你的表所有者
AND t.table_name = 'TAB_P1' -- 替换为你的表名
ORDER BY
i.index_name, p.partition_name, col.column_name ;

 

 

 

Range 分区的适用范围：
时间数据：

最典型的应用场景是将数据按时间分区。例如，按天、月、季度或年进行分区。这对于日志数据、交易记录、历史数据等时间序列数据非常有用。
适用场景：日志表、订单表、财务数据表、历史记录表等。
数字区间：

适用于按数字区间划分数据的场景，例如，按价格、评分、销售额等数字字段划分数据。
适用场景：根据价格区间分区的产品表、根据评分区间分区的电影评论表等。
分区的精细化管理：

在某些情况下，可能希望根据数据的特定范围来划分数据，以便于管理。例如，某些年份的数据频繁访问，而其他年份的数据则很少访问。分区可以有助于管理这些数据。
适用场景：对于数据量较大且具有不同访问模式的表，可以通过范围分区来优化查询性能。
历史数据归档：

范围分区非常适用于历史数据的归档。可以方便地将老数据归档到一个单独的分区中，并根据需求进行删除或备份。
适用场景：历史数据表、过期数据管理等。
Range 分区的优缺点：
优点：
查询性能： 对于范围查询（例如查询某个日期区间的数据），范围分区能显著提高查询性能，因为 Oracle 可以跳过不匹配的分区。
数据管理： 可以独立地管理每个分区的数据，比如删除某个时间段的数据、归档数据等。
数据插入： 数据插入过程更高效，因为插入的数据会根据其范围直接进入对应的分区。
缺点：
不适合非顺序数据： 如果数据没有明显的范围，范围分区可能不适用。它最适合具有顺序和区间性质的数据。
分区边界的管理： 随着数据增长，可能需要调整分区的边界或添加新的分区，特别是在时间范围分区中，可能需要根据数据的分布调整分区的定义。
总结：
范围分区（Range Partitioning） 是将数据按列的值范围划分到不同的分区中。它特别适用于有明确区间或顺序的数据，如时间数据（日期、月份、年份等）或数字数据。通过这种分区方式，Oracle 提供了更高效的查询性能和更简便的数据管理能力。