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、范围分区

使用列值的范围来确定一个数据行被插入到那个分区中。

1）分区键最多可16列，一个表最多可65636 个分区。

2）分区范围中不能有任何空隙。

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

4）lessthen子句指定的值，必须是一个用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_2000 VALUES LESS THAN(to_date('2001-1-1','yyyy-mm-dd'))

TABLESPACE users,

PARTITION p_2001 VALUES LESS THAN(to_date('2002-1-1','yyyy-mm-dd')),

PARTITION p_other VALUES LESS THAN(MAXVALUE)

)

SQL> select * from myrangepartition(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 fromscott.emp

where ename='KING';

C、散列分区

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

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

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

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、组合分区

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

1）分区是逻辑结构，表数据被存储在子分区级上。

2）组合分区对：分区级的、历史的、日期的，查询十分好使。

3）组合分区对子分区级的并行操作也十分有用。

4）分区级的连接操作通过使用组合的局部索引支持。

create table myrh

(empno number(4),

ename varchar2(13),

hiredate date,

sale number(10))

PARTITION BY RANGE (hiredate)

SUBPARTITION BY HASH (empno)

SUBPARTITIONS 2

STORE IN (users, users)

(

PARTITION p_2000 VALUES LESS THAN

(to_date('2001-1-1','yyyy-mm-dd')),

PARTITION p_2001 VALUES LESS THAN

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

PARTITION p_other VALUES LESS THAN(MAXVALUE)

)

三、索引

<一>、索引分区

分两类：全局与局部索引，前缀与非前缀索引。

全局与局部索引：与该索引的分区结构是否与被索引基础表结构匹配有关系。

局部索引：索引中的分区与基础表的分区逐个匹配，也称分区索引。即

1. 分区索引就是在所有每个区上单独创建索引，它能自动维护

2. 在drop或truncate 某个分区时不影响该索引的其他分区索引的使用，也就是索引不会失效，维护起来比较方便，但是在查询性能稍微有点影响

3. 可以在任何分区上创建局部分区索引

4. 创建后，Oracle自动维护表分区与索引分区之间的关系

5. 分区上的位图索引必须是局部的索引

举例：

create index myemp_idx_t on myemp(hiredate)

local

(

partition idx_1 tablespace idxtbs1,

partition idx_2 tablespace idxtbs2,

partition idx_3 tablespace idxtbs3

);

全局索引：索引中的分区与基础表的分区数不逐个匹配。即：

1、全局索引就是在全表上创建索引

2、它可以创建自己的分区，可以和分区表的分区不一样，也就是它是独立的索引。

3、在drop或truncate 某个分区时需要重建索引alterindex idx_xxrebuild，

4、也可以altertable table_name drop partition partition_name updateglobal indexes;实现，但是要花很长时间在重建索引上。

5、可以通过查询user_indexes、user_part_indexes 和user_ind_partitions视图来查看索引是否有效。

6、可以在任何分区上创建全局分区索引，但它自身必须是范围的。

7、最高分区必须以MAXVALUE参数来定义。

8、使用global创建与表分区一样多的索引，Oracle 也不会认为是局部索引。

举例：

create index myemp_idx_t on myemp()

global partition by range(sal)

(

partition idx_1 values less than (1000)tablespace idxtbs1,

partition idx_2 values less than (5000)tablespace idxtbs2,

partition idx_3 values less than (maxvalue)tablespace idxtbs3

);

global 子句允许指定索引的范围值，这个范围值为索引字段的范围值.

create index my_idx_t on dinya_test(empno);

<二>、前缀与非前缀索引：

与该索引是否有分区键及分区键出现在索引结构内的位置有关系。

1、前缀索引：索引的最左列与分区键相同。可以是唯一的，也可以不是唯一的。

前缀局部索引：

create index myrange_lp_idx on myrange (hiredate) local;

此时默认与基础表分区数一样。以hiredate范围分区(这里hiredate 是分区

键)。

前缀全局索引：

略。

2、非前缀索引：

索引的最左列与分区键不相同。可以是唯一的，也可以不是唯一的。

非前缀局部索引：(不存在全局非前缀索引)

SQL>create index myrange_lp_idx onmyrange (empno) local;

分区索引不能作为整体一次重建。

alter indexlocal2_alarminforebuild partitionl_ind2_alarminfo_050915;

注：局部前缀索引比局部非前缀索引具有更好的性能，因为它们将检查的索引

的数目最小化。

注：在单表查询中，局部非前缀索引可能增加可用性，也更加实用。例如表T

（a， b）按a 区间分区，若在b 上建立本地索引，则当某个分区离线，仅查询

b 的某个值时，该索引可用，而索引（a， b）不可用；

举例：

create table partitioned_table

(a int,

b int

)

partition by range(a)

(

partition part_1 values less than (2),

partition part_2 values less than (3)

);

SQL> create index local_prefixed onpartitioned_table(a, b) local;

SQL> create index local_nonprefixed onpartitioned_table(b) local;

四、管理分区

收集分区的统计数据

可以针对段、分区或子分区级上收集。

例（收集分区表和分区索引）：

SQL>execute

dbms_stats.gather_table_stats(ownname=>’APPS’,tabname=>’STUDENT_H

ISTORY’,partname=>’P_1998’,granularity=>’PARTITION’);

SQL>execute

dbms_stats.gather_index_stats(ownname=>’SYS’,indname=>’STUDENT_HI

STORY_GP_IDX’,partname=>’P_200n’);

查询分区信息的视图

DBA_IND_PARTITIONS

DBA_IND_SUBPARTITIONS

DBA_LOB_PARTITIONS

DBA_LOB_SUBPARTITIONS

DBA_PART_COL_STATISTICS

DBA_PART_HISTOGRAMS

DBA_PART_INDEXES

DBA_PART_KEY_COLUMNS

DBA_PART_LOBS

DBA_PART_TABLES

DBA_SUBPART_COL_STATISTICS

DBA_SUBPART_HISTOGRAMS

DBA_SUBPART_KEY_COLUMNS

DBA_TAB_PARTITIONS

DBA_TAB_SUBPARTITIONS

增加分区

例：给范围分区增加分区

create table myrange

(empno number(4),

ename varchar2(13),

hiredate date,

sale number(10))

PARTITION BY RANGE (hiredate)

(PARTITION p_2000 VALUES LESS THAN(to_date('2001-1-1','yyyy-mm-dd'))

TABLESPACE users,

PARTITION p_2001 VALUES LESS THAN(to_date('2002-1-1','yyyy-mm-dd'))

);

alter table myrange add partition p_2002values less

than(to_date('2003-1-1','yyyy-mm-dd'))tablespace mytbs1

注意：可以不断的增加分区，但values lessthan ()的值不能比已经存在的分区的值小。可以用maxvalue关键字指定列最大值。

truncate分区

例：

alter table myrange truncate partitionp_2001;

将分区数据清空。不同于delete。

丢弃分区

例：

alter table myrange drop partition p_2001;

将丢弃此分区的数据，不会影响其他分区。

交换分区

（可应用于大量数据插入中，先把数据临时插到一个临时表，再把该临时表与

一个分区交换，需要考虑执行时间，未测试）

注意：

1）是将某一个分区于某一个表的数据交换。

2）临时表结构和分区表结构必须一样。

3）临时表的数据必须符合分区规则。

4）若分区里有局部分区索引，此时将不可用，必须重建。（除数据外，包括索

引一起交换的除外---includeingindexes）

例：创建分区表

create table mytest

(empno number(4),

ename varchar2(10),

hiredate date,

sale number(7,2))

PARTITION BY RANGE (hiredate)

(PARTITION p_2000 VALUES LESS THAN (to_date('2001-1-1','yyyy-mm-dd'))

TABLESPACE users,

PARTITION p_2001 VALUES LESS THAN(to_date('2002-1-1','yyyy-mm-dd')),

PARTITION p_other VALUES LESS THAN(MAXVALUE)

)

创建普通临时表

create table mytemp as selectempno,ename,hiredate,sal sale from emp

where 1=2;

插入数据

insert into mytemp selectempno,ename,hiredate,sal from emp;

交换分区

alter table mytest exchange partition

p_2000 with table mytemp

移动分区

例：

SQL>alter table myrangemovepartition p_2001 tablespace mytbs2;

注意：此时索引需要重建。

修改分区

例：

SQL>alter table myrangemodifypartition p_2000 storage(pctincrease 20);

修改分区存储参数。

重命名分区

例：

SQL>alter table myrangerenamepartition p_oldname to p_newname;

分割分区

例：

创建一个分区

SQL>alter table C_PERF_CELLCIRCUIT

add partition part25 VALUES LESS THAN 25

tablespace D_PF_CELL_DBS25;

分割原来的分区

SQL>alter table C_PERF_CELLCIRCUIT

split partition part26

at (25)

into (partition part25,

(partition part26);

前一个分区小于at 指定的值，后一个分区大于at 指定的值。

At 指定的值不能等于本分区和上一个分区的上限值。

如果被分割的分区有数据，则local索引需要重建。

分区的逻辑备份与恢复

%exp username/passwd file=xxxx.dmptables=( p_nor_mcbtss2:

p_nor_mcbtss2_part04, p_nor_mcbtss2:p_nor_mcbtss2_part05)

%imp username/passwd file=xxxx.dmptables=( p_nor_mcbtss2:

p_nor_mcbtss2_part04, p_nor_mcbtss2:p_nor_mcbtss2_part05) ignore=y