Oracle分区技术总结

一．分区概述：为了简化数据库大数据量的管理，ORACLE推出了分区选项。分区将表或索引分离在若干不同的表空间上，用分而治之的方法来支撑无限膨胀的大表和索引，从而提高大表和索引在物理一级的可管理性。将它们分割成较小的分区可以改善表和分区的维护、备份、恢复、事务及查询性能。

二．分区的特点： u  所有的分区的逻辑属性是相同的，但他们的物理属性可以不同。

u  分区的剪枝 （Partition Pruning）
Oracle server 可以自动识别分区，根据select 语句所指定的选择条件，只查询有用的分区。如果语句的条件中对分区字段使用了函数，优化器则不能进行分区剪枝，但to_date函数除外。

u  分区的优点
(1) 高可用性：如果表的一个分区由于系统故障而不能使用，表的其余好的分区仍然可以使用；
(2) 减少关闭时间：如果系统故障只影响表的一部分分区，那么只有这部分分区需     要修复，故能比整个大表修复花的时间更少；
(3) 维护轻松：对于大型的历史数据表，将其分区，分别管理和方便地添加和删除。；
(4) 均衡I/O:可以把表的不同分区分配到不同的磁盘来平衡I/O改善性能；
(5) 改善性能：对大表的查询、增加、修改等操作可以分解到表的不同分区来并行执行，可使运行速度更快；
(6) 基于分区的 join 操作，会提高查询性能
(7) 分区对用户透明，最终用户感觉不到分区的存在。　　

三．分区的方法： u  Range Partitioning (范围分区)

范围分区就是对数据表中的某个值的范围进行分区，根据某个值的范围，决定将该数据存储在哪个分区上。如根据城市分区，根据时间进行分区等。实现方法就是在CREATE TABLE命令中增加 PARTITION BY RANGE子句。

例如：CREATE TABLE UNITELE.BB_ACCOUNT_INFO_T

(

  ACCOUNT_ID          NUMBER(10),

  CITY_CODE           VARCHAR2(8 BYTE)          NOT NULL,

  CUSTOMER_ID         NUMBER(10)                NOT NULL,

  MAIL_SERVICE        NUMBER(4),

  UNIT_COUNT          NUMBER(8)                 DEFAULT 0,

  REMARK              VARCHAR2(256 BYTE),

  IF_VALID            NUMBER(2)                 DEFAULT 1,

  ACCOUNT_FAVOUR_ID   NUMBER(8)       DEFAULT 0     NOT NULL

)                                                            

TABLESPACE TS_TAB_BASE

PARTITION BY RANGE (CITY_CODE)

( 

  PARTITION PART840 VALUES LESS THAN ('841'), 

  PARTITION PART_OTHER VALUES LESS THAN (MAXVALUE)

);

分区的字段可以是一个列，也可以是多个列。

★ 范围分区的特点

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

b、  数据管理能力强，可以进行数据迁移，数据备份以及数据交换的操作。

c、  范围分区的数据可能分布不均匀。

d、  范围分区与记录值相关，实施难度和可维护性相对较差。有可能出现一个表分成上万个分区，还可能出现后期拆分分区，增加分区的操作。

u  Hash Partitioning（散列分区）;

散列分区为通过指定分区编号来均匀分布数据的一种分区类型，因为通过在I/O设备上进行散列分区，使得这些分区大小一致。在不知道一个范围内有多少数据的情况下使用散列分区可能更合适一些。例如：

CREATE TABLE PARTITION_TEST

 (ID NUMBER,

  NAME VARCHAR2 (60))

  PARTITION BY HASH (ID) PARTITIONS 4

  STORE IN (GEAR1, GEAR2, GEAR3, GEAR4);

★ 散列分区的特点

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

b、易于实施。

c、总体性能最佳。

d、适合于静态数据。

e、数据存储均匀。

f、对数据值无法控制，数据管理能力弱

u  List Partitioning（列表分区）

如果已知将要存储在列中的所有的值，那么可以使用列表分区。列表分区对每一行怎么对应到一个分区给出了很明确的显示。而范围分区则控制的是一个值得范围如果对应到一个分区上。列表分区可以通过离散值较好的管理分区。

SQL> CREATE TABLE LOCATIONS

  2    (LOCATION_ID, STREET_ADDRESS,

  3       POSTAL_CODE, CITY, STATE_PROVINCE,

  4       COUNTRY_ID)

  5    STORAGE(INITIAL 10K NEXT 20K)

  6    TABLESPACE USERS

  7    PARTITION BY LIST (STATE_PROVINCE)

  8    (PARTITION REGION_EAST

  9        VALUES('MA','NY','CT','ME','MD'),

 10     PARTITION REGION_WEST

 11        VALUES('CA','AZ','NM','OR','WA'),

 12     PARTITION REGION_SOUTH

 13        VALUES('TX','KY','TN','LA','MS'),

 14     PARTITION REGION_CENTRAL

 15        VALUES('OH','ND','SD','MO','IL'));

