在知识库中专注阅读，并随时返回相关工具与课程
听说金仓 V9R1C10 兼容了很多 Oracle 的特性，正好最近在做一些分区表的数据归档，测试一下金仓的 Oracle 兼容模式的分区表功能。 创建一个范围分区表： sql test= show databasemode; databasemode --------------- oracle test= CREATE TABLE salesrange saleid NU
本章目标：掌握本章核心知识点 前置要求：完成前序章节学习 预计时长：60 分钟
听说金仓 V9R1C10 兼容了很多 Oracle 的特性，正好最近在做一些分区表的数据归档，测试一下金仓的 Oracle 兼容模式的分区表功能。
创建一个范围分区表：
1test=# show database_mode; 2 database_mode 3---------------4 oracle56test=# CREATE TABLE sales_range (7 sale_id NUMBER,8 product_name VARCHAR2(100),9 sale_date DATE,10 amount NUMBER(10,2),11 region VARCHAR2(50)12)13PARTITION BY RANGE (sale_date) (14 PARTITION p_2023_q1 VALUES LESS THAN (TO_DATE('2023-04-01', 'YYYY-MM-DD')),15 PARTITION p_2023_q2 VALUES LESS THAN (TO_DATE('2023-07-01', 'YYYY-MM-DD')),16 PARTITION p_2023_q3 VALUES LESS THAN (TO_DATE('2023-10-01', 'YYYY-MM-DD')),17 PARTITION p_2023_q4 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')),18 PARTITION p_2024 VALUES LESS THAN (MAXVALUE)19);20CREATE TABLE
插入测试数据到不同分区：
1test=# INSERT INTO sales_range VALUES (1, 'Laptop', TO_DATE('2023-02-15', 'YYYY-MM-DD'), 1200.50, 'North');2INSERT 0 13test=# INSERT INTO sales_range VALUES (2, 'Mouse', TO_DATE('2023-05-20', 'YYYY-MM-DD'), 25.99, 'South');4INSERT 0 15test=# INSERT INTO sales_range VALUES (3, 'Keyboard', TO_DATE('2023-08-10', 'YYYY-MM-DD'), 80.00, 'East');6INSERT 0 17test=# INSERT INTO sales_range VALUES (4, 'Monitor', TO_DATE('2023-11-05', 'YYYY-MM-DD'), 350.00, 'West');8INSERT 0 19test=# INSERT INTO sales_range VALUES (5, 'Tablet', TO_DATE('2024-03-15', 'YYYY-MM-DD'), 499.99, 'North');10INSERT 0 1
验证分区数据分布：
1test=# SELECT 'p_2023_q1' AS partition_name, COUNT(*) FROM sales_range PARTITION (p_2023_q1)2UNION ALL3SELECT 'p_2023_q2', COUNT(*) FROM sales_range PARTITION (p_2023_q2)4UNION ALL5SELECT 'p_2023_q3', COUNT(*) FROM sales_range PARTITION (p_2023_q3)6UNION ALL7SELECT 'p_2023_q4', COUNT(*) FROM sales_range PARTITION (p_2023_q4)8UNION ALL9SELECT 'p_2024', COUNT(*) FROM sales_range PARTITION (p_2024);1011 partition_name | count 12----------------+-------13 p_2023_q1 | 114 p_2023_q2 | 115 p_2023_q3 | 116 p_2023_q4 | 117 p_2024 | 1
查看分区数据：
1EXPLAIN SELECT * FROM sales_range WHERE sale_date BETWEEN TO_DATE('2023-05-01', 'YYYY-MM-DD')2 AND TO_DATE('2023-06-30', 'YYYY-MM-DD');34SELECT * FROM sales_range WHERE sale_date BETWEEN TO_DATE('2023-05-01', 'YYYY-MM-DD')5 AND TO_DATE('2023-06-30', 'YYYY-MM-DD');
创建一个 LIST 分区表：
1test=# CREATE TABLE sales_list (2 sale_id NUMBER,3 product_name VARCHAR2(100),4 amount NUMBER(10,2),5 region VARCHAR2(50)6)7PARTITION BY LIST (region) (8 PARTITION p_north VALUES ('North', 'Northeast', 'Northwest'),9 PARTITION p_south VALUES ('South', 'Southeast', 'Southwest'),10 PARTITION p_east VALUES ('East'),11 PARTITION p_west VALUES ('West'),12 PARTITION p_default VALUES (DEFAULT)13);14CREATE TABLE
插入数据：
1test=# INSERT INTO sales_list VALUES (1, 'Product A', 100.00, 'North');2INSERT 0 13test=# INSERT INTO sales_list VALUES (2, 'Product B', 200.00, 'South');4INSERT 0 15test=# INSERT INTO sales_list VALUES (3, 'Product C', 150.00, 'East');6INSERT 0 17test=# INSERT INTO sales_list VALUES (4, 'Product D', 180.00, 'West');8INSERT 0 19test=# INSERT INTO sales_list VALUES (5, 'Product E', 220.00, 'Central');10INSERT 0 111test=# INSERT INTO sales_list VALUES (6, 'Product F', 90.00, 'Northeast');12INSERT 0 1
验证分区数据：
1test=# SELECT * FROM sales_list PARTITION (p_north);2 sale_id | product_name | amount | region 3---------+--------------+--------+-----------4 1 | Product A | 100.00 | North5 6 | Product F | 90.00 | Northeast67test=# SELECT * FROM sales_list PARTITION (p_default);8 sale_id | product_name | amount | region 9---------+--------------+--------+---------10 5 | Product E | 220.00 | Central
创建一个哈希分区：
1test=# CREATE TABLE customer_hash (2 customer_id NUMBER PRIMARY KEY,3 customer_name VARCHAR2(100),4 email VARCHAR2(100),5 phone VARCHAR2(20)6)7PARTITION BY HASH (customer_id) (8 PARTITION p1,9 PARTITION p2,10 PARTITION p3,11 PARTITION p412);13CREATE TABLE
插入数据（会自动根据哈希算法分配到不同分区）：
1test=# INSERT INTO customer_hash2SELECT3 i,4 'Customer_' || i,5 'customer' || i || '@example.com',6 '1234567' || LPAD(i::TEXT, 3, '0')7FROM generate_series(1, 20) AS i;8INSERT 0 20
查看各分区数据分布：
1test=# SELECT 'p1' AS partition_name, COUNT(*) FROM customer_hash PARTITION (p1)2UNION ALL3SELECT 'p2', COUNT(*) FROM customer_hash PARTITION (p2)4UNION ALL5SELECT 'p3', COUNT(*) FROM customer_hash PARTITION (p3)6UNION ALL7SELECT 'p4', COUNT(*) FROM customer_hash PARTITION (p4);89 partition_name | count 10----------------+-------11 p1 | 512 p2 | 613 p3 | 214 p4 | 7
验证分区数据：
1test=# SELECT * FROM customer_hash PARTITION (p1);2 customer_id | customer_name | email | phone 3-------------+---------------+------------------------+------------4 4 | Customer_4 | customer4@example.com | 12345670045 7 | Customer_7 | customer7@example.com | 12345670076 10 | Customer_10 | customer10@example.com | 12345670107 12 | Customer_12 | customer12@example.com | 12345670128 16 | Customer_16 | customer16@example.com | 1234567016910test=# SELECT * FROM customer_hash PARTITION (p4);11 customer_id | customer_name | email | phone 12-------------+---------------+------------------------+------------13 2 | Customer_2 | customer2@example.com | 123456700214 6 | Customer_6 | customer6@example.com | 123456700615 11 | Customer_11 | customer11@example.com | 123456701116 13 | Customer_13 | customer13@example.com | 123456701317 17 | Customer_17 | customer17@example.com | 123456701718 18 | Customer_18 | customer18@example.com | 123456701819 19 | Customer_19 | customer19@example.com | 1234567019
创建一个间隔分区：
1-- 每月自动创建新分区2test=# CREATE TABLE logs_interval (3 log_id NUMBER,4 log_date DATE,5 log_level VARCHAR2(20),6 message VARCHAR2(500)7)8PARTITION BY RANGE (log_date)9INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))10(11 PARTITION p_start VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD'))12);13CREATE TABLE
插入不同月份的数据，观察自动分区创建：
1test=# INSERT INTO logs_interval VALUES (1, TO_DATE('2023-12-20', 'YYYY-MM-DD'), 'INFO', 'Old log entry');2INSERT 0 13test=# INSERT INTO logs_interval VALUES (2, TO_DATE('2024-01-15', 'YYYY-MM-DD'), 'ERROR', 'Jan 2024 log');4INSERT 0 15test=# INSERT INTO logs_interval VALUES (3, TO_DATE('2024-02-10', 'YYYY-MM-DD'), 'WARN', 'Feb 2024 log');6INSERT 0 17test=# INSERT INTO logs_interval VALUES (4, TO_DATE('2024-03-05', 'YYYY-MM-DD'), 'INFO', 'Mar 2024 log');8INSERT 0 19test=# INSERT INTO logs_interval VALUES (5, TO_DATE('2024-06-20', 'YYYY-MM-DD'), 'DEBUG', 'Jun 2024 log');10INSERT 0 111test=# INSERT INTO logs_interval VALUES (6, TO_DATE('2024-10-15', 'YYYY-MM-DD'), 'ERROR', 'Oct 2024 log');12INSERT 0 1
查询所有数据（应该能看到自动创建的分区）：
1test=# SELECT * FROM logs_interval ORDER BY log_date;2 log_id | log_date | log_level | message 3--------+---------------------+-----------+---------------4 1 | 2023-12-20 00:00:00 | INFO | Old log entry5 2 | 2024-01-15 00:00:00 | ERROR | Jan 2024 log6 3 | 2024-02-10 00:00:00 | WARN | Feb 2024 log7 4 | 2024-03-05 00:00:00 | INFO | Mar 2024 log8 5 | 2024-06-20 00:00:00 | DEBUG | Jun 2024 log9 6 | 2024-10-15 00:00:00 | ERROR | Oct 2024 log
查看分区定义：
1test=# SELECT partition_name, high_value FROM user_tab_partitions WHERE table_name = 'LOGS_INTERVAL';2 partition_name | high_value 3----------------+-------------------------4 P_START | ('2024-01-01 00:00:00')5 P1 | ('2024-02-01 00:00:00')6 P2 | ('2024-03-01 00:00:00')7 P3 | ('2024-04-01 00:00:00')8 P4 | ('2024-07-01 00:00:00')9 P5 | ('2024-11-01 00:00:00')
在特定分区插入数据：
1test=# INSERT INTO sales_range PARTITION (p_2023_q3)2VALUES (6, 'Headset', TO_DATE('2023-09-15', 'YYYY-MM-DD'), 120.00, 'Central');3INSERT 0 1
更新特定分区数据：
1test=# UPDATE sales_range PARTITION (p_2023_q1)2SET amount = amount * 1.13WHERE region = 'North';
删除特定分区数据：
1test=# select * FROM sales_range PARTITION (p_2024);2 sale_id | product_name | sale_date | amount | region 3---------+--------------+---------------------+--------+--------4 5 | Tablet | 2024-03-15 00:00:00 | 499.99 | North5(1 行记录)67test=# DELETE FROM sales_range PARTITION (p_2024) WHERE amount < 100;8DELETE 09test=# DELETE FROM sales_range PARTITION (p_2024) WHERE amount > 100;10DELETE 111test=# select * FROM sales_range PARTITION (p_2024); 12 sale_id | product_name | sale_date | amount | region 13---------+--------------+-----------+--------+--------14(0 行记录)
TRUNCATE 特定分区：
1test=# select * FROM sales_range PARTITION (P_2023_q3);2 sale_id | product_name | sale_date | amount | region 3---------+--------------+---------------------+--------+---------4 3 | Keyboard | 2023-08-10 00:00:00 | 80.00 | East5 6 | Headset | 2023-09-15 00:00:00 | 120.00 | Central6(2 行记录)78test=# ALTER TABLE sales_range TRUNCATE PARTITION p_2023_q3;9ALTER TABLE10test=# select * FROM sales_range PARTITION (P_2023_q3); 11 sale_id | product_name | sale_date | amount | region 12---------+--------------+-----------+--------+--------13(0 行记录)
添加新分区：
1-- 删除 MAXVALUE 分区2test=# ALTER TABLE sales_range DETACH PARTITION sales_range_p_2024;3ALTER TABLE45-- 添加 2025 年分区6test=# ALTER TABLE sales_range ADD PARTITION p_2025 VALUES LESS THAN (TO_DATE('2026-01-01', 'YYYY-MM-DD'));7ALTER TABLE89-- 重新附加或创建 MAXVALUE 分区10test=# CREATE TABLE sales_range_p_future PARTITION OF sales_range FOR VALUES FROM ('2026-01-01') TO (MAXVALUE);11CREATE TABLE
删除分区：
1test=# ALTER TABLE sales_range DROP PARTITION p_2025;2ALTER TABLE拆分分区：
1test=# ALTER TABLE sales_range SPLIT PARTITION p_2023_q12AT ('2023-02-01')3INTO (PARTITION p_2023_h1, PARTITION p_2023_h2);4ALTER TABLE
合并分区：
1test=# ALTER TABLE sales_range MERGE PARTITIONS p_2023_h1, p_2023_h22INTO PARTITION p_2023_q1;
重命名分区：
1ALTER TABLE sales_range RENAME PARTITION p_2024_all TO p_2024;交换分区：
1CREATE TABLE temp_sales (2 sale_id NUMBER,3 product_name VARCHAR2(100),4 sale_date DATE,5 amount NUMBER(10,2),6 region VARCHAR2(50)7);89INSERT INTO temp_sales VALUES (100, 'Test Product', TO_DATE('2023-03-10', 'YYYY-MM-DD'), 999.99, 'Test');将普通表数据交换到分区：
1ALTER TABLE sales_range EXCHANGE PARTITION p_2023_q1 WITH TABLE temp_sales;23SELECT * FROM sales_range PARTITION (p_2023_q1);4SELECT * FROM temp_sales;56-- 再交换回来7ALTER TABLE sales_range EXCHANGE PARTITION p_2023_q1 WITH TABLE temp_sales;关于分区表常用的一些分区类型以及功能特性，均已在金仓 V9R1C10 版本的 Oracle 兼容模式下测试，并且全部支持，点赞👍🏻！