Oracle分区的5W+H

前段时间使用oracle分区功能并参考了一篇英文文档,现在与大家分享一下,有些是直接翻译的,英文水平不高,见笑!

Oracle分区的5W+H

说明:

5W:what,why,whern,who,where

H:how

 

分区使得表和索引或者索引表再细分成更小的易管理的片,将这些细分的每个小片称为”分区”.从”应用程序设计”的观点看,分区表与非分区表是没有区别的,如果应用程序开始对一个非分区表进行写操作,它没有必要去访问一个分区表.

 

Oracle在8.0版本中引进了分区功能,并支持关键字范围分区,稍后再详细介绍.随后oracle8i中的”Hash和复合分区”与oracle9i中的”列表分区”,并与其他一些特点被相继引进.其中任何一种分区方法都有其优点和缺点,根据数据和应用程序的类型来决定采用那种分区方式,同时也可以修改,重命名,移动,添加,删除,截取,拆分分区.现在就来详细说明.

表中使用分区的优点

 

1.     更小,更多易管理的数据片(分区)

2.     减少恢复时间

3.     减小失败影响

4.     能够在”分区级别”实现import / export功能

5.     快速访问数据

6.     分区间的独立工作

7.     使用非常简单

 

分区方法类型

 

1.     范围分区

此分区类型基于”列范围”值创建分区,主要通过限制分区范围来定义每个分区,”范围分区” 一般情况下采用的值是数据表中的时间字段.假设存在一个SAMPLE_ORDERS数据表,并带有ORDER_DATE字段,并假设该表存在5年的历史记录.接下来可以依据假设的时间和方面(依据实际因素设定)来创建分区.

这样每个方面的数据都成为SAMPLE_ORDER表的一个分区.第一个分区将是最低限度的,最后一个将是最高限度的.据此,如果我们查询1999范围的数据,就可以直接查询第一个分区(实际划分的1999)

以下语法实现了范围分区的创建

 

