【从零单排】Oracle Table Partition总结

什么是数据库表分区

数据库表分区（Partitioning），即将一个大的数据表（Table）及其索引（Index）切分更小的部分。这些分区可以有不同的名字，甚至是存储方式。

Oracle Partitioning Overview

为什么要做分区

• 存储均衡：可以分摊大量数据到不同存储介质中。
• 方便管理：方便DBA管理数据表，进行各种操作，比如删除陈旧的数据。
• 直接定位查询快：根据分区策略查询数据时，可直接定位到目标分区，减少查询时间。
• 并行查询提高效率：做聚合查询时，多个分区（磁盘）并行查询，可以提高效率。

怎么做分区

分区策略

• Range：分区规则是一段段连续区间。
• List：分区规则是一个个List。
• Hash：给定分区数目，将元素均匀分配到各个分区。

分区方式

• Single-Level Partitioning（单级分区）：只有一层分区。
• Composite Partitioning（多级分区）：可以有多层分区，即在分区之上，再次进行分区（Sub-Partition）。

数据表分区策略及实现

分区实例

Range Partitioning

CREATE TABLE sales_range 
(salesman_id  NUMBER(5), 
salesman_name VARCHAR2(30), 
sales_amount  NUMBER(10), 
sales_date    DATE)
PARTITION BY RANGE(sales_date) 
(
PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','MM/DD/YYYY')),
PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','MM/DD/YYYY')),
PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','MM/DD/YYYY')),
PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','MM/DD/YYYY'))
);

List Partitioning

CREATE TABLE sales_list
(salesman_id  NUMBER(5), 
salesman_name VARCHAR2(30),
sales_state   VARCHAR2(20),
sales_amount  NUMBER(10), 
sales_date    DATE)
PARTITION BY LIST(sales_state)
(
PARTITION sales_west VALUES('California', 'Hawaii'),
PARTITION sales_east VALUES ('New York', 'Virginia', 'Florida'),
PARTITION sales_central VALUES('Texas', 'Illinois'),
PARTITION sales_other VALUES(DEFAULT)
);

Hash Partitioning

CREATE TABLE sales_hash
(salesman_id  NUMBER(5), 
salesman_name VARCHAR2(30), 
sales_amount  NUMBER(10), 
week_no       NUMBER(2)) 
PARTITION BY HASH(salesman_id) 
PARTITIONS 4 
STORE IN (ts1, ts2, ts3, ts4);

删除分区

要想删除分区，可以使用如下SQL：

ALTER TABLE table-name DROP PARTITION partition-name;

进一步，可以写一个函数来删除数据表陈旧的数据，如下：

-- delete the partition older than INPUT_DAYS days (exclusive) in INPUT_TABLE
create or replace procedure do_delete_old_partition
(
	INPUT_TABLE IN VARCHAR,
	INPUT_DAYS IN NUMBER
) AS
BEGIN
    FOR P IN (SELECT PARTITION_NAME, HIGH_VALUE FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = INPUT_TABLE) 
    LOOP
        EXECUTE IMMEDIATE
          'BEGIN
            IF TO_DATE(' || P.HIGH_VALUE || ',''YYYYMMDD'') <= (SYSDATE-' || INPUT_DAYS || ') THEN
              EXECUTE IMMEDIATE
                ''ALTER TABLE ' || INPUT_TABLE || ' DROP PARTITION ' || P.PARTITION_NAME || ''';
            END IF;
          END;';
    END LOOP;
END;
/

切换 Tablespace

有的时候，需要将partition迁移到新的tablespace上，比如将不常用的分区移走，加快访问速度。

ALTER TABLE TABLE_NAME 
MOVE PARTITION PARTITION_NAME 
TABLESPACE TABLESPACE_NAME;

A schema is a namespace - a logical thing. It is used to organize the names of database objects. It has nothing to do with the way the data is stored.
A tablespace is a physical thing. It's a container for data and has nothing to do with the logical organization of the database objects.

• ALTER TABLE… MOVE PARTITION

切换 Table

有的时候，还需要将陈旧的数据迁移到一个新的表，比如TABLE_NAME_OLD_1001。
这里用到了EXCHANGE PARTITION。

CREATE TABLE TMP_TABLE(SAME COLUMNS);
ADD PARTITION P_1001 IN TABLE ARCH_TABLE;

ALTER TABLE CURR_TABLE EXCHANGE PARTITION P_1001 WITH TABLE TMP_TABLE;
ALTER TABLE ARCH_TABLE EXCHANGE PARTITION P_1001 WITH TABLE TMP_TABLE;

• how-to-detach-a-partition-from-a-table-and-attach-it-to-another-in-oracle

上线策略

停机升级

这种整表层面的改动，如果可以的话，最简单的办法是将应用停掉（比如，凌晨1点到6点，进行系统升级，停止用户访问），然后进行操作。

不停机升级

但是对于互联网应用，是无法接受停机升级的。这就需要采取新的方法。
一种操作是，对于原读写数据库X，专门写一个模块A，当作写入时的临时数据库；另外，备份一个现有数据库B，当作读数据库。这两个模块，合起来A+B=X。
然后就可以对X进行升级，而期间用A+B临时代替X。当升级完成后，再把A的数据写回到X'里。