表 分 区 学 习 笔 记
目 录
一． 什么是表分区
1. 分区主要有两种形式
水平分区（HorizontalPartitioning）:
这种形式分区是对表的行进行分区，通过这样的方式不同分组里面的物理列分割的数据集得以组合，从而进行个体分割（单分区）或集体分割（1个或多个分区）。所有在表中定义的列在每个数据集中都能找到，所以表的特性依然得以保持。
垂直分区（VerticalPartitioning）：
这种分区方式一般来说是通过对表的垂直划分来减少目标表的宽度，使某些特定的列被划分到特定的分区，每个分区都包含了其中的列所对应的行。
注；我们下面介绍的都是水平分区。
2. 表空间
是一个或多个数据文件的集合，所有的数据对象都存放在指定的表空间中，但主要存放的是表，所以称作表空间。
3. 分区
表分区其实就是将一张大数据量表中的数据按照不同的分区策略分配到不同的系统分区、硬盘或是不同的服务器设备上，实现数据的均衡分配。
4. 分区与分表
(1).分区和分表针对的都是数据表。
(2).分表是真正的生成数据表，是将一张大数据量的表分成多个小表实现数据均衡。
(3).分区并不是生成新的数据表，而是将表的数据均衡分摊到不同的硬盘，系统或是不同服务器存储介子中，实际上还是一张表。
(4)分区和分表都可以做到将表的数据均衡到不同的地方，提高数据检索的效率，降低数据库的频繁IO压力值
二． 为什么要分区
1.表分区的优点
(1). 增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用；
(2).维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可
(3).均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能
(4). 改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度
2.表分区的缺点
分区表相关：已经存在的表没有方法可以直接转化为分区表。不过 Oracle 提供了在线重定义表的功能。
3.表分区规则
当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表。
关于何时应该进行分区,Oracle有如下建议:
（1）Tables greater than 2GB should always be considered forpartitioning.
（2）Tablescontaining historical data, in which new data is added into the newestpartition.
关于分区其他：
（1）对于Oracle9iR2:Tables can be partitioned into up to 64,000 separate partitions.
（2）对于Oracle10gR2:Tables can be partitioned into up to 1024K-1 separate partitions
注：查看表所占空间大小
SELECT segment_name AS TABLENAME, BYTES/1024/1024 MB
FROM user_segments
WHERE segment_name='T_ZJCG_USERINFO'三． 分区策略
在分区之前可以先建几个表空间，进行表分区时可以将该分区放入指定的表空间。如下为创建表空间的的方法：、
create tablespace dinya_space01 datafile 'C:\表空间\dinya01.dbf' SIZE 5M;
create tablespace dinya_space02 datafile 'C:\表空间\dinya02.dbf' SIZE 5M;
create tablespace dinya_space03 datafile 'C:\表空间\dinya03.dbf' SIZE 5M;
select * from user_tablespaces --查询表空间查询该表有哪些分区
select partition_name from user_tab_partitions where table_name='表名'; 1. 范围分区（range）
范围分区就是对数据表中的某个值的范围进行分区，根据某个值的范围，决定将该数据存储在哪个分区上。
(1).每一个分区都必须有一个VALUES LESS THEN子句，它指定了一个不包括在该分区中的上限值。分区键的任何值等于或者大于这个上限值的记录都会被加入到下一个高一些的分区中。
(2).所有分区，除了第一个，都会有一个隐式的下限值，这个值就是此分区的前一个分区的上限值。
(3).在最高的分区中，MAXVALUE被定义。MAXVALUE代表了一个不确定的值。这个值高于其它分区中的任何分区键的值，也可以理解为高于任何分区中指定的VALUE LESS THEN的值，同时包括空值。
例一：按CUSTOMER_ID划分
假设有一个CUSTOMER表，表中有数据200000行，我们将此表通过CUSTOMER_ID进行分区，每个分区存储100000行，我们将每个分区保存到单独的表空间中，这样数据文件就可以跨越多个物理磁盘。下面是创建表和分区的代码，如下：
CREATE TABLE CUSTOMER(
CUSTOMER_ID NUMBER NOT NULL PRIMARYKEY,
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 VALUESLESSTHAN (100000) TABLESPACE CUS_TS01,
PARTITION CUS_PART2 VALUESLESSTHAN (200000) TABLESPACE CUS_TS02
)例二：按时间划分
首先来介绍下numtodsinterval和numtoyminterval两个函数。
numtoyminterval和numtoyminterval是日期转换函数,作用：可以将数字转换成相应的日期单位时间
语法：NUMTOYMINTERVAL ( n , 'char_expr' )
char_expr:日期描述，可以是YEAR和MONTH。
通常当我们使用add_month添加月时，如果输入是本月月底的日期，那么得到的也是月底的日期，比如add_month(to_date('2007-02-28','yyyy-mm-dd'),1)得到的就会是'2007-03-31'，而不是'2007-03-28'。
此时，如果使用的是to_date('2007-02-28','yyyy-mm-dd')+numtoyminterval(1,'month')，那么得到的就 是'2007-03-28'。
numtodsinterval的用法和numtoyminterval一样只是他的参数为day、hour、minute、second。
例如：
Oracle 11g之前，维护分区需要手工。11g之后使用interval来实现自动扩展分区，简化了维护。
根据年: INTERVAL(NUMTOYMINTERVAL(1,'YEAR'))
根据月: INTERVAL(NUMTOYMINTERVAL(1,'MONTH'))
根据天: INTERVAL(NUMTODSINTERVAL(1,'DAY'))
根据时分秒: NUMTODSINTERVAL( n, { 'DAY'|'HOUR'|'MINUTE'|'SECOND'})
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 VALUESLESSTHAN (TO_DATE('01- MAY -2003','DD-MON-YYYY')) TABLESPACE ORD_TS01,
PARTITION ORD_ACT_PART02 VALUESLESSTHAN (TO_DATE('01-JUN-2003','DD-MON-YYYY')) TABLESPACE ORD_TS02,
PARTITION ORD_ACT_PART02 VALUESLESSTHAN (TO_DATE('01-JUL-2003','DD-MON-YYYY')) TABLESPACE ORD_TS03
)例三：MAXVALUE
CREATE TABLE RangeTable(
idd NUMBER PRIMARY KEY ,
iNAME VARCHAR(10),
grade NUMBER
)
PARTITION BY RANGE (grade)
(
PARTITION part1 VALUES LESS THEN (1000) TABLESPACE Part1_tb,
PARTITION part2 VALUES LESS THEN (MAXVALUE) TABLESPACE Part2_tb
);例四：自动扩展分区
/*创建自动扩展分区表*/
create table TABLE_NAME_CDD (fid number not null PRIMARY KEY, sdate date)
partition by range (sdate) INTERVAL(NUMTODSINTERVAL(1,'DAY'))
(
partition part_name_P01 values less than (to_date('2018-06-01', 'yyyy-mm-dd'))
)
/*删掉表*/
drop table TABLE_NAME_CDD;
/*插入数据*/
insert into TABLE_NAME_CDD
values(1,sysdate-1);
insert into TABLE_NAME_CDD
values(2,sysdate);
insert into TABLE_NAME_CDD
values(3,sysdate+1);
insert into TABLE_NAME_CDD
values(4,sysdate+2);
/*查询所有数据*/
select * from TABLE_NAME_CDD;
/*查询该表目前有哪些分区*/
select partition_name from user_tab_partitions where table_name='TABLE_NAME_CDD';
/*查询第一个分区的数据*/
select * from TABLE_NAME_CDD PARTITION(part_name_P01);注意：上面的例子中只要日期在2018-06-01之前的，都会在第一个分区。
2. 列表分区（list）
该分区的特点是某列的值只有几个，就分几个区，基于这样的特点我们可以采用列表分区。
例子:按status的值（‘ACTIVE’/INACTIVE）来分区
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
)3. 哈希分区（hash）
这类分区是在列值上使用散列算法，以确定将行放入哪个分区中。当列的值没有合适的条件时，建议使用散列分区。散列分区为通过指定分区编号来均匀分布数据的一种分区类型，因为通过在I/O设备上进行散列分区，使得这些分区大小一致。
我们对hash partition都有一个常识，就是partition的数量最好是2的次方，也就是2，4，8，16……，否则分区会出现不分区均衡的现象，按照hash的原理，不管是几个分区，都可以做到完全均衡的，为什么会不均衡，其实答案已经出来了，Oracle为了能够增加分区，为你预留了几个看不到的分区。
例一：
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
)
PARTITIONBY HASH (empno) PARTITIONS8
STOREIN(emp1,emp2,emp3,emp4,emp5,emp6,emp7,emp8);4. 复合分区
（1） range-list
这种分区是基于范围分区和列表分区，表首先按某列进行范围分区，然后再按某列进行列表分区，分区之中的分区被称为子分区。
例如：
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 LESSTHAN (TO_DATE('2003-03-01','YYYY-MM-DD')) TABLESPACE rptfact2009
(
SUBPARTITION P2SUB1 VALUES ('ACTIVE') TABLESPACE rptfact2009,
SUBPARTITION P2SUB2 VALUES ('INACTIVE') TABLESPACE rptfact2009
) )（2） range-hash
这种分区是基于范围分区和散列分区，表首先按某列进行范围分区，然后再按某列进行散列分区。
例如：
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)
);
四． 已存在的表分区
1.利用原表重建分区表
看实例：
step1.创建表TABLE_CDD
CREATETABLE TABLE_CDD (IDNUMBERPRIMARYKEY, TIMEDATE);step2.给表中添加大量数据
INSERTINTO TABLE_CDD SELECTROWNUM, CREATED FROM DBA_OBJECTS;step3.提交
COMMITstep4.创建新的副本表并分区
CREATETABLE TABLE_CDD_NEW (ID, TIME)
PARTITION BY RANGE (TIME)
(
PARTITION P1 VALUES LESS THAN (TO_DATE('2004-7-1', 'YYYY-MM-DD')),
PARTITION P2 VALUES LESS THAN (TO_DATE('2005-1-1', 'YYYY-MM-DD')),
PARTITION P3 VALUESLESSTHAN (TO_DATE('2005-7-1', 'YYYY-MM-DD')),
PARTITION P4 VALUESLESSTHAN (MAXVALUE)
)
AS SELECT ID, TIME FROM TABLE_CDD;step5.重命名TABLE_CDD
RENAMETABLE_CDD T0 TABLE_CDD_OLD;step6.重命名TABLE_CDD_NEW
RENAMETABLE_CDD_NEW TO TABLZE_CDD;step7.查询检验
SELECTCOUNT(*) FROM TABLE_CDD; --结果为c
SELECTCOUNT(*) FROM TABLE_CDD PARTITION(P1); --结果为c1
SELECTCOUNT(*) FROM TABLE_CDD PARTITION(P2); --结果为c2
SELECTCOUNT(*) FROM TABLE_CDD PARTITION(P3); --结果为c3验证c=c1+c2+c3是否成立。
总结
优点：方法简单易用，由于采用DDL语句，不会产生UNDO，且只产生少量REDO，效率相对较高，而且建表完成后数据已经在分布到各个分区中了。
不足：对于数据的一致性方面还需要额外的考虑。由于几乎没有办法通过手工锁定T表的方式保证一致性，在执行CREATE TABLE语句和RENAME T_NEW TO T语句直接的修改可能会丢失，如果要保证一致性，需要在执行完语句后对数据进行检查，而这个代价是比较大的。另外在执行两个RENAME语句之间执行的对T的访问会失败。适用于修改不频繁的表，在闲时进行操作，表的数据量不宜太大。
2.使用交换分区的方法。
看实例：
step1.创建表TABLE_CDD
CREATE TABLE TABLE_CDD (IDNUMBERPRIMARYKEY, TIMEDATE);step2.给表中添加大量数据
INSERT INTO TABLE_CDD SELECTROWNUM, CREATED FROM DBA_OBJECTS;step3.提交
COMMITstep4.创建分区表
CREATE TABLE TABLE_CDD_NEW (IDNUMBERPRIMARYKEY, TIMEDATE)
PARTITION BY RANGE (TIME)
(
PARTITION P1 VALUESLESSTHAN (TO_DATE('2005-7-1', 'YYYY-MM-DD')),
PARTITION P2 VALUESLESSTHAN (MAXVALUE)
); setp5.更改表
ALTER TABLE TABLE_CDD_NEW EXCHANGE PARTITION P1 WITH TABLE TABLE_CDD;step6.重命名TABLE_CDD 为 TABLE_CDD_OLD
RENAME TABLE_CDD T0TABLE_CDD_OLD;step7.重命名TABLE_CDD_NEW为TABLE_CDD
RENAME TABLE_CDD_NEW TO TABLZE_CDD;step8.查询
SELECTCOUNT(*) FROM TABLE_CDD;总结：
优点：只是对数据字典中分区和表的定义进行了修改，没有数据的修改或复制，效率最高。如果对数据在分区中的分布没有进一步要求的话，实现比较简单。在执行完RENAME操作后，可以检查T_OLD中是否存在数据，如果存在的话，直接将这些数据插入到T中，可以保证对T插入的操作不会丢失。
不足：仍然存在一致性问题，交换分区之后RENAME T_NEW TO T之前，查询、更新和删除会出现错误或访问不到数据。如果要求数据分布到多个分区中，则需要进行分区的SPLIT操作，会增加操作的复杂度，效率也会降低。
适用于包含大数据量的表转到分区表中的一个分区的操作。应尽量在闲时进行操作。
3.Oracle9i以上版本，利用在线重定义功能
看实例：
step1:创建表
CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE)step2.录入数据
INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTSstep3.提交
COMMITstep4.
EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(USER, 'T',DBMS_REDEFINITION.CONS_USE_PK)step5.创建分区表
CREATE TABLE T_NEW (IDNUMBERPRIMARYKEY, TIMEDATE)
PARTITION BY RANGE (TIME)
(
PARTITION P1 VALUESLESSTHAN (TO_DATE('2004-7-1', 'YYYY-MM-DD')),
PARTITION P2 VALUESLESSTHAN (TO_DATE('2005-1-1', 'YYYY-MM-DD')),
PARTITION P3 VALUESLESSTHAN (TO_DATE('2005-7-1', 'YYYY-MM-DD')),
PARTITION P4 VALUESLESSTHAN (MAXVALUE)
)step6.
EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'T', 'T_NEW', - 'ID ID, TIME TIME', DBMS_REDEFINITION.CONS_USE_PK)step7.
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('YANGTK', 'T', 'T_NEW')step8.查询验证
SELECT COUNT(*) FROM T --整表查询记录数c
SELECT COUNT(*) FROM T PARTITION (P1) --查询P1分区记录数c1
SELECT COUNT(*) FROM T PARTITION (P2) --查询p2分区记录数c2
SELECT COUNT(*) FROM T PARTITION (P3) --查询P3分区记录数c3
SELECT COUNT(*) FROM T PARTITION (P4) --查询P4分区记录数c4验证c = c1 + c2 + c3 + c4
五． 表分区的一些维护性操作
1.添加分区
以下代码给SALES表添加了一个P3分区
ALTER TABLE SALES ADD PARTITION P3 VALUES LESSTHAN(TO_DATE('2003-06-01','YYYY-MM-DD'));注意：以上添加的分区界限应该高于最后一个分区界限。
以下代码给SALES表的P3分区添加了一个P3SUB1子分区
ALTER TABLE SALES MODIFY PARTITION P3 ADD SUBPARTITION P3SUB1 VALUES('COMPLETE');2.删除分区
以下代码删除了P3表分区：
ALTER TABLE SALES DROP PARTITION P3;在以下代码删除了P4SUB1子分区：
ALTER TABLE SALES DROP SUBPARTITION P4SUB1;注意：如果删除的分区是表中唯一的分区，那么此分区将不能被删除，要想删除此分区，必须删除表。
3.截断分区
截断某个分区是指删除某个分区中的数据，并不会删除分区，也不会删除其它分区中的数据。当表中即使只有一个分区时，也可以截断该分区。通过以下代码截断分区：
ALTER TABLE SALES TRUNCATE PARTITION P2;通过以下代码截断子分区：
ALTER TABLE SALES TRUNCATE SUBPARTITION P2SUB2;4.合并分区
合并分区是将相邻的分区合并成一个分区，结果分区将采用较高分区的界限，值得注意的是，不能将分区合并到界限较低的分区。以下代码实现了P1 P2分区的合并：
ALTER TABLE SALES MERGE PARTITIONS P1,P2 INTO PARTITION P2;5.拆分分区
拆分分区将一个分区拆分两个新分区，拆分后原来分区不再存在。注意不能对HASH类型的分区进行拆分。
ALTER TABLE SALES SBLIT PARTITION P2AT(TO_DATE('2003-02-01','YYYY-MM-DD')) INTO (PARTITION P21,PARTITION P22);
6.接合分区(coalesca)
结合分区是将散列分区中的数据接合到其它分区中，当散列分区中的数据比较大时，可以增加散列分区，然后进行接合，值得注意的是，接合分区只能用于散列分区中。通过以下代码进行接合分区：
ALTER TABLE SALES COALESCA PARTITION;7.重命名表分区
以下代码将P21更改为P2
ALTER TABLE SALES RENAME PARTITION P21 TO P2;8.相关查询
(1).跨分区查询
select sum( *) from
(select count(*) cn from t_table_SS PARTITION (P200709_1)
union all
select count(*) cn from t_table_SS PARTITION (P200709_2)
);(2).查询表上有多少分区
SELECT * FROM user_TAB_PARTITIONS WHERE TABLE_NAME='tableName'(3).查询索引信息
select object_name,object_type,tablespace_name,sum(value)
from v$segment_statistics
where statistic_name IN ('physical reads','physical write','logicalreads')and object_type='INDEX'
group by object_name,object_type,tablespace_name
order by 4 desc(4).其他
--显示数据库所有分区表的信息：
select * from DBA_PART_TABLES--显示当前用户可访问的所有分区表信息:
select * from ALL_PART_TABLES--显示当前用户所有分区表的信息：
select * from USER_PART_TABLES--显示表分区信息显示数据库所有分区表的详细分区信息：
select * from DBA_TAB_PARTITIONS--显示当前用户可访问的所有分区表的详细分区信息：
select * from ALL_TAB_PARTITIONS--显示当前用户所有分区表的详细分区信息：
select * from USER_TAB_PARTITIONS--显示子分区信息显示数据库所有组合分区表的子分区信息：
select * from DBA_TAB_SUBPARTITIONS--显示当前用户可访问的所有组合分区表的子分区信息：
select * from ALL_TAB_SUBPARTITIONS--显示当前用户所有组合分区表的子分区信息：
select * from USER_TAB_SUBPARTITIONS--显示分区列显示数据库所有分区表的分区列信息：
select * from DBA_PART_KEY_COLUMNS--显示当前用户可访问的所有分区表的分区列信息：
select * from ALL_PART_KEY_COLUMNS--显示当前用户所有分区表的分区列信息：
select * from USER_PART_KEY_COLUMNS--显示子分区列显示数据库所有分区表的子分区列信息：
select * from DBA_SUBPART_KEY_COLUMNS--显示当前用户可访问的所有分区表的子分区列信息：
select * from ALL_SUBPART_KEY_COLUMNS--显示当前用户所有分区表的子分区列信息：
select * from USER_SUBPART_KEY_COLUMNS--怎样查询出oracle数据库中所有的的分区表
select * from user_tables a where a.partitioned='YES'--删除一个表的数据是
truncate table table_name;--删除分区表一个分区的数据是
alter table table_name truncate partition p5;--查看当前数据库是那个版本
select * from product_component_version;特别说明：在学习表分区时，以上是我查阅各种文章后的表分区学习笔记，其中难免有参阅到大佬的文章，很小小部分摘录在我的文章中，如有侵权，敬请留言，以便删改！如有错误，希望批评指正，我们一起进步！
202
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
