【讨论贴】Oracle 分区表知识大讨论（来者有分）

[江南小鱼]

有关oracle数据库，畅所欲言，来者有分，请同仁们不吝赐教。

先抛出一个问题：
1、一般按什么规则来分区，有没有创建分区的脚本？
2、分区表有什么优势和劣势，以及对备份及数据库维护啥的，有木有需要注意的地方？

[止水i]

可能以后会用得到对于大于2GB的一个表就可以用的上了
(4).表分区的几种类型及操作方法
一.范围分区：
范围分区将数据基于范围映射到每一个分区，这个范围是你在创建分区时指定的分区键决定的。这种分区方式是最为常用的，并且分区键经常采用日期。举个例子：你可能会将销售数据按照月份进行分区。
当使用范围分区时，请考虑以下几个规则：
1、每一个分区都必须有一个VALUES LESS THAN子句，它指定了一个不包括在该分区中的上限值。分区键的任何值等于或者大于这个上限值的记录都会被加入到下一个高一些的分区中。
2、所有分区，除了第一个，都会有一个隐式的下限值，这个值就是此分区的前一个分区的上限值。
3、在最高的分区中，MAXVALUE被定义。MAXVALUE代表了一个不确定的值。这个值高于其它分区中的任何分区键的值，也可以理解为高于任何分区中指定的VALUE LESS THAN的值，同时包括空值。
例一：
假设有一个CUSTOMER表，表中有数据200000行，我们将此表通过CUSTOMER_ID进行分区，每个分区存储100000行，我们将每个分区保存到单独的表空间中，这样数据文件就可以跨越多个物理磁盘。下面是创建表和分区的代码，如下：
CREATE TABLE CUSTOMER
(
CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY,
FIRST_NAME VARCHAR2(30) NOT NULL,
LAST_NAME VARCHAR2(30) NOT NULL,
PHONE VARCHAR2(15) NOT NULL,
EMAIL VARCHAR2(80),
STATUS CHAR(1)
)
PARTITION BY RANGE (CUSTOMER_ID)
(
PARTITION CUS_PART1 VALUES LESS THAN (100000) TABLESPACE CUS_TS01,
PARTITION CUS_PART2 VALUES LESS THAN (200000) TABLESPACE CUS_TS02
)
例二：按时间划分
CREATE TABLE ORDER_ACTIVITIES
(
ORDER_ID NUMBER(7) NOT NULL,
ORDER_DATE DATE,
TOTAL_AMOUNT NUMBER,
CUSTOTMER_ID NUMBER(7),
PAID CHAR(1)
)
PARTITION BY RANGE (ORDER_DATE)
(
PARTITION ORD_ACT_PART01 VALUES LESS THAN (TO_DATE('01- MAY -2003','DD-MON-YYYY')) TABLESPACEORD_TS01,
PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE('01-JUN-2003','DD-MON-YYYY')) TABLESPACE ORD_TS02,
PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE('01-JUL-2003','DD-MON-YYYY')) TABLESPACE ORD_TS03
)
例三：MAXVALUE
CREATE TABLE RangeTable
(
idd INT PRIMARY KEY ,
iNAME VARCHAR(10),
grade INT
)
PARTITION BY RANGE (grade)
(
PARTITION part1 VALUES LESS THAN (1000) TABLESPACE Part1_tb,
PARTITION part2 VALUES LESS THAN (MAXVALUE) TABLESPACE Part2_tb
);
二.列表分区：
该分区的特点是某列的值只有几个，基于这样的特点我们可以采用列表分区。
例一
CREATE TABLE PROBLEM_TICKETS
(
PROBLEM_ID NUMBER(7) NOT NULL PRIMARY KEY,
DESCRIPTION VARCHAR2(2000),
CUSTOMER_ID NUMBER(7) NOT NULL,
DATE_ENTERED DATE NOT NULL,
STATUS VARCHAR2(20)
)
PARTITION BY LIST (STATUS)
(
PARTITION PROB_ACTIVE VALUES ('ACTIVE') TABLESPACE PROB_TS01,
PARTITION PROB_INACTIVE VALUES ('INACTIVE') TABLESPACE PROB_TS02)
例二
CREATE TABLE ListTable
(
id INT PRIMARY KEY ,
name VARCHAR (20),
area VARCHAR (10)
)
PARTITION BY LIST (area)
(
PARTITION part1 VALUES ('guangdong','beijing') TABLESPACE Part1_tb,
PARTITION part2 VALUES ('shanghai','nanjing') TABLESPACE Part2_tb
);
三.散列分区（也称HASH分区）：
这类分区是在列值上使用散列算法，以确定将行放入哪个分区中。当列的值没有合适的条件时，建议使用散列分区。
散列分区为通过指定分区编号来均匀分布数据的一种分区类型，因为通过在I/O设备上进行散列分区，使得这些分区大小一致。
例一：
CREATE TABLE HASH_TABLE
(
COL NUMBER(8),
INF VARCHAR2(100)
)
PARTITION BY HASH (COL)
(
PARTITION PART01 TABLESPACE HASH_TS01,
PARTITION PART02 TABLESPACE HASH_TS02,
PARTITION PART03 TABLESPACE HASH_TS03
)
简写：
CREATE TABLE emp
(
empno NUMBER (4),
ename VARCHAR2 (30),
sal NUMBER
)
PARTITION BY HASH (empno) PARTITIONS 8
STORE IN (tbs01,tbs02,tbs03,tbs04,tbs05,tbs06,tbs07,tbs08);
hash分区最主要的机制是根据hash算法来计算具体某条纪录应该插入到哪个分区中,hash算法中最重要的是hash函数，Oracle中如果你要使用hash分区，只需指定分区的数量即可。建议分区的数量采用2的n次方，这样可以使得各个分区间数据分布更加均匀。
四.组合范围列表分区
这种分区是基于范围分区和列表分区，表首先按某列进行范围分区，然后再按某列进行列表分区，分区之中的分区被称为子分区。
CREATE TABLE SALES
(
PRODUCT_ID VARCHAR2(5),
SALES_DATE DATE,
SALES_COST NUMBER(10),
STATUS VARCHAR2(20)
)
PARTITION BY RANGE(SALES_DATE) SUBPARTITION BY LIST (STATUS)
(
PARTITION P1 VALUES LESS THAN(TO_DATE('2003-01-01','YYYY-MM-DD'))TABLESPACE rptfact2009
(
SUBPARTITION P1SUB1 VALUES ('ACTIVE') TABLESPACE rptfact2009,
SUBPARTITION P1SUB2 VALUES ('INACTIVE') TABLESPACE rptfact2009
),
PARTITION P2 VALUES LESS THAN (TO_DATE('2003-03-01','YYYY-MM-DD')) TABLESPACE rptfact2009
(
SUBPARTITION P2SUB1 VALUES ('ACTIVE') TABLESPACE rptfact2009,
SUBPARTITION P2SUB2 VALUES ('INACTIVE') TABLESPACE rptfact2009
)
)
五.复合范围散列分区：
这种分区是基于范围分区和散列分区，表首先按某列进行范围分区，然后再按某列进行散列分区。
create table dinya_test
(
transaction_id number primary key,
item_id number(8) not null,
item_description varchar2(300),
transaction_date date
)
partition by range(transaction_date)subpartition by hash(transaction_id) subpartitions 3 store in (dinya_space01,dinya_space02,dinya_space03)
(
partition part_01 values less than(to_date(‘2006-01-01’,’yyyy-mm-dd’)),
partition part_02 values less than(to_date(‘2010-01-01’,’yyyy-mm-dd’)),
partition part_03 values less than(maxvalue)
);
(5).有关表分区的一些维护性操作：
一、添加分区
以下代码给SALES表添加了一个P3分区
ALTER TABLE SALES ADD PARTITION P3 VALUES LESS THAN (TO_DATE('2003-06-01','YYYY-MM-DD')) TABLESPACE SPACE_NAME;
注意：增加一个分区的时候，增加的分区的条件必须大于现有分区的最大值，否则系统将提示ORA-14074 partition bound must collate higher than that of the last partition 错误。
以下代码给SALES表的P3分区添加了一个P3SUB1子分区
ALTER TABLE SALES MODIFY PARTITION P3 ADD SUBPARTITION P3SUB1 VALUES('COMPLETE') TABLESPACE SPACE_NAME;
二、删除分区
以下代码删除了SALES表中名为P3的分区：
ALTER TABLE SALES DROP PARTITION P3;
在以下代码删除了P4SUB1子分区：
ALTER TABLE SALES DROP SUBPARTITION P4SUB1;
注意：如果删除的分区是表中唯一的分区，那么此分区将不能被删除，要想删除此分区，必须删除表。
三、截断分区
截断某个分区是指删除某个分区中的数据，并不会删除分区，也不会删除其它分区中的数据。当表中即使只有一个分区时，也可以截断该分区。通过以下代码截断分区：
ALTER TABLE SALES TRUNCATE PARTITION P2;
通过以下代码截断子分区：
ALTER TABLE SALES TRUNCATE SUBPARTITION P2SUB2;
四、合并分区
合并分区是将相邻的分区合并成一个分区，结果分区将采用较高分区的界限，值得注意的是，不能将分区合并到界限较低的分区。以下代码实现了P1 P2分区的合并：
ALTER TABLE SALES MERGE PARTITIONS P1, P2 INTO PARTITION P2;
五、拆分分区
拆分分区将一个分区拆分两个新分区，拆分后原来分区不再存在但是如果表存在PMAX分区那么原来的分区还是可以存在的。注意不能对 HASH类型的分区进行拆分。
ALTER TABLE SALES SBLIT PARTITION P2 AT(TO_DATE('2003-02-01','YYYY-MM-DD')) INTO (PARTITION P21,PARTITION P22);
此拆分语句是将P2分区拆分为PARTITION P21与PARTITION P22两个分区，日期小于2003-02-01的数据存于 P21分区中否则存于 P22分区中。
--包含PMAX分区的表拆分分区
ALTER TABLE hs_his.HISHOLDSINFO SPLIT PARTITION PMAX AT(20100900) INTO (partition P201008, partition PMAX);
此拆分语句是将 PMAX 分区拆分为P201008与 PMAX 两个分区，数据小于20100900 的数据存于 P201008 分区中否则存于 PMAX 分区中。
六、接合分区(coalesca)
结合分区是将散列分区中的数据接合到其它分区中，当散列分区中的数据比较大时，可以增加散列分区，然后进行接合，值得注意的是，接合分区只能用于散列分区中。通过以下代码进行接合分区：
ALTER TABLE SALES COALESCA PARTITION;
七、重命名表分区
以下代码将P21更改为P2
ALTER TABLE SALES RENAME PARTITION P21 TO P2;

