深入学习分区表及分区索引(详解oracle分区)

下载的，写的非常好，给大家分享下。

什么时候使用分区：

1、大数据量的表，比如大于2GB。一方面2GB文件对于32位os是一个上限，另外备份时间长。

2、包括历史数据的表，比如最新的数据放入到最新的分区中。典型的例子：历史表，只有当前月份的数据可以被修改，而其他月份只能read-only

ORACLE只支持以下分区：tables, indexes on tables, materialized views, and indexes on materialized views

分区对SQL和DML是透明的(应用程序不必知道已经作了分区)，但是DDL

可以对不同的分区进行管理。

不同的分区之间必须有相同的逻辑属性，比如共同的表名，列名，数据类型，约束;

但是可以有不同的物理属性，比如pctfree, pctused, and tablespaces.

分区独立性：即使某些分区不可用，其他分区仍然可用。

最多可以分成64000个分区，但是具有LONG or LONG RAW列的表不可以，但是有CLOB or BLOB列的表可以。

可以不用to_date函数，比如：

alter session set nls_date_format='mm/dd/yyyy';

CREATE TABLE sales_range

(salesman_id NUMBER(5),

salesman_name VARCHAR2(30),

sales_amount NUMBER(10),

sales_date DATE)

PARTITION BY RANGE(sales_date)

(

PARTITION sales_jan2000 VALUES LESS THAN('02/01/2000'),

PARTITION sales_feb2000 VALUES LESS THAN('03/01/2000'), PARTITION sales_mar2000 VALUES LESS THAN('04/01/2000'), PARTITION sales_apr2000 VALUES LESS THAN('05/01/2000') );

Partition Key：最多16个columns,可以是nullable的

非分区的表可以有分区或者非分区的索引;

分区表可以有分区或者非分区的索引;

Partitioning 方法：

Range Partitioning

List Partitioning

Hash Partitioning

Composite Partitioning

Composite Partitioning:组合，以及 range-hash and range-list composite partitioning

Range Partitioning：

每个分区都有VALUES LESS THAN子句，表示这个分区小于(<)某个上限，而大于等于(>=)前一个分区的VALUES LESS THAN值。

MAXVALUE定义最高的分区，他表示一个虚拟的无限大的值。这个分区包括null值。

CREATE TABLE sales_range

(salesman_id NUMBER(5),

salesman_name VARCHAR2(30),

sales_amount NUMBER(10),

sales_date DATE)

PARTITION BY RANGE(sales_date)

(

PARTITION sales_jan2000 VALUES LESS

THAN(TO_DATE('01/02/2000','DD/MM/YYYY')),

PARTITION sales_feb2000 VALUES LESS

THAN(TO_DATE('01/03/2000','DD/MM/YYYY')),

PARTITION sales_mar2000 VALUES LESS

THAN(TO_DATE('01/04/2000','DD/MM/YYYY')),

PARTITION sales_apr2000 VALUES LESS

THAN(TO_DATE('01/05/2000','DD/MM/YYYY')),

PARTITION sales_2000 VALUES LESS THAN(MAXVALUE)

);

插入数据：

Insert into sales_range

values(1,2,3,to_date('21-04-2000','DD-MM-YYYY'));

Insert into sales_range values(1,2,3,sysdate);

选择数据：

select * from sales_range;

select * from sales_range partition(sales_apr2000);

select * from sales_range partition(sales_mar2000);

select * from sales_range partition(sales_2000);

按照多个列分区：

CREATE TABLE sales_range1

(salesman_id NUMBER(5),

salesman_name VARCHAR2(30),

sales_date DATE)

PARTITION BY RANGE(sales_date, sales_amount)

(

PARTITION sales_jan2000 VALUES LESS

THAN(TO_DATE('01/02/2000','DD/MM/YYYY'),1000),

PARTITION sales_feb2000 VALUES LESS

THAN(TO_DATE('01/03/2000','DD/MM/YYYY'),2000),

PARTITION sales_mar2000 VALUES LESS

THAN(TO_DATE('01/04/2000','DD/MM/YYYY'),3000),

PARTITION sales_apr2000 VALUES LESS

THAN(TO_DATE('01/05/2000','DD/MM/YYYY'),4000),

PARTITION sales_2000 VALUES LESS THAN(MAXVALUE, MAXVALUE) );

Insert into sales_range1 values(1,2,500,

TO_DATE('21/01/2000','DD/MM/YYYY'));

Insert into sales_range1 values(2,3,1500, sysdate);

