Oracle 分区表

 

1.1.1.概述

分区表是将大表的数据分成称为分区的许多小的子集，9i提供四种分区方法：范围分区，列表分区，哈希分区和混合分区；

• 范围分区是根椐分区键的不同取值范围来划分子集的，关键字RANGE， VALUES LESS THAN；

• 列表分区是根椐分区键的一些离散的取值来划分子集的，关键字LIST， VALUES；

• 哈希分区是应用哈希算法将分区键对应到某个子集中去，关键字HASH， PARTITIONS；

• 混合分区只能有两层，第一层是范围分区，第二层可以是列表分区或者哈希分区；

• 范围分区和列表分区中，如果插入记录的分区键没有对应的容纳分区，会产生ORA-14400；

• update操作如果会使记录从一个分区迁移到另一个分区，且分区表的ROW MOVEMENT属性是DISABLE，会产ORA-14402；

• 分区表上的索引有两大类：普通的二叉树索引，分区索引，下面讲到的都是分区索引：

• 按索引分区和表分区间的对应关系可以分为局部索引和全局索引；

Ø 局部索引的索引分区和表分区间是一一对应的，全局索引则相反；

Ø 局部索引的分区方法可以用上面提到四种的任何一种，全局索引的分区方法只有范围分区（而且最高的分区必须用MAXVALUE来定义）；

Ø ORACLE自动维护局部索引的分区，当表分区被合并，分裂或删除时，关联的索引分区也会被合并，分裂或删除；对分区表执行管理操作时会使其上的全局索引失效；

Ø 建在分区表的位图索引必须是局部分区索引；

Ø ORACLE推荐尽可能地使用局部索引；

• 按索引栏位和分区键间的关系分为前缀索引和非前缀索引；

Ø 前缀索引最前面的栏位是分区键栏位，非前缀索引相反；

• 在这两种分类方法的四种组合中，只有三种有效（局部前缀索引，局部非前缀索引，全局前缀索引），不存在全局非前缀索引；

• 分区表给CBO带来很多选项，如分区排除，并行分区连接等。

 

下面皆以学生表（tab_students）为例说明。

下面是一些关键字说明

INITIAL ： specifies the size in bytes of the object's first extent. Oracle allocates space for this extent when you create the object. You can also use K or M to specify this size in kilobytes or megabytes. The default value is the size of 5 data blocks. The minimum value is the size of 2 data blocks. The maximum value varies depending on your operating system. Oracle rounds values up to the next multiple of the data block size.

NEXT ： specifies the size in bytes of the next extent to be allocated to the object. You can also use K or M to specify the size in kilobytes or megabytes. The default value is the size of 5 data blocks. The minimum value is the size of 1 data block. The maximum value varies depending on your operating system. Oracle rounds values up to the next multiple of the data block size.

MINEXTENTS ： specifies the total number of extents allocated when the segment is created. This parameter allows you to allocate a large amount of space when you create an object, even if the space available is not contiguous. The default and minimum value is 1, meaning that Oracle only allocates the initial extent, except for rollback segments for which the default and minimum value is 2. The maximum value varies depending on your operating system.
If the MINEXTENTS value is greater than 1, then Oracle calculates the size of subsequent extents based on the values of the INITIAL, NEXT, and PCTINCREASE parameters.

MAXEXTENTS ： specifies the total number of extents, including the first, that Oracle can allocate for the object. The minimum value is 1. The default and maximum values vary depending your data block size.

PCTINCREASE ： specifies the percent by which each extent after the second grows over the previous extent. The default value is 50, meaning that each subsequent extent is 50% larger than the preceding extent. The minimum value is 0, meaning all extents after the first are the same size. The maximum value varies depending on your operating system.
You cannot specify PCTINCREASE for rollback segments. Rollback segments always have a PCTINCREASE value of 0.
Oracle rounds the calculated size of each new extent up to the next multiple of the data block size.

 

1.1.2.创建范围分区表

1.1.2.1.   按数字范围创建

--按学生的年齡分区小于１０岁一个区，小于20的一个分区

-- 创建表

create table tab_students(c_id number,c_name varchar2( 10 ),
c_age number,c_birthday date,c_nation varchar2( 10 ))
partition by range(c_age)
(partition part_age_less10 values less than( 10 )
 tablespace ACHIEVO_TEST_TABLESPACE
 storage(initial 100 k next 100 k minextents 1 maxextents unlimited pctincrease 0 ),
 partition part_age_less20 values less than( 20 )
 tablespace ACHIEVO_TEST_TABLESPACE
 storage(initial 100 k next 100 k minextents 1 maxextents unlimited pctincrease 0 ));
 -- 创建索引
