oracle表分区设计_Oracle数据库分区技术

最新推荐文章于 2021-04-04 21:45:08 发布

复制验证码

最新推荐文章于 2021-04-04 21:45:08 发布

阅读量436

点赞数

文章标签： oracle表分区设计

本文链接：https://blog.csdn.net/weixin_42536863/article/details/112021137

版权

1.Oracle的分区技术基本原理：

Oracle的分区技术的基本思路就是：分而治之。

2.分区概述

l 大数据对象(表、索引)被分成小的物理段

l 当分区表建立时，记录基于分区字段值被存储到相应分区

l 分区字段值可以修改(要打开 row movement enabled功能)

l 分区可以存储在不同的表空间

l 分区可以有不同的物理存储参数

l 分区支持IOT表(索引组织表)、对象表、LOB字段(Large Objects)、varrays(树簇)等

3.分区技术的效益

l 性能

Ø Select和DML操作只访问指定分区

Ø 并行DML操作(多个CPU同时工作。比如32颗CPU开10个并行，提高执行效率)

Ø Partition-Wise

Join(分区连接)

l 可管理性：数据删除，数据备份(导入导出，数据泵)

Ø 历史数据清除

Ø 提高备份性能

Ø 指定分区的数据维护操作

l 可用性

Ø 将故障局限在分区中

Ø 缩短恢复时间

l 分区目标优先级

高性能à数据的维护能力à实施难度à高可用性(故障屏蔽能力)

分区技术在Oracle_12C中增加的新特性：

l 支持自动list分区

l 支持间隔子分区

l 在线直接把普通表转换成分区表(*)

4.Oracle分区策略和业务案例

Oracle_11g基本的分区策略和概述

分区策略

数据分布

业务案例

范围分区(Range)

基于值的连续范围

Order表按order_date列进行范围分区

列表分区(List)

基于值的无序列表

Order表按country列进行列表分区

哈希分区(Hash)

基于一种hash算法

Order表按customer_id列进行哈希分区

组合分区：

Range—Range

Range—List

Range—Hash

List—List

List—Range

List—Hash

基于上述的两个基本技术Range，list，hash，interval分区的组合

Order表按order_date列进行范围分区，子分区按customer_id列进行哈希分区。

Order表按order_date列进行范围分区，子分区按shipment_date列进行范围分区

Oracle_11g附加分区策略和概述

分区扩展

分区键

业务案例

Interval分区

² Interval

² Interval-Range

² Interval-list

² Interval-Hash

扩展范围分区。定义一个间隔，提供同等宽度范围。除第一个分区外，其他所有分区是在匹配数据到达时自动创建的

Order表按order_date列从“01-Jan-2007”起按每一天的间隔进行范围分区

引用分区

子表通过主键-外键关系从父表继承分区。分区键是在子表中没有实际的存储的虚拟列

Orders表order_date列进行范围分区，子表order_line继承父表的分区技术。Order_date列只存在父表orders中。

虚拟列分区

通过上述的分区技术和分区键是基于虚拟列定义分区。虚拟列为存储在磁盘上，仅作为元数据只存在

Orders具有一个虚拟列基于客户账号的前三位的销售区域代码。Orders表基于这个区域代码进行列表分区

5.范围分区

示例一

CREATE

