oracle分区应用详解

分区应用详解

一、概述：

1、分区：将表数据划分成更小的子集。

经验：在一个表的数据超过2000万条或占用2G 空间时，建议建立分区表。2、好处：1）提高可用性。

如100GB 的表，如果划分成2G 的50个分区，只要查询的数据所处的分区online，而其他的分区可以是offline，数据库依然工作正常。2）减轻管理负担。

对分区数据的维护，比如删除、更新、分析、数据恢复等，可以以分区为单位进行维护。

3）提高DML 和查询操作性能。

利用CBO 的分区排除机制，跳过未含有相关数据的表分区，减少I/O。分区表使用CBO，CBO 具有很好的“分区意识能力”，直接排除不属于查询结果的分区。（注：分区键列上同样不能应用函数，否则不会使用索引）

对于分区的表，应尽可能的利用分区键作为查询条件，不然会执行full table scan，会比不分区的全表扫描花费更多的时间，失去分区的意义。3、分区信息可查询：select *from user_tab_partitions;

（比如小区表，可以按小时分区，小时里再按hash 分区防止分区不均匀，此时再对各分区进行truncate 等维护，可提高性能）

二、操作：

1、创建分区

分区可分为如下几种：（范围分区，hash 分区，列表分区，范围-hash 分区，范围-列表分区等）

A

l l R i g

h

t s

R e

s

e r v

e

d

A、范围分区

使用列值的范围来确定一个数据行被插入到那个分区中。1）分区键最多可16列，一个表最多可65636个分区。2）分区范围中不能有任何空隙。

3）每个分区中，等于上限的值都是存储在上一个分区中的。

4）less then 子句指定的值，必须是一个用RPAD 函数填充过的值、日期（必须包含世纪）或常数。

5）分区表不能包含带有long,long raw 的数据列。

6）试图插入一个无法放入任一分区的数据，都导致ORA-14400错误。7）通过更新数据使数据在分区间移动不被允许，除非创建表的时候指定了enable row movement 子局。

注：如果插入的数据导致分区不均匀，应考虑使用散列分区。例：

create table myrange (empno number(4),ename varchar2(13),hiredate date,

sale number(10))

PARTITION BY RANGE (hiredate)

(PARTITION p_2000VALUES LESS THAN (to_date('2001-1-1','yyyy-mm-dd'))TABLESPACE users,

PARTITION p_2001VALUES LESS THAN (to_date('2002-1-1','yyyy-mm-dd')),PARTITION p_other VALUES LESS THAN (MAXVALUE))

A

l l R i g

h

t s

R e

s

e r v

e

d

SQL>select *from myrange partition(p_2000);

B、列表分区

类似范围分区，区别是它基于一组指定的值，范围分区是基于一个范围。试图插入一个无法放入任一分区的数据，都导致ORA-14400错误。create table mylist (empno number(4),ename varchar2(13),hiredate date,sale number(10))

PARTITION BY LIST (ename)

(PARTITION p_manager VALUES ('KING','CLARK','SCOTT'),PARTITION p_boss VALUES ('TINA'),

PARTITION p_CLARK VALUES ('SMITH','ALLEN','WARD','JONES','MARTIN'))

insert into mylist

select empno,ename,hiredate,sal from scott.emp

where ename='KING';

C、散列分区

使用一个散列算法来分配数据被插入到那个分区中，一般分配很均匀，对于序数的列很适合。

1）分区键应具有很高基数。

2）如果是唯一键分区，效果最好。如果是范围查找，不会有性能提升。

A

l l R i g

h

t s

R e

s

e r v

e

d

3）通过更新数据使数据在分区间移动不被允许。4）总分区数，应该是2的整数倍。

举例：

create table myhash1(empno number(4),ename varchar2(13),hiredate date)

PARTITION BY HASH (empno)(PARTITION h1,PARTITION h2,PARTITION h3)

create table myhash2(empno number(4),

ename varchar2(13),hiredate date)

PARTITION BY HASH (empno)PARTITIONS 3

STORE(USERS，USERS，USERS)

D、组合分区

创建范围分区，在该分区内又再创建散列分区。（一般用在需要范围分区，而范围分区又不均匀的情况下）

A

l l R i g

h

t s

R e

s

e r v

e

d