Oracle表分区详解

Oracle表的分区详解

什么是表分区？

表分区是将表按照一定的规则，分成多个逻辑部分，每个部分称之为一个分区。

它是 Oracle 数据库中非常重要的功能，它可以极大的提高数据库的性能、可靠性和管理效率。

表分区的优点

使用表分区有以下几个优点：

1. 提高查询性能。 在查询时，如果能够排除掉一些数据分区，就会大幅度提高查询效率，从而缩短查询时间。
2. 提高数据文件的可靠性。 可以减小数据丢失的风险，因为如果某个分区的数据出现故障，不会影响到整个表的数据。
3. 提高数据的可维护性。 能够更加容易、便利地对表进行管理操作，比如备份、恢复、重建、加密等操作。

如何创建表分区？

创建分区表时，需要使用到 PARTITION BY 子句

CREATE TABLE emp (
    empno NUMBER PRIMARY KEY,
    ename VARCHAR2(10),
    job VARCHAR2(9),
    hiredate DATE,
    sal NUMBER,
    comm NUMBER,
    deptno NUMBER
)
PARTITION BY RANGE (hiredate) (
    PARTITION p_2018 VALUES LESS THAN (TO_DATE('2019-01-01', 'YYYY-MM-DD')),
    PARTITION p_2019 VALUES LESS THAN (TO_DATE('2020-01-01', 'YYYY-MM-DD')),
    PARTITION p_2020 VALUES LESS THAN (TO_DATE('2021-01-01', 'YYYY-MM-DD')),
    PARTITION p_2021 VALUES LESS THAN (MAXVALUE)
)

在上面的语句中，通过 PARTITION BY RANGE (hiredate) 告诉 Oracle 使用 “员工入职时间” 这个字段作为分区键；然后通过 PARTITION...VALUES LESS THAN 的语法，指定了四个分区，其中 p_2018 包含了 hiredate 小于 2019 年 1 月 1 日的数据，以此类推。

如何添加分区？

添加分区需要使用 ALTER TABLE 命令：

ALTER TABLE emp ADD PARTITION p_year_2022 VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD'));

在上面的命令中，我们在 emp 表上添加了一个名为 p_year_2022 的分区，包含 hiredate 小于 2023 年 1 月 1 日的数据。

示例1

接下来，我们用一个实际的例子来说明如何使用分区表提高查询性能。

我们创建一个 sales 表，如下所示：

CREATE TABLE sales (
    id NUMBER,
    sale_date DATE,
    amount NUMBER
)
PARTITION BY RANGE (sale_date) (
    PARTITION sales_2019 VALUES LESS THAN (TO_DATE('2020-01-01', 'YYYY-MM-DD')),
    PARTITION sales_2020 VALUES LESS THAN (TO_DATE('2021-01-01', 'YYYY-MM-DD')),
    PARTITION sales_2021 VALUES LESS THAN (TO_DATE('2022-01-01', 'YYYY-MM-DD')),
    PARTITION sales_2022 VALUES LESS THAN (MAXVALUE)
);

接下来往表中插入大量数据，然后我们测试一下查询语句的性能：

SELECT sum(amount) FROM sales WHERE sale_date BETWEEN TO_DATE('2019-01-01','YYYY-MM-DD') AND TO_DATE('2020-01-01','YYYY-MM-DD');

查询结果：

Execution Plan
----------------------------------------------------------
Plan hash value: 3041799129

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    14 |     2   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE             |       |     1 |    14 |            |          |       |       |
|   2 |   PARTITION RANGE ALL       |       |  1097K|    14M|     2   (0)| 00:00:01 |     1 |     4 |
|*  3 |    TABLE ACCESS STORAGE FULL| SALES |  1097K|    14M|     2   (0)| 00:00:01 |     1 |     4 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - storage("SALE_DATE">=TO_DATE(' 2019-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "SALE_DATE"<TO_DATE(' 2020-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

可以看到，Oracle 使用了全部的 4 个分区来执行该查询，包括 p_sales_2019、p_sales_2020、p_sales_2021 和 p_sales_2022。

示例2

我们在上面的例子上再加一个例子，这次我们增加一个指定分区数量的示例，如下所示：

CREATE TABLE employee (
    id NUMBER, 
    name VARCHAR2(256), 
    birthdate DATE
)
PARTITION BY RANGE (birthdate) INTERVAL (NUMTOYMINTERVAL(1,'YEAR'))
(PARTITION P_OLD VALUES LESS THAN (TO_DATE('2015-01-01','YYYY-MM-DD')));

在这个例子中，我们创建了一个 employee 表，并将其分成两个分区，其中 P_OLD 包含 birthdate 小于 2015 年 1 月 1 日的数据，而第二个分区则是当前年份。

当我们向表中插入数据时，Oracle 会自动为我们创建新分区。

INSERT INTO employee (id, name, birthdate) VALUES 
(1, 'Alice', TO_DATE('1980-05-05','YYYY-MM-DD')),
(2, 'Bob', TO_DATE('1990-09-01','YYYY-MM-DD')),
(3, 'Charlie', TO_DATE('1995-12-01','YYYY-MM-DD')),
(4, 'David', TO_DATE('2000-11-05','YYYY-MM-DD')),
(5, 'Eve', TO_DATE('2010-10-01','YYYY-MM-DD')),
(6, 'Frank', TO_DATE('2020-09-01','YYYY-MM-DD'));

然后我们可以看到 Oracle 为我们自动创建了新分区：

SELECT partition_name, high_value 
FROM user_tab_partitions 
WHERE table_name='EMPLOYEE';

PARTITION_NAME HIGH_VALUE
-------------- --------------------------------------------------
P_OLD          TO_DATE(' 2015-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
P_OLD_1_2022   TO_DATE(' 2023-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')

在上面的查询结果中，我们可以看到 Oracle 自动为我们创建了一个名为 "P_OLD_1_2022" 的分区，要注意的是，这个分区从 2016 年开始，直到当前年份。

因此，当我们执行查询语句时，Oracle 只会扫描需要的分区，从而提高了查询速度：

SELECT * FROM employee WHERE birthdate >= TO_DATE('2000-01-01','YYYY-MM-DD');

查询结果：

Execution Plan
----------------------------------------------------------
Plan hash value: 2778217284

--------------------------------------------------------------------------------------------
| Id  | Operation                | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                |     1 |    34 |     2   (0)| 00:00:01 |
|   1 |  PARTITION RANGE ITERATOR|                |     1 |    34 |     2   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL      | EMPLOYEE       |     1 |    34 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("BIRTHDATE">=TO_DATE(' 2000-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

我们可以看到 Oracle 只扫描了包含 birthdate 大于等于 2000 年 1 月 1 日的数据，即 P_OLD_1_2022 分区。