oracle-分区表-list分区

Oracle List 分区详解

1. 什么是 List 分区？

List 分区是一种将表数据按照某个列的离散值进行分区的方法。与 Range 分区不同，List 分区不需要定义连续的范围，而是根据具体的值列表来决定数据应该存储在哪个分区中。这种方式非常适合那些具有固定类别或离散值的数据。

2. List 分区的适用场景

List 分区特别适用于以下场景：

• 地区数据：根据国家或地区的代码进行分区。
• 产品类别：根据产品的类别进行分区。
• 客户类型：根据客户的类型（如 VIP、普通客户等）进行分区。
• 业务类型：根据业务类型（如销售、采购、库存等）进行分区。

3. List 分区的创建方法

创建list分区的基本方法：

以下是一个创建 List 分区表的基本示例：

CREATE TABLE anshen.sales (
sale_id NUMBER PRIMARY KEY,
region VARCHAR2(10),
amount NUMBER,
sale_date DATE
)
PARTITION BY LIST (region)
(
PARTITION p_north VALUES ('NORTH') TABLESPACE tbs01,
PARTITION p_south VALUES ('SOUTH') TABLESPACE tbs01,
PARTITION p_east VALUES ('EAST') TABLESPACE tbs01,
PARTITION p_west VALUES ('WEST') TABLESPACE tbs01,
PARTITION p_default VALUES (DEFAULT) TABLESPACE tbs01
);

 

 

创建包含多个值的 List 分区

CREATE TABLE anshen.sales (
sale_id NUMBER PRIMARY KEY,
region VARCHAR2(10),
amount NUMBER,
sale_date DATE
)
PARTITION BY LIST (region)
(
PARTITION p_north_south VALUES ('NORTH', 'SOUTH'),
PARTITION p_east_west VALUES ('EAST', 'WEST')
);

创建包含默认分区的 List 分区

CREATE TABLE anshen.sales (
sale_id NUMBER PRIMARY KEY,
region VARCHAR2(10),
amount NUMBER,
sale_date DATE
)
PARTITION BY LIST (region)
(
PARTITION p_north VALUES ('NORTH'),
PARTITION p_south VALUES ('SOUTH'),
PARTITION p_east VALUES ('EAST'),
PARTITION p_west VALUES ('WEST'),
PARTITION p_default VALUES (DEFAULT)
);

4.创建本地索引，提高查询效率

本地索引是针对每个分区单独创建的索引，可以提高查询性能：

 

5. 创建全局索引

全局索引是跨所有分区的索引，适用于某些特定的查询场景：

 

6. 插入数据：

为了插入每分区 3000 条数据，我们可以使用 PL/SQL 块来生成数据：

BEGIN
FOR i IN 1..3000 LOOP
INSERT INTO anshen.sales (sale_id, region, amount, sale_date)
VALUES (i, 'NORTH', DBMS_RANDOM.VALUE(100, 10000), SYSDATE - DBMS_RANDOM.VALUE(1, 1000));
END LOOP;

FOR i IN 3001..6000 LOOP
INSERT INTO anshen.sales (sale_id, region, amount, sale_date)
VALUES (i, 'SOUTH', DBMS_RANDOM.VALUE(100, 10000), SYSDATE - DBMS_RANDOM.VALUE(1, 1000));
END LOOP;

FOR i IN 6001..9000 LOOP
INSERT INTO anshen.sales (sale_id, region, amount, sale_date)
VALUES (i, 'EAST', DBMS_RANDOM.VALUE(100, 10000), SYSDATE - DBMS_RANDOM.VALUE(1, 1000));
END LOOP;

FOR i IN 9001..12000 LOOP
INSERT INTO anshen.sales (sale_id, region, amount, sale_date)
VALUES (i, 'WEST', DBMS_RANDOM.VALUE(100, 10000), SYSDATE - DBMS_RANDOM.VALUE(1, 1000));
END LOOP;

FOR i IN 12001..15000 LOOP
INSERT INTO anshen.sales (sale_id, region, amount, sale_date)
VALUES (i, 'DEFAULT', DBMS_RANDOM.VALUE(100, 10000), SYSDATE - DBMS_RANDOM.VALUE(1, 1000));
END LOOP;

COMMIT;
END;
/

 

7.查看统计信息并收集

 

 收集表的统计信息

 

再次查看表的统计信息

8.List分区优缺点总结： 

List 分区的优点
查询性能：对于基于特定值的查询，List 分区可以显著提高查询性能，因为 Oracle 可以直接访问相关的分区，而不需要扫描整个表。
数据管理：可以独立地管理每个分区的数据，例如删除某个地区的所有数据、归档数据等。
数据插入：数据插入过程更高效，因为插入的数据会根据其值直接进入对应的分区。
List 分区的缺点
不适用于非离散值：List 分区最适合具有固定类别或离散值的数据，对于连续值的数据不适用。
分区值的管理：随着数据的增长，可能需要定期调整分区的值列表，特别是在业务发生变化时。