如果多个分区列的值冲突，则按照从左到右的优先级。

List Partitioning：

可以组织无序的，或者没有关系的数据在相同的分区。

不支持多列的(multicolumn) partition keys，只能是一个列。

DEFAULT表示不满足条件的都放在这个分区。

CREATE TABLE sales_list

(salesman_id NUMBER(5),

salesman_name VARCHAR2(30),

sales_state VARCHAR2(20),

sales_date DATE)

PARTITION BY LIST(sales_state)

(

PARTITION sales_west VALUES('California', 'Hawaii'),

PARTITION sales_east VALUES ('New York', 'Virginia', 'Florida'),

PARTITION sales_central VALUES('Texas', 'Illinois'),

PARTITION sales_other VALUES(DEFAULT)

);

Hash Partitioning：

不可以作splitting, dropping or merging操作。但是可以added and coalesced.

当我们无法判断有多少数据映射或者怎样映射到各个分区时，可以使用这种方法。分区数据最好是2的幂，这样可以平均分配数据。

CREATE TABLE sales_hash1

(salesman_id NUMBER(5),

salesman_name VARCHAR2(30),

sales_amount NUMBER(10),

week_no NUMBER(2))

PARTITION BY HASH(salesman_id)

PARTITIONS 4

STORE IN (users, TOOLS, TEST, TABLESPACE1); --表空间

CREATE TABLE sales_hash

(salesman_id NUMBER(5),

salesman_name VARCHAR2(30),

sales_amount NUMBER(10),

week_no NUMBER(2))

PARTITION BY HASH(salesman_id)

(

PARTITION p1 tablespace users,

PARTITION p2 tablespace system

);

Composite Partitioning:

先按照range分区，每个子分区又按照list or hash分区。

CREATE TABLE sales_composite

(salesman_id NUMBER(5),

salesman_name VARCHAR2(30),

sales_amount NUMBER(10),

sales_date DATE)

PARTITION BY RANGE(sales_date)

SUBPARTITION BY HASH(salesman_id) --子分区

SUBPARTITION TEMPLATE(

SUBPARTITION sp1 TABLESPACE data1,

SUBPARTITION sp2 TABLESPACE data2,

SUBPARTITION sp3 TABLESPACE data3,

SUBPARTITION sp4 TABLESPACE data4)

(PARTITION sales_jan2000 VALUES LESS

THAN(TO_DATE('02/01/2000','DD/MM/YYYY'))

PARTITION sales_feb2000 VALUES LESS

THAN(TO_DATE('03/01/2000','DD/MM/YYYY'))

PARTITION sales_mar2000 VALUES LESS

THAN(TO_DATE('04/01/2000','DD/MM/YYYY'))

PARTITION sales_apr2000 VALUES LESS

THAN(TO_DATE('05/01/2000','DD/MM/YYYY'))

PARTITION sales_may2000 VALUES LESS

THAN(TO_DATE('06/01/2000','DD/MM/YYYY')));

使用TEMPLATE，oracle会这样命名子分区：分区_子分区，比如

sales_jan2000_sp1表示将数据放在data1表空间

Range-list:

CREATE TABLE bimonthly_regional_sales

(deptno NUMBER,

item_no VARCHAR2(20),

txn_date DATE,

txn_amount NUMBER,

state VARCHAR2(2))

PARTITION BY RANGE (txn_date)

SUBPARTITION BY LIST (state)

SUBPARTITION TEMPLATE(

SUBPARTITION east VALUES('NY', 'VA', 'FL') TABLESPACE system,

SUBPARTITION west VALUES('CA', 'OR', 'HI') TABLESPACE users,

SUBPARTITION central VALUES('IL', 'TX', 'MO') TABLESPACE tools)

( PARTITION janfeb_2000 VALUES LESS THAN

(TO_DATE('1-03-2000','DD-Mm-YYYY')), PARTITION marapr_2000 VALUES LESS THAN (TO_DATE('1-05-2000','DD-Mm-YYYY')), PARTITION mayjun_2000 VALUES LESS THAN (TO_DATE('1-07-2000','DD-Mm-YYYY')) );

分区维护操作：

移动分区：

通常是移动到不同的表空间。Move

Alter table sales_hash move partition p2 tablespace users;

(单独移动表也可以，达到整理碎片的效果

Alter table t move tablespace users;)

添加分区：

Alter table t add partition p3 values less than……

只能在已经分区表的最后一个分区之后添加，并且最后一个分区使用特定健值定义，不能是maxvalue.