TABLE sales_parts (serial_id NUMBER(10,0),

goods_id

NUMBER(10,0),unit_price(12,2),

quantity

NUMBER(12,2),subtotal (NUMBER(12,2),

create_date

date)

PARTITION

BY RANGE (create_date)

(PARTITION

p2009 VALUES LESS THAN (to_date('2010-01-01','YYYY-MM-DD')),

PARTITION

p2010 VALUES LESS THAN (to_date('2011-01-01','YYYY-MM-DD')),

PARTITION

p2011 VALUES LESS THAN (to_date('2012-01-01','YYYY-MM-DD')),

PARTITION

p2012 VALUES LESS THAN (to_date('2013-01-01','YYYY-MM-DD')),

PARTITION

p2013 VALUES LESS THAN (to_date('2014-01-01','YYYY-MM-DD')),

PARTITION

p2014 VALUES LESS THAN (to_date('2015-01-01','YYYY-MM-DD')),

PARTITION

p2015 VALUES LESS THAN (to_date('2016-01-01','YYYY-MM-DD')),

PARTITION

p2016 VALUES LESS THAN (to_date('2017-01-01','YYYY-MM-DD')),

PARTITION

pmaxval VALUES LESS THAN (MAXVALUE));

注：partition by range 分区字段：create_date,values less than 必须是确定值。如上每个分区可以单独的定义物理属性。

范围分区的特点

l Range分区通过对分区字段值的范围进行分区

l Range分区特别适合于按时间周期进行数据的存储。日、周、月、年等。

l 数据管理能力强

数据迁移

数据备份

数据交换

l 范围分区的数据可能不均匀

l 范围分区与记录值相关，实施难度和可维护性相对较差

6.Hash分区

示例二

create

table CUSTOMERS_PARTS (customer_id number(10,0),customer_name varchar2(60),

customer_no

varchar2(10),address varchar2(100),country varchar2(20))

partition

by hash(customer_no) partitions 8 store in (cust_tbs01,cust_tbs02);

create

table CUSTOMERS_PARTS (customer_id number(10,0),customer_name varchar2(60),

customer_no

varchar2(10),address varchar2(100),country varchar2(20))

partition

by hash(customer_no)

(partition

cust_hash_p01 tablespace cust_tbs01

,partition

cust_hash_p02 tablespace cust_tbs02

,partition

cust_hash_p03 tablespace cust_tbs03

,partition

cust_hash_p04 tablespace cust_tbs04

,partition

cust_hash_p05 tablespace cust_tbs05

,partition

cust_hash_p06 tablespace cust_tbs06

,partition

cust_hash_p07 tablespace cust_tbs07

,partition

cust_hash_p08 tablespace cust_tbs08);

注：从上面两种Hash分区的创建方法可以看出，Hash分区的创建要比Range分区的创建语句简单很多，我们如果不指定它的分区名字的话，系统会自动命名。

Hash分区的特点

l 基于分区字段的HASH值，自动将记录插入到指定分区。

l 分区数一般是2的幂(如果不是的话分区数据会分布不均匀)

l 易于实施

l 总体性能最佳

l 适合于静态数据

l HASH分区适合于数据的均匀存储

l 数据管理能力弱

l HASH分区对数据值无法控制

7.列表分区

示例三

CREATE

TABLE b2c_orders_parts (serial_id NUMBER(10,0),

goods_id

NUMBER(10,0),unit_price(12,2),

quantity

NUMBER(12,2),subtotal (NUMBER(12,2),

city

varchar2(30),

create_date

date)

PARTITION

BY list (city)

(PARTITION

p_bj VALUES ('北京'),

PARTITION

p_sh VALUES ('上海')

PARTITION

p_gd VALUES ('广东')

PARTITION

p_ln VALUES ('辽宁')

PARTITION

p_sd VALUES ('山东')

PARTITION

p_other VALUES (DEFAULT));

注：如上列表分区会有默认的分区，不符合条件数据都会插入默认分区。

List分区的特点

l List分区通过对分区字段的离散值进行分区

l List分区是不排序的，而且分区之间没有关联关系

l List分区适合于对数据离散值进行控制

l List分区只支持单个字段

l List分区具有与范围分区相似得优缺点

Ø 数据管理能力强

Ø List分区的数据可能不均匀

Ø List分区与记录值相关，实施难度和可维护性相对较差

8.复合分区

示例四

CREATE

TABLE orders

(ordid

NUMBER, orderdate DATE, productid NUMBER,quantity NUMBER)

PARTITION

BY RANGE(orderdate)

SUBPARTITION

BY HASH(productid) SUBPARTITIONS 8

STORE

IN (users)

(PARTITION

q2009 VALUES LESS THAN(to_date('2010-01-01','yyyy-mm-dd'))

,PARTITION

q2010 VALUES LESS THAN(to_date('2011-01-01','yyyy-mm-dd'))

,PARTITION

q2011 VALUES LESS THAN(to_date('2012-01-01','yyyy-mm-dd'))

,PARTITION

q2012 VALUES LESS THAN(to_date('2013-01-01','yyyy-mm-dd')));

注:如上可以指定表空间store in (users)

复合分区的特点

l 既适合于历史数据，有适合于数据的均匀分布

l 与范围分区一样提供高可用性和管理性

l 实现粒度更细的操作

9.间隔分区(Interval)

示例五

CREATE

TABLE SH.SALES_INTERVAL

PARTITION

BY RANGE (time_id)

INTERVAL

(NUMTOYMINTERVAL(1,'month')) STORE IN (tbs1,tbs2,tbs3,tbs4)

(

PARTITION P1 values less than

(TO_DATE('1-1-2002','dd-mm-yyyy')),

PARTITION P2 values less than

(TO_DATE('1-1-2003','dd-mm-yyyy')),

PARTITION P3 values less than

(TO_DATE('1-1-2004','dd-mm-yyyy')))

SELECT *

FROM SH.SALES

WHERE TIME_ID < TO_DATE('1-1-2004','dd-mm-yyyy');

注：上面的例子是创建间隔分区表的过程。Store in 后面跟了四个表空间的名字，这样数据库会在插入数据时以循环的方式将间隔分区数据存储到指定的表空间中。同样的，我们也可以不指定表空间的名称。Intervl子句的唯一参数是一个间隔类型常量，目前只能指定一个分区键列，且该键列必须是DATE或NUMBER类型。

间隔分区的特点

l 间隔分区是范围分区的一种扩展

l 当插入的数据超过了所有范围分区时，将创建指定间隔的分区

l 必须至少创建一个范围分区

l 间隔分区可以自动创建范围分区

l 间隔分区需要考虑下列限制：

Ø 只能指定一个分区键列，并且该键列必须是Number或Date类型

Ø 索引表不支持间隔分区

Ø 不能为间隔分区表创建域索引

10.索引分区的选择

Global index

l 指向任何分区中的行

l 索引可以分区或不分区

l 表可以是分区表或者非分区表

l 全局分区索引只能是范围分区(分区键必须是索引的前缀)

Local index

l 索引分区和表分区对应，仅指向应用分区中的行

l 索引分区键总是和表分区键相同

如上图是全局非分区索引、全局分区索引、分区表、本地分区索引之间的关系。

分区索引举例

l 全局非前缀分区索引

create index cust_idx1 on

customers(customer_name)

global partition by range

(customer_name)

(partition cust_p01 values less than

(‘H’) tablespace cust_index01

,partition cust_p02 values less than

(‘N’) tablespace cust_index02

,partition cust_p03 values less than

(‘T’) tablespace cust_index03

,partition cust_p04 values

less than (MAXVALUE) tablespace cust_index04)；

l 本地前缀分区索引

create index cust_idx2 on

customers(customer_no) local

(partition cust_idx_p01 tablespace

cust_index01

,partition cust_idx_p02 tablespace

cust_index02

,partition cust_idx_p03 tablespace

cust_index03

,partition cust_idx_p04 tablespace

cust_index04

,partition cust_idx_p05 tablespace

cust_index05

,partition cust_idx_p06 tablespace

cust_index06

,partition cust_idx_p07 tablespace

cust_index07

,partition cust_idx_p08 tablespace

cust_index08)；

l 本地非前缀分区索引

create index cust_idx3 on

customers(customer_type) local;

注：一般建议建立LOCAL的索引，因为GLOBAL的索引容易引起所有的索引都失效，而LOCAL的分区索引最多只在某张分区表上失效。如果索引失效，必须一个分区一个分区的去rebuild。

11.分区表设计原则

表的大小

Ø 当表的大小超过1.5GB-2GB，或对于OLTP系统，表的记录超过了1000万，都应该考虑对表进行分区。

数据访问特性

Ø 基于表的大部分查询，只访问表中少量的数据。对于这样的表进行分区，可以充分利用分区排除无关数据查询的特性。

数据维护

Ø 按时间段删除成批的数据，例如按月删除历史数据。对于这样的表需要考虑进行分区，以满足维护的需要。

数据备份和恢复

Ø 按时间周期进行表空间的备份时，将分区与表空间建立对应关系。

只读数据

Ø 如果一个表中大部分数据都是只读数据，通过对表进行分区，可将只读数据存储在只读表空间中，对数据库的备份是非常有益的。

并行数据库操作

Ø 对于经常执行并行操作(如Parallel Insert，Parallel Update等)的表应考虑进行分区。

表的可用性

Ø 当对表的部分数据可用性要求很高时，应考虑进行表分区。

12.分区表的管理

分区表的管理：新增分区(ADD)

Ø 直接添加新的分区

alter table emp_part add

partition p10 values less than (10000);

注：以上添加的分区界限应该高于最后一个分区界限。子分区自动根据定义创建。

以下的命令给emp_part表的盘p10分区添加了1个hash子分区

alter table emp_part

modify partition p10 add subpartition sys_subp61;

Ø 使用拆分分区的方式添加新的分区(有MAXVALUE时)

alter

table emp_part split partition p_max

at (10000) into (partition p_max,partition p10);

分区表的管理：删除分区(DROP)

Ø 删除非Hash分区表的分区

alter

table emp_part drop partition (p10);

注：1.如果删除的分区是表中唯一的分区，那么此分区将不能被删除，要想删除此分区，必须删除表。

2.Hash分区和hash子分区不能删除

分区表的管理：收缩分区(Coalesce Partitions)

Ø 只被应用于hash分区或者复合分区的hash子分区

alter

table emp_part coalesce partition;

分区表的管理：截断分区(TRUNCATE)

截断某个分区是指删除某个分区中的数据，并不会删除分区，也不会删除其他分区中的数据。当表中即使只有一个分区时，也可以截断该分区。

Ø 截断分区：

ALTER TABLE orders

TRUNCATE PARTITION P2009;

Ø 截断子分区：

ALTER

TABLE orders TRUNCATE SUBPARTITION SYS_SUBP70;

注：hash分区和hash子分区可以truncate。

分区表的管理：分区移动(MOVE)

alter table emp_part move

partition p2 tablespace example;

注：如果是将分区表从一个表空间移动到另外一个表空间，使用下面的语法：

alter table

owner.table_name move partition partition_name [tablespace tablespace_name];

分区表的管理：分区更名(RENAME)

alter table emp_part

rename partition p2 to p1;

分区表的管理：交换表分区(Exchange Partitions)

Ø Exchange Partition提供了一种方式，让我们可以在表与表或分区与分区之间迁移数据，注意不是将表转换成分区或非分区的形式(Oracle_12C提供在线转换功能)。而仅仅只是迁移表中的数据(相互迁移)，由于ORACLE号称此项技术是采用了更改数据字典的方式，因此效率最高(基本不涉及I/O操作)

Ø Exchange

Partition适用于所有分区格式，我们可以将数据从分区表迁移到非分区表，也可以从非分区表迁移到分区表。

alter table tbname1 exchange

partition/subpartition ptname with table tbname2;

注：上面这条命令的with前后没有区别表，执行第一遍是将tbname1的数据交换到tbname2里面，再执行一遍就是将tbname2的数据交换回tbname1。

分区表的管理：INSERT注意

Ø Insert的记录对于list分区表和range分区表来说，必须是在表分区的边界值之内，否则将会报错；

Ø 对于hash分区则没有影响；

Ø 对于间隔分区，超过现在表分区的边界后，会自动根据间隔范围创建新的分区。

分区表的管理：UPDATE注意

在对分区表进行UPDATE时，必须开启行移动功能，否则会报错。

SQL> update orders set