[勤学&苦练]

也来跟着学习学习。

[天黑嘉嘉]

我的理解是分区为了把表的粒度缩小到分区，这样就可以基于分区对表进行管理了。这对于数据了很大的表来说可以更加方便管理和维护。至于相关实现和管理的语句，这个比较好搜索，可以参考：
http://blog.csdn.net/feiyu84/article/details/6430512#comments

[大脸猫o]

先抛出一个问题：
1、一般按什么规则来分区，有没有创建分区的脚本？
具体自己可以百度，google下
1、oracle分区主要是：范围分区，hash（散列）分区，列表分区和复合分区
时间类的按照范围分区较多，
地域类，种类固定的可以考虑列表，
hash的没怎么用过，你可以理解不适合以上两种的，
复合分区就是以上3种方式2种以上结合进行分区，比如 时间（范围分区）+地域（列表）

脚本 抛砖引玉做个 range的。

drop table range_t;
create table range_t
(range_col varchar2(8),
t2 varchar2(20))
partition by range (range_col)
(
partition P_20180101 values less than('20180102'),
partition P_20180102 values less than('20180103'),
partition P_MAX values less than(maxvalue)
)

insert into range_t values ('20180101','P_20180101');
insert into range_t values ('20180102','P_20180102');
insert into range_t values ('20180103','P_MAX');
insert into range_t values ('20190101','P_MAX');