如果想在中间或开始部分，或者maxvalue后，添加分区，使用split 分裂已有分区。

拆分分区：

ALTER TABLE SALES_RANGE SPLIT PARTITION sales_2000

at (TO_DATE('01/05/2001','DD/MM/YYYY'))

INTO ( PARTITION sales_2000_1, PARTITION sales_2000_2);

相当于：PARTITION sales_2000_1 values less

than(TO_DATE('01/05/2001','DD/MM/YYYY'))

删除分区：

Alter table t drop partition p3;

Alter table t truncate partition p3;

结合分区：

只是对于hash partition, 用来合并并减少一个partition

set line 150

Select segment_name,partition_name from dba_segments

where segment_name=upper(’sales_hash’);

alter table sales_hash coalesce partition;

合并分区：

合并相邻的分区

ALTER TABLE four_seasons

MERGE PARTITIONS quarter_one, quarter_two INTO PARTITION

quarter_two;

交换表分区：

CREATE TABLE sales_range_temp

(salesman_id NUMBER(5),

salesman_name VARCHAR2(30),

sales_amount NUMBER(10),

sales_date DATE);

insert into sales_range_temp values(11,11,11,sysdate);

select * from sales_range partition(sales_2000_2);

alter table sales_range exchange partition sales_2000 with table sales_range_temp;

分区索引：

分为global, local index

local partitioned index:

每个local index对应一个分区。增加和删除分区自动的增加和删除了local index。

Local index可以是全表unique的，条件是partition key必须是index columns的一部分?

CREATE INDEX employees_local_idx ON employees (employee_id) LOCAL;

分区上的位图索引只能建立为local partitioned index,不能是global

Global partitioned index:

索引也分区，但是分几个区，按照什么样的partition key分区跟表没有关系。不能增加分区，可以使用ALTER INDEX SPLIT PARTITION;删除：ALTER INDEX DROP PARTITION;这些操作会使的索引失效，所以建议：

ALTER TABLE DROP PARTITION P1 UPDATE GLOBAL INDEXES

这样保证索引仍然有效，online，不需重建。

CREATE INDEX employees_global_part_idx ON employees(employee_id)

GLOBAL PARTITION BY RANGE(employee_id)

(PARTITION p1 VALUES LESS THAN(5000),

PARTITION p2 VALUES LESS THAN(MAXVALUE));

Global Nonpartitioned Indexes:

怎样提高性能：

Partition Pruning

根据SQL自动选择应该访问哪些必要的分区，partition pruning可以跳过不必要的索引或者表分区或子分区。但是如果SQL对partition columns作了function( to_date除外)，则不会删除分区。

深入学习分区表及分区索引(1)

2008-05-22 18:58

关于分区表和分区索引(About Partitioned Tables and Indexes)对于10gR2而言，基本上可以分成几类：

v Range(范围)分区

v Hash(哈希)分区

v List(列表)分区

v 以及组合分区：Range-Hash,Range-List。

对于表而言(常规意义上的堆组织表)，上述分区形式都可以应用(甚至可以对某个分区指定compress属性)，只不过分区依赖列不能是lob,long之类数据类型，每个表的分区或子分区数的总数不能超过1023个。

对于索引组织表，只能够支持普通分区方式，不支持组合分区，常规表的限制对于索引组织表同样有效，除此之外呢，还有一些其实的限制，比如要求索引组织表的分区依赖列必须是主键才可以等。

注：本篇所有示例仅针对常规表，即堆组织表！

对于索引，需要区分创建的是全局索引，或本地索引：

l 全局索引(global index)：即可以分区，也可以不分区。即可以建range分区，也可以建hash分区，即可建于分区表，又可创建于非分区表上，就是说，全局索引是完全独立的，因此它也需要我们更多的维护操作。

l 本地索引(local index)：其分区形式与表的分区完全相同，依赖列相同，存储属性也相同。对于本地索引，其索引分区的维护自动进行，就是说你

add/drop/split/truncate表的分区时，本地索引会自动维护其索引分区。

Oracle建议如果单个表超过2G就最好对其进行分区，对于大表创建分区的好处是显而易见的，这里不多论述why，而将重点放在when以及how。

WHEN

一、When使用Range分区