create index idx_age on tab_students(c_age)
tablespace ACHIEVO_TEST_TABLESPACE
storage(initial 100 k next 100 k minextents 1 maxextents unlimited pctincrease 0 ) nologging;

1.1.2.2.   按日期范围创建

--按学生的生日创建分区

create table tab_students(c_id number,c_name varchar2( 10 ),
c_age number,c_birthday date,c_nation varchar2( 10 ))
partition by range(c_birthday)
(partition part_born_less1980 values less than(to_date( '1980-01-01' , 'yyyy-mm-dd' ))
 tablespace ACHIEVO_TEST_TABLESPACE
 storage(initial 100 k next 100 k minextents 1 maxextents unlimited pctincrease 0 ),
 partition part_born_less1990 values less than(to_date( '1990-01-01' , 'yyyy-mm-dd' ))
 tablespace ACHIEVO_TEST_TABLESPACE
 storage(initial 100 k next 100 k minextents 1 maxextents unlimited pctincrease 0 ));
 
create index idx_birthday on tab_students(c_birthday)
tablespace ACHIEVO_TEST_TABLESPACE
storage(initial 100 k next 100 k minextents 1 maxextents unlimited pctincrease 0 ) nologging;

 

1.1.3.创建列表分区表

--按学生的民族分区

create table tab_students(c_id number,c_name varchar2( 10 ),
c_age number,c_birthday date,c_nation varchar2( 10 ))
partition by list(c_nation)
(partition part_hanzu values( ' 汉族 ' )
 tablespace ACHIEVO_TEST_TABLESPACE
 storage(initial 100 k next 100 k minextents 1 maxextents unlimited pctincrease 0 ),
 partition part_huimiao values( ' 苗族 ' , ' 满族 ' , ' 回族 ' )
 tablespace ACHIEVO_TEST_TABLESPACE
 storage(initial 100 k next 100 k minextents 1 maxextents unlimited pctincrease 0 ),
 partition part_others values(default)
 tablespace ACHIEVO_TEST_TABLESPACE
 storage(initial 100 k next 100 k minextents 1 maxextents unlimited pctincrease 0 ));

 

1.1.4.创建HASH分区

-- 根据名字创建分区

create table tab_students(c_id number,c_name varchar2( 10 ),
c_age number,c_birthday date,c_nation varchar2( 10 ))
partition by hash(c_name)
(partition part_name1
 tablespace ACHIEVO_TEST_TABLESPACE,
 partition part_name2 tablespace ACHIEVO_TEST_TABLESPACE,
 partition part_name3 tablespace ACHIEVO_TEST_TABLESPACE);
 

1.1.5.创建混合分区表

create table tab_students(c_id number,c_name varchar2( 10 ),
c_age number,c_birthday date,c_nation varchar2( 10 ))
partition by range(c_id)
subpartition by list(c_nation)
SUBPARTITION TEMPLATE
(SUBPARTITION part_hanzu VALUES ( ' 汉族 ' ),
SUBPARTITION part_others VALUES (DEFAULT))
(PARTITION part_id1 VALUES LESS THAN ( 1000 ),
PARTITION part_id2 VALUES LESS THAN ( 2500 ),
PARTITION part_id3 VALUES LESS THAN (MAXVALUE));

1.1.6.分区管理

1.1.6.1.   增加分区表

 alter table table_name add partition_name

values less than (200409) tablespace tablespace_name;

1.1.6.2.   删除一分区

alter table table_name drop Partition partition_name;

1.1.6.3.   将一个分区分为两个分区

alter table table_name split Partition partition_name at (200409)
into (Partition partition_name tablespace tablespace_name,
Partition partition_name tablespace tablespace_name);

1.1.6.4.   合并分区

ALTER TABLE table_name
   MERGE PARTITIONS partition_name, partition_name INTO PARTITION partition_name

1.1.6.5.   将分区改名

alter table table_name rename Partition partition_name to partition_name

1.1.6.6.   将分区改表空间

alter table table_name move partition_name
tablespace tablespace_name nologging 

1.1.6.7.   查询特定分区

select count(*) from table_name partition (partition_name); 

1.1.6.8.   添加数据

insert into table_name select * from table_name partition (partition_name)  　

1.1.6.9.   分区表的导出

userid=USER/PWD
buffer=102400
tables=table_name:partition_name,
file=E:/exp_para/xxx.dmp
log=E:/exp_para/xxx.log 

1.1.6.10.             技巧：删除表中一个字段

alter table table_name set unused column column_name;

1.1.6.11.            添加一个字段

alter table table_name add column_name number(1);