★ 列表分区特点

a、  List分区通过对分区字段的离散值进行分区。

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

c、  List分区只支持单个字段。

d、  List分区具有与范围分区相似的优缺点

–  数据管理能力强

–  数据可能不均匀

–  与记录值相关，实施难度和可维护性相对较差

u  Composite Partitioning（复合分区）;

复合分区支持Range-Hash,Range-List。只有子分区是从物理上创建在标空间上的。分区只是逻辑上的表示。

例如：

CREATE TABLE EMP

(DEPTNO NUMBER,

EMPNAME VARCHAR(32),

GRADE NUMBER)

PARTITION BY RANGE(DEPTNO)

SUBPARTITION BY HASH(EMPNAME) SUBPARTITIONS 8

STORE IN (TS1, TS3, TS5, TS7)

 (PARTITION P1 VALUES LESS THAN (1000),

  PARTITION P2 VALUES LESS THAN (2000)

STORE IN (TS2, TS4, TS6, TS8),

PARTITION P3 VALUES LESS THAN (MAXVALUE)

  (SUBPARTITION P3_S1 TABLESPACE TS4,

SUBPARTITION P3_S2 TABLESPACE TS5));

★ 复合分区的特点

a.Oracle支持的Composite分区（Range-Hash,Range-List）

b.既适合于历史数据，又适合于数据均匀分布

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

四．分区的管理：     对分区表的维护和管理包括如下操作：

1.增加分区。
ALTER TABLE TABLE_NAME ADD PARTITION P_NEW1 VALUES LESS THAN (200409) TABLESPACE TS_NEW;

2.删除不必要的分区。
ALTER TABLE TABLE_NAME DROP PARTITION PART_OLD1;

3.分区合并。
ALTER TABLE TABLE_NAME  MERGE PARTITIONS P_NEW1, P_NEW2 INTO PARTITION P_ALL

4.拆分分区。
ALTER TABLE TABLE_NAME SPLIT PARTITION P_200409 AT (200409) INTO (PARTITION P_200409_1 TABLESPACE TS_ZIKEN, PARTITION P_200409_2 TABLESPACE TS_ZIKEN_IDX);

5.分区改名。
ALTER TABLE TABLE_NAME RENAME PARTITION P_1 TO P_2;

6.将分区改表空间。

ALTER TABLE BILL_MONTHFEE_ZERO MOVE PARTITION P_200409 TABLESPACE TS_NEW;

7.分区的EXPORT。
EXP SALES/SALES_PASSWORD TABLES=BB_SERVICE_RELATION_T:PART_089 ROWS=Y

8.分区的IMPORT。
IMP SALES/SALES_PASSWORD FILE =SALES1999_Q1.DMP TABLES = (BB_SERVICE_RELATION_T:PART_089) IGNORE=Y

9.表的分区信息，可查看数据字典USER_EXTENTS。
SELECT * FROM USER_EXTENTS WHERE SEGMENT_NAME=’BB_SERVICE_RELATION_T’;

10．表的清空。
ALTER TABLE TABLE_NAME TRUNCATE PARTITION P_1;

11.分区的交换。
ALTER TABLE T_NEW EXCHANGE PARTITION P1 WITH TABLE T;

五．索引的分区：和表一样，为了获得更好的可管理性和性能，也可以对索引进行分区。

已分区的表可以有分区或未分区的索引，已分区或未分区的表都可以创建已分区索引。

当索引的分区对应于表分区时（等分的分区）

可以创建以下三种类型的分区索引：

1.本地前缀索引（Local prefixed）

分区关键字是索引前导列（索引最左边的列）的本地索引。

2.本地非前缀索引（Local non-prefixed）

分区关键字列不是前导列，但是索引是本地的。

3.全局前缀索引（Global prefixed）

按照分区关键字顺序作为前导列的全局索引。

创建在分区表上的位图索引必须是本地索引，在未分区的表上不能创建分区位图索引。

在单表查询中，本地非前缀索引可能增加可用性，也更加实用。例如表T(a, b)按a区间分区，若在b上建立本地索引，则当某个分区离线，仅查询b的某个值时，该索引可用，而索引(a, b)不可用；删除索引(a, b)，查询(a, b)的某对值，b上的索引仍可用。此时若建立索引(b, a)，则可应对各类查询。

在多表关联时，系统可能会发现代价较高而不会用到本地非前缀索引（如上例中(b,a)）。因此建立本地索引时应当考虑通常的使用环境。

无法基于本地非前缀索引建立唯一键或主键。

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25516289/viewspace-710299/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25516289/viewspace-710299/