Range分区呢是应用范围比较广的表分区方式，它是以列的值的范围来做为分区的划分条件，将记录存放到列值所在的range分区中，比如按照时间划分，2008年1季度的数据放到a分区，08年2季度的数据放到b分区，因此在创建的时候呢，需要你指定基于的列，以及分区的范围值，如果某些记录暂无法预测范围，可以创建maxvalue分区，所有不在指定范围内的记录都会被存储到maxvalue所在分区中，并且支持指定多列做为依赖列，后面在讲how的时候会详细谈到。

二、When使用Hash分区

通常呢，对于那些无法有效划分范围的表，可以使用hash分区，这样对于提高性能还是会有一定的帮助。hash分区会将表中的数据平均分配到你指定的几个分区中，列所在分区是依据分区列的hash值自动分配，因此你并不能控制也不知道哪条记录会被放到哪个分区中，hash分区也可以支持多个依赖列。

三、When使用List分区

List分区与range分区和hash分区都有类似之处，该分区与range分区类似的是也需要你指定列的值，但这又不同与range分区的范围式列值---其分区值必须明确指定，也不同与hash分区---通过明确指定分区值，你能控制记录存储在哪个分区。它的分区列只能有一个，而不能像range或者hash分区那样同时指定多个列做为分区依赖列，不过呢，它的单个分区对应值可以是多个。

你在分区时必须确定分区列可能存在的值，一旦插入的列值不在分区范围内，则插入/更新就会失败，因此通常建议使用list分区时，要创建一个default分区存储那些不在指定范围内的记录，类似range分区中的maxvalue分区。

四、When使用组合分区

如果某表按照某列分区之后，仍然较大，或者是一些其它的需求，还可以通过分区内再建子分区的方式将分区再分区，即组合分区的方式。

组合分区呢在10g中有两种：range-hash，range-list。注意顺序哟，根分区只能是range分区，子分区可以是hash分区或list分区。

提示：11g在组合分区功能这块有所增强，又推出了range-range,list-range,list-list,list-hash，这就相当于除hash外三种分区方式的笛卡尔形式都有了。为什么会没有hash做为根分区的组合分区形式呢，再仔细回味一下第二点，你一定能够想明白~~。

深入学习分区表及分区索引(2)--创建range分区

2008-05-22 19:00

一、如何创建

如果想对某个表做分区，必须在创建表时就指定分区，我们可以对一个包含分区的表中的分区做修改，但不能直接将一个未分区的表修改成分区表(起码在10g 是不行的，当然你可能会说，可以通过在线重定义的方式，但是这不是直接哟，这也是借助临时表间接实现的)。

创建表或索引的语法就不说了，大家肯定比我还熟悉，而想在建表(索引)同时指定分区也非常容易，只需要把创建分区的子句放到";"前就行啦，同时需要注意表的row movement属性，它用来控制是否允许修改列值所造成的记录移动至其它分区存储，有enable|disable两种状态，默认是disable row movement，当disable时，如果记录要被更新至其它分区，则更新语句会报错。

下面分别演示不同分区方式的表和索引的创建：

1、创建range分区

语法如下，图：[range_partitioning.gif]

需要我们指定的有：

l column:分区依赖列(如果是多个，以逗号分隔);

l partition:分区名称;

l values less than:后跟分区范围值(如果依赖列有多个，范围对应值也应是多个，中间以逗号分隔);

l tablespace_clause:分区的存储属性，例如所在表空间等属性(可为空)，默认继承基表所在表空间的属性。

①创建一个标准的range分区表：

JSSWEB> create table t_partition_range (id number,name varchar2(50))

2 partition by range(id)(

3 partition t_range_p1 values less than (10) tablespace tbspart01,

4 partition t_range_p2 values less than (20) tablespace tbspart02,

5 partition t_range_p3 values less than (30) tablespace tbspart03,

6 partition t_range_pmax values less than (maxvalue) tablespace tbspart04

7 );

表已创建。

要查询创建分区的信息，可以通过查询

user_part_tables,user_tab_partitions两个数据字典（索引分区、组织分区等信息也有对应的数据字典，后续示例会逐步提及）。

user_part_tables：记录分区的表的信息;

user_tab_partitions：记录表的分区的信息。

例如：

JSSWEB> select table_name,partitioning_type,partition_count

2 From user_part_tables where table_name='T_PARTITION_RANGE'; TABLE_NAME PARTITI PARTITION_COUNT

------------------------------ ------- ---------------

T_PARTITION_RANGE RANGE 4

JSSWEB> select partition_name,high_value,tablespace_name

2 from user_tab_partitions where table_name='T_PARTITION_RANGE'

3 order by partition_position;

PARTITION_NAME HIGH_VALUE TABLESPACE_NAME