select * from range_t partition(P_20180102);
select * from range_t partition(P_MAX);

2、分区表有什么优势和劣势，以及对备份及数据库维护啥的，有木有需要注意的地方？
分区表的优势就是将一张大表根据分区规则拆分为一些小分区表，分区表之间相互独立，不影响彼此，表锁只生效于分区表，不影响全表，查询速度更快，劣势维护起来比非区分区表麻烦，索引不同。
备份比较方便，可增量，可全量，节约时间。
总体来说收益大于弊端。

[大脸猫o]

引用 162 楼 战狼二号 的回复:

不懂，看了评论区，实用性不强啊

你被误导了。

[Medo丶]

单纯的想进来学点东西，搬个板凳

[weixin_44892524]

虽然不是很懂

[qq_43394417]

目前在用Oracle，但是感觉真心不太好用

[AHUA1001]

分区的脚本，根据需求不同，不止一种，常见的有 范围分区、Hash分区、List分区、组合分区。
相关的脚本样例可以参考以下链接地址。
https://www.cnblogs.com/andy6/p/6238512.html
分区的优势主要在于：
1、可以把大表，按照逻辑，分成相关的块，减小扫描范围，以此来提高查询范围。
2、减小服务器的压力，同一个表，可以把不同的分区放在不同的磁盘，甚至不同的主机上。
3、安全性，以上说到的，可以把不同的分区放在不同的磁盘，甚至不同的主机上，当某一个分区所在的磁盘或者主机发生故障，其它磁盘或者主机上的分区数据，仍然可以正常使用。
分区的劣势主要在于：
1、加大入门成本，虽然不是很复杂，但是相关的注意事项，还是要积累一段时间的。
2、只适用于大数据量，否则就是画蛇添足。