CREATE TABLE SAMPLE_ORDERS
(ORDER_NUMBER NUMBER,
ORDER_DATE DATE,
CUST_NUM NUMBER,
TOTAL_PRICE NUMBER,
TOTAL_TAX NUMBER,
TOTAL_SHIPPING NUMBER)
PARTITION BY RANGE(ORDER_DATE)
(
PARTITION SO99Q1 VALUES LESS THAN TO_DATE(‘01-APR-1999’, ‘DD-MON-YYYY’),
PARTITION SO99Q2 VALUES LESS THAN TO_DATE(‘01-JUL-1999’, ‘DD-MON-YYYY’),
PARTITION SO99Q3 VALUES LESS THAN TO_DATE(‘01-OCT-1999’, ‘DD-MON-YYYY’),
PARTITION SO99Q4 VALUES LESS THAN TO_DATE(‘01-JAN-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q1 VALUES LESS THAN TO_DATE(‘01-APR-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q2 VALUES LESS THAN TO_DATE(‘01-JUL-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q3 VALUES LESS THAN TO_DATE(‘01-OCT-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q4 VALUES LESS THAN TO_DATE(‘01-JAN-2001’, ‘DD-MON-YYYY’)
)
;

上述例子主要创建了8个基于SAMPLE_ORDERS表的分区,所有分区都对应一个方面,分区SO99Q1只包含1999方面的orders数据.

 

2.     Hash 分区(散列分区)

 

该分区类型下的表记录根据用于分区的hash’列值进行分区.”Hash”分区与范围分区相比没有任何逻辑意义.列举一例.

CREATE TABLE SAMPLE_ORDERS
(ORDER_NUMBER NUMBER,
ORDER_DATE DATE,
CUST_NUM NUMBER,
TOTAL_PRICE NUMBER,
TOTAL_TAX NUMBER,
TOTAL_SHIPPING NUMBER,
ORDER_ZIP_CODE)
PARTITION BY HASH (ORDER_ZIP_CODE)
(PARTITION P1_ZIP TABLESPACE TS01, 
PARTITION P2_ZIP TABLESPACE TS02,
PARTITION P3_ZIP TABLESPACE TS03, 
PARTITION P4_ZIP TABLESPACE TS04)
ENABLE ROW MOVEMENT;

上述例子依据能表示orders存放位置的zip代码列创建了4个hash分区.

 

3.     列表分区(仅存在9i中)

 

使用该分区类型的表记录根据表中的一列值创建分区,假设存在像’政府’,’亚洲’,’雇员’,’美洲’,’欧洲’这样类别的数据表,把这些类别定义为分区的关键字,就可以创建一个独立的列表分区或者一个类别组分区,例如,’ American-partition’包含所有类似‘American’分区的记录.

CREATE TABLE SAMPLE_ORDERS
(ORDER_NUMBER NUMBER,
ORDER_DATE DATE,
CUST_NUM NUMBER,
TOTAL_PRICE NUMBER,
TOTAL_TAX NUMBER,
TOTAL_SHIPPING NUMBER,
SHIP_TO_ZIP_CODE,
SHIP_TO_STATE)
PARTITION BY LIST (SHIP_TO_STATE)
(PARTITION SHIP_TO_ARIZONA VALUES (‘AZ’) TABLESPACE TS01, 
PARTITION SHIP_TO_CALIFORNIA VALUES (‘CA’) TABLESPACE TS02, 
PARTITION SHIP_TO_ILLINOIS VALUES (‘IL’) TABLESPACE TS03, 
PARTITION SHIP_TO_MASACHUSETTES VALUES (‘MA’) TABLESPACE TS04, 
PARTITION SHIP_TO_MICHIGAN VALUES (‘MI’) TABLESPACE TS05)
ENABLE ROW MOVEMENT;

上述例子创建了一组基于SHIP_TO_STATE的列表分区,并为每个分区指定了不同的表空间.

 

4.Range-Hash 复合分区

这是一种范围与哈希的合成分区方式,由此看来,首先将数据进行范围分区,然后利用哈希关键值对数据进一步进行哈希分区.所有的子分区都对应数据的一个逻辑子集.

再次修改上面的例子:

CREATE TABLE SAMPLE_ORDERS
(ORDER_NUMBER NUMBER,
ORDER_DATE DATE,
CUST_NUM NUMBER,
CUST_NAME VARCAHR2, 
TOTAL_PRICE NUMBER,
TOTAL_TAX NUMBER,
TOTAL_SHIPPING NUMBER,
SHIP_TO_ZIP_CODE,
SHIP_TO_STATE)
TABLESPACE USERS
PARTITION BY RANGE (ORDER_DATE) 
SUBPARTITION BY HASH(CUST_NAME) 
SUBPARTITION TEMPLATE(
(SUBPARTITION SHIP_TO_ARIZONA VALUES (‘AZ’) TABLESPACE TS01, 
SUBPARTITION SHIP_TO_CALIFORNIA VALUES (‘CA’) TABLESPACE TS02, 
SUBPARTITION SHIP_TO_ILLINOIS VALUES (‘IL’) TABLESPACE TS03, 
SUBPARTITION SHIP_TO_NORTHEAST VALUES (‘MA’, ‘NY’, ‘NJ’) TABLESPACE TS04, 
SUBPARTITION SHIP_TO_MICHIGAN VALUES (‘MI’) TABLESPACE TS05)
(
PARTITION SO99Q1 VALUES LESS THAN TO_DATE(‘01-APR-1999’, ‘DD-MON-YYYY’),
PARTITION SO99Q2 VALUES LESS THAN TO_DATE(‘01-JUL-1999’, ‘DD-MON-YYYY’),
PARTITION SO99Q3 VALUES LESS THAN TO_DATE(‘01-OCT-1999’, ‘DD-MON-YYYY’),
PARTITION SO99Q4 VALUES LESS THAN TO_DATE(‘01-JAN-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q1 VALUES LESS THAN TO_DATE(‘01-APR-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q2 VALUES LESS THAN TO_DATE(‘01-JUL-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q3 VALUES LESS THAN TO_DATE(‘01-OCT-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q4 VALUES LESS THAN TO_DATE(‘01-JAN-2001’, ‘DD-MON-YYYY’)
)
ENABLE ROW MOVEMENT;

上面的例子说明每个范围分区都被列表分区又分成的更小的分区. SHIP_TO_ARIZONA是一个基于AZ列表值的子分区.这个分区又用于表示SO99Q1范围主分区等等.

5.Range-List 复合分区(仅存在9i中)

也可以说是一种合成分区,首先将数据进行范围分区,然后在根据列表关键值对这些范围分区进一步做列表分区,每个子分区都单独表示数据的一个逻辑子集,它不同于Range-Hash分区.

由索引组成的表可以使用Range或者Hash进行分区.

再次修改上面的分区.

CREATE TABLE SAMPLE_ORDERS
(ORDER_NUMBER NUMBER,
ORDER_DATE DATE,
CUST_NUM NUMBER,
CUST_NAME VARCAHR2, 
TOTAL_PRICE NUMBER,
TOTAL_TAX NUMBER,
TOTAL_SHIPPING NUMBER,
SHIP_TO_ZIP_CODE,
SHIP_TO_STATE)
TABLESPACE USERS
PARTITION BY RANGE (ORDER_DATE) 
SUBPARTITION BY LIST(SHIP_TO_STATE) 
SUBPARTITION TEMPLATE(
SUBPARTITION SP1 TABLESPACE TS01,
SUBPARTITION SP2 TABLESPACE TS02,
SUBPARTITION SP3 TABLESPACE TS03,
SUBPARTITION SP4 TABLESPACE TS04,
SUBPARTITION SP5 TABLESPACE TS05)
(
PARTITION SO99Q1 VALUES LESS THAN TO_DATE(‘01-APR-1999’, ‘DD-MON-YYYY’),
PARTITION SO99Q2 VALUES LESS THAN TO_DATE(‘01-JUL-1999’, ‘DD-MON-YYYY’),
PARTITION SO99Q3 VALUES LESS THAN TO_DATE(‘01-OCT-1999’, ‘DD-MON-YYYY’),
PARTITION SO99Q4 VALUES LESS THAN TO_DATE(‘01-JAN-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q1 VALUES LESS THAN TO_DATE(‘01-APR-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q2 VALUES LESS THAN TO_DATE(‘01-JUL-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q3 VALUES LESS THAN TO_DATE(‘01-OCT-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q4 VALUES LESS THAN TO_DATE(‘01-JAN-2001’, ‘DD-MON-YYYY’)
)
ENABLE ROW MOVEMENT;

在oracle9i中,还可以分区上创建索引.如下:

a.     本地索引

本地索引是在分区表中存在的相同方式的索引.每个本地索引分区只对应一个分区.

b.全局分区索引

在分区表或非分区表上都能够创建全局分区索引,但现在我们只能使用” 范围分区” ,例如上面将表分成标识一个方面(“Global Index”)的例子中,可以被使用不同的分区关键字和分区号码创建一个全局索引.

c.全局非分区索引

全局非分区索引与非分区表上的普通索引没有区别.它的结构并没有被分区.