------------------------------ ---------- --------------------

T_RANGE_P1 10 TBSPART01

T_RANGE_P2 20 TBSPART02

T_RANGE_P3 30 TBSPART03

T_RANGE_PMAX MAXVALUE TBSPART04

②创建global索引range分区：

JSSWEB> create index idx_parti_range_id on t_partition_range(id)

2 global partition by range(id)(

3 partition i_range_p1 values less than (10) tablespace tbspart01,

4 partition i_range_p2 values less than (40) tablespace tbspart02,

5 partition i_range_pmax values less than (maxvalue) tablespace tbspart03);

索引已创建。

由上例可以看出，创建global索引的分区与创建表的分区语句格式完全相同，而且其分区形式与索引所在表的分区形式没有关联关系。

注意：我们这里借助上面的表t_partition_range来演示创建range分区的global索引，并不表示range分区的表，只能创建range分区的global索引，只要你想，也可以为其创建hash分区的global索引。

查询索引的分区信息可以通过user_part_indexes、user_ind_partitions两个数据字典：

JSSWEB> select index_name, partitioning_type, partition_count

2 From user_part_indexes

3 where index_name = 'IDX_PARTI_RANGE_ID';

INDEX_NAME PARTITI PARTITION_COUNT

------------------------------ ------- ---------------

IDX_PARTI_RANGE_ID RANGE 3

JSSWEB> select partition_name, high_value, tablespace_name

2 from user_ind_partitions

3 where index_name = 'IDX_PARTI_RANGE_ID'

4 order by partition_position;

PARTITION_NAME HIGH_VALUE TABLESPACE_NAME

------------------------------ ---------- --------------------

I_RANGE_P1 10 TBSPART01

I_RANGE_P2 40 TBSPART02

I_RANGE_PMAX MAXVALUE TBSPART03

③Local分区索引的创建最简单，例如：

仍然借助t_partition_range表来创建索引

--首先删除之前创建的global索引

JSSWEB> drop index IDX_PARTI_RANGE_ID;

索引已删除。

JSSWEB> create index IDX_PARTI_RANGE_ID on T_PARTITION_RANGE(id) local; 索引已创建。

查询相关数据字典：

JSSWEB> select index_name, partitioning_type, partition_count

2 From user_part_indexes

3 where index_name = 'IDX_PARTI_RANGE_ID';

INDEX_NAME PARTITI PARTITION_COUNT

------------------------------ ------- ---------------

IDX_PARTI_RANGE_ID RANGE 4

JSSWEB> select partition_name, high_value, tablespace_name

2 from user_ind_partitions

3 where index_name = 'IDX_PARTI_RANGE_ID'

4 order by partition_position;

PARTITION_NAME HIGH_VALUE TABLESPACE_NAME

------------------------------ ---------- --------------------

T_RANGE_P1 10 TBSPART01

T_RANGE_P2 20 TBSPART02

T_RANGE_P3 30 TBSPART03

T_RANGE_PMAX MAXVALUE TBSPART04

可以看出，local索引的分区完全继承表的分区的属性，包括分区类型，分区的范围值即不需指定也不能更改，这就是前面说的：local索引的分区维护完全依赖于其索引所在表。

不过呢分区名称，以及分区所在表空间等信息是可以自定义的，例如：

SQL> create index IDX_PART_RANGE_ID ON T_PARTITION_RANGE(id) local (

2 partition i_range_p1 tablespace tbspart01,

3 partition i_range_p2 tablespace tbspart01,

4 partition i_range_p3 tablespace tbspart02,

5 partition i_range_pmax tablespace tbspart02

6 );

索引已创建。

SQL> select index_name, partitioning_type, partition_count

2 From user_part_indexes

3 where index_name = 'IDX_PART_RANGE_ID';

INDEX_NAME PARTITI PARTITION_COUNT ------------------------------ ------- --------------- IDX_PART_RANGE_ID RANGE 4 SQL> select partition_name, high_value, tablespace_name

2 from user_ind_partitions

3 where index_name = 'IDX_PART_RANGE_ID'

4 order by partition_position;

PARTITION_NAME HIGH_VALUE TABLESPACE_NAME

--------------- --------------- --------------------

I_RANGE_P1 10 TBSPART01

I_RANGE_P2 20 TBSPART01

I_RANGE_P3 30 TBSPART02

I_RANGE_PMAX MAXVALUE TBSPART02

深入学习分区表及分区索引(3)--创建hash分区

2008-05-22 19:02