另外，好多系统都是按照单一的时间来分区的，但是我个人认为，这样做，未必是最好的方案，因为随着时间的推移，分区会越来越多，到了后期，维护成本会比较高。

最好是按照某个固定的逻辑建立分区，分区的数量，最好是2的次方数，就是2、4、8、16、32……，最好不要大于16。
数量多，上边说过，后期维护成本比较高。数量如果不是2的次方数，其实逻辑上没什么，但是让人感觉不专业。就类似与家里的卫生间和厨房对门，没什么大不了的，但是总让人感觉怪怪的。

[xhh_1986178185]

准备入坑oracle，进来学习

[zhoufei1007]

想请教datagrip的问题

[fly_zq]

good good

[lhdz_bj]

1、分区为海量数据的管理和维护提供了极大的方便；
2、分区也为海量数据的查询等操作的性能优化提供了技术支撑。

[xhh_1986178185]

大家好，我是来学习的

[今夕明夕]

支持一下,严重支持

[好好学习振兴中华]

这个挺好的，感谢

[Calimin]

已置顶，支持一下

[women1949]

范围列表哈希复合分区

[xhh_1986178185]

这个就不清楚了，学习

[AT152S]

Oracle的表分区功能通过改善可管理性、性能和可用性，从而为各式应用程序带来了极大的好处。通常，分区可以使某些查询以及维护操作的性能大大提高。此外,分区还可以极大简化常见的管理任务，分区是构建千兆字节数据系统或超高可用性系统的关键工具。