数据库中的分区是将表或索引按照一定规则分割成多个小分区进行管理的技术。分区可以提高查询效率和数据存储的可靠性。常见的分区方式有范围分区、哈希分区、列表分区等。
以下是一个范围分区的示例代码:
创建表并进行分区:
CREATE TABLE sales
(
sale_id NUMBER(10),
sale_date DATE,
sale_amount NUMBER(10)
)
PARTITION BY RANGE (sale_date)
(
PARTITION sales_q1 VALUES LESS THAN (TO_DATE("01-APR-2020","DD-MON-YYYY")),
PARTITION sales_q2 VALUES LESS THAN (TO_DATE("01-JUL-2020","DD-MON-YYYY")),
PARTITION sales_q3 VALUES LESS THAN (TO_DATE("01-OCT-2020","DD-MON-YYYY")),
PARTITION sales_q4 VALUES LESS THAN (TO_DATE("01-JAN-2021","DD-MON-YYYY"))
);
向表中插入数据:
INSERT INTO sales VALUES (1, TO_DATE("01-JAN-2020","DD-MON-YYYY"), 100);
INSERT INTO sales VALUES (2, TO_DATE("15-FEB-2020","DD-MON-YYYY"), 200);
INSERT INTO sales VALUES (3, TO_DATE("20-MAR-2020","DD-MON-YYYY"), 300);
INSERT INTO sales VALUES (4, TO_DATE("05-MAY-2020","DD-MON-YYYY"), 400);
INSERT INTO sales VALUES (5, TO_DATE("25-JUN-2020","DD-MON-YYYY"), 500);
INSERT INTO sales VALUES (6, TO_DATE("10-AUG-2020","DD-MON-YYYY"), 600);
INSERT INTO sales VALUES (7, TO_DATE("30-SEP-2020","DD-MON-YYYY"), 700);
INSERT INTO sales VALUES (8, TO_DATE("15-NOV-2020","DD-MON-YYYY"), 800);
INSERT INTO sales VALUES (9, TO_DATE("25-DEC-2020","DD-MON-YYYY"), 900);
查询分区:
SELECT partition_name, high_value
FROM user_tab_partitions
WHERE table_name = "SALES";
输出:
PARTITION_NAME HIGH_VALUE
-------------- -----------------------------
SALES_Q1 TO_DATE(" 2020-04-01 00:00:00", "SYYYY-MM-DD HH24:MI:SS", "NLS_CALENDAR=GREGORIAN")
SALES_Q2 TO_DATE(" 2020-07-01 00:00:00", "SYYYY-MM-DD HH24:MI:SS", "NLS_CALENDAR=GREGORIAN")
SALES_Q3 TO_DATE(" 2020-10-01 00:00:00", "SYYYY-MM-DD HH24:MI:SS", "NLS_CALENDAR=GREGORIAN")
SALES_Q4 TO_DATE(" 2021-01-01 00:00:00", "SYYYY-MM-DD HH24:MI:SS", "NLS_CALENDAR=GREGORIAN")
可以看到,根据销售日期分区后,表被分成了四个区间:Q1、Q2、Q3、Q4。可以通过查询分区元数据的方式来了解表的分区情况。