oracle分区表的作用是什么

oracle分区表的作用是什么，相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。

成都创新互联于2013年创立，是专业互联网技术服务公司，拥有项目做网站、网站建设网站策划，项目实施与项目整合能力。我们以让每一个梦想脱颖而出为使命，1280元宜城做网站,已为上家服务,为宜城各地企业和个人服务,联系电话:18980820575

1.表空间及分区表的概念

表空间：是一个或多个数据文件的集合，所有的数据对象都存放在指定的表空间中，但主要存放的是表，所以称作表空间。

分区表：当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表。

2.表分区的具体作用

oracle的表分区功能通过改善可管理性、性能和可用性，从而为各式应用程序带来了极大的好处。通常，分区可以使某些查询以及维护操作的性能大大提高。此外，分区还可以极大简化常见的管理任务，分区是构建千兆字节数据系统或超高可用性系统的关键工具。

分区功能能够将表、索引或索引组织表进一步细分为段，这些数据库对象的段叫做分区。每个分区有自己的名称，还可以选择自己的存储特性。从数据库 管理员的角度来看，一个分区后的对象具有多个段，这些段既可进行集体管理，也可单独管理，这就使数据库管理员在管理分区后的对象时有相当大的灵活性。但 是，从应用程序的角度来看，分区后的表与非分区表完全相同，使用 SQL DML 命令访问分区后的表时，无需任何修改。

什么时候使用分区表，官方给的建议是：

a. 表的大小超过2GB。

b. 表中包含历史数据，新的数据被增加到新的分区中。

3.表分区的优缺点

优点：

a.改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。

b.增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用。

c.维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可。

d.均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能。

缺点：

分区表相关，已经存在的表没有方法可以直接转化为分区表。不过oracle提供了在线重定义表的功能。

4.表分区的几种类型及操作方法

4.1 范围分区(range) maxvalue

范围分区将数据基于范围映射到每一个分区，这个范围是你在创建分区时指定的分区键决定的。这种分区方式是最为常用的，并且分区键经常采用日期。举个例子：你可能会将销售数据按照月份进行分区。

当使用范围分区时，请考虑以下几个规则：

a.每一个分区都必须有一个VALUES LESS THEN子句，它指定了一个不包括在该分区中的上限值。分区键的任何值等于或者大于这个上限值的记录都会被加入到下一个高一些的分区中。

b.所有分区，除了第一个，都会有一个隐式的下限值，这个值就是此分区的前一个分区的上限值。

c.如果某些记录暂无法预测范围，可以创建maxvalue分区，所有不在指定范围内的记录都会被存储到maxvalue所在分区中。

例1：假设有一个test表，表中有数据200000行，我们将此表通过id进行分区，每个分区存储100000行，我们将每个分区保存到单独的表空间中，这样数据文件就可以跨越多个物理磁盘。下面是创建表和分区的代码，如下：

----先创建多个测试表空间

sys@ORCL>create tablespace test_ts01 datafile '/home/oracle/test_01.dbf' size 32m extent management local autoallocate;

Tablespace created.

sys@ORCL>create tablespace test_ts02 datafile '/home/oracle/test_02.dbf' size 32m extent management local autoallocate;

Tablespace created.

sys@ORCL>create tablespace test_ts03 datafile '/home/oracle/test_03.dbf' size 32m extent management local autoallocate;

Tablespace created.

----创建test分区表

create table test

( id number not null,

first_name varchar2(30) not null,

last_name varchar2(30) not null,

phone varchar2(30) not null,

email varchar2(80),

status char(1),

constraint test_id primary key (id)

)

partition by range (id)

( partition test_part1 values less than (100000) tablespace test_ts01,

partition test_part2 values less than (200000) tablespace test_ts02,

partition test_part3 values less than (maxvalue) tablespace test_ts03

);

例2：按时间划分

create table order_time

( order_id number(7) not null,

order_date date,

total_amount number,

custotmer_id number(7),

paid char(1)

)

partition by range(order_date)

( partition ora_time_part01 values less than (to_date('2016-06-01','yyyy-mm-dd')) tablespace test_ts01,

partition ora_time_part02 values less than (to_date('2016-07-01','yyyy-mm-dd')) tablespace test_ts02,

partition ora_time_part03 values less than (to_date('2016-08-01','yyyy-mm-dd')) tablespace test_ts03

);

例3：maxvalue

create table rangetable

( rt_id number(8) not null,

name varchar(10),

grade int,

constraint ranget_id primary key (rt_id)

)

partition by range (grade)

( partition part1 values less than (1000) tablespace test_ts01,

partition part2 values less than (2000) tablespace test_ts02,

partition part3 values less than (maxvalue) tablespace test_ts03

);

4.2 列表分区(list) default

List分区也需要指定列的值，其分区值必须明确指定，该分区列只能有一个，不能像range或者hash分区那样同时指定多个列做为分区依赖列，但它的单个分区对应值可以是多个。

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

在根据某字段，如城市代码分区时，可以指定default，把非分区规则的数据，全部放到这个default分区。该分区的特点是某列的值只有几个，基于这样的特点我们可以采用列表分区。

create tablespace test_ts04 datafile '/home/oracle/test_04.dbf' size 32m extent management local autoallocate;

create tablespace test_ts05 datafile '/home/oracle/test_05.dbf' size 32m extent management local autoallocate;

create tablespace test_ts06 datafile '/home/oracle/test_06.dbf' size 32m extent management local autoallocate;

alter database datafile '/home/oracle/test_06.dbf' resize 100m;

例1：

create table problem_tickets

( problem_id number(7) not null,

description varchar2(2000),

customer_id number(7) not null,

date_entered date not null,

status varchar2(20),

constraint problem_tic_id primary key (problem_id)

)

partition by list (status)

( partition prob_active values ('active') tablespace test_ts04,

partition prob_inactive values ('inactive') tablespace test_ts05,

partition prob_other values(default) tablespace test_ts06

);

例2：

create table ListTable

( id int,

name varchar2(20),

area varchar2(10),

constraint ListTable_id primary key (id)

)

partition by list (area)

( partition part1 values ('SH','BJ') tablespace test_ts04,

partition part2 values ('SC','CQ') tablespace test_ts05,

partition part3 values ('SD') tablespace test_ts06

);

4.3 散列分区(hash)

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

例1：

create table hash_table

( col number(8),

inf varchar2(100)

)

partition by hash(col)

( partition part01 tablespace test_ts04,

partition part02 tablespace test_ts05,

partition part03 tablespace test_ts06

);

简写：

create tablespace test_ts07 datafile '/home/oracle/test_07.dbf' size 32m extent management local autoallocate;

create tablespace test_ts08 datafile '/home/oracle/test_08.dbf' size 32m extent management local autoallocate;

create tablespace test_ts09 datafile '/home/oracle/test_09.dbf' size 32m extent management local autoallocate;

create table emp

( empno number(4),

ename varchar2(30),

sal number

)

partition by hash (empno) partitions 4

store in (test_ts06,test_ts07,test_ts08,test_ts09);

4.4 组合分区

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

在10g中组合分区主要有两种：range-hash，range-list。11g中又增加了range-range，list-range，list-list，list-hash，并且 11g里面还支持Interval分区和虚拟列分区。 注意顺序，根分区只能是range分区，子分区可以是hash分区或list分区。

----oracle 11g 新特性简介：

http://blog.csdn.net/tianlesoftware/article/details/5134819

----分区表 之 Interval分区 和 虚拟列 按星期分区表

http://blog.csdn.net/tianlesoftware/article/details/5662337

4.4.1 范围-列表复合分区(range-list)

这种分区是基于范围分区和列表分区，表首先按某列进行范围分区，然后再按某列进行列表分区，分区之中的分区被称为子分区。

create table sales

( product_id varchar2(5),

sales_date date,

sales_cost number(10),

status varchar2(30)

)

partition by range (sales_date) subpartition by list (status)

( partition p1 values less than(to_date('2016-06-01','yyyy-mm-dd')) tablespace test_ts07

( subpartition p1sub1 values ('active') tablespace test_ts07,

subpartition p1sub2 values ('inactive') tablespace test_ts07

),

partition p2 values less than(to_date('2016-07-01','yyyy-mm-dd')) tablespace test_ts08

( subpartition p2sub1 values('active') tablespace test_ts08,

subpartition p2sub2 values ('inactive') tablespace test_ts08

)

);

4.4.2 范围-散列复合分区(range-hash)

这种分区是基于范围分区和散列分区，表首先按某列进行范围分区，然后再按某列进行散列分区。

create tablespace test_ts11 datafile '/home/oracle/test_11.dbf' size 32m extent management local autoallocate;

create tablespace test_ts12 datafile '/home/oracle/test_12.dbf' size 32m extent management local autoallocate;

create tablespace test_ts13 datafile '/home/oracle/test_13.dbf' size 32m extent management local autoallocate;

create table dinya_test

( transaction_id number,

item_id number(8) not null,

item_description varchar2(300),

transaction_date date,

constraint dinya_test_id primary key (transaction_id)

)

partition by range(transaction_date) subpartition by hash(transaction_id) subpartitions 3

store in (test_ts11,test_ts12,test_ts13)

( partition part_01 values less than(to_date('2016-06-01','yyyy-mm-dd')),

partition part_02 values less than(to_date('2016-12-01','yyyy-mm-dd')),

partition part_03 values less than(maxvalue)

);

5.分区表的维护操作

5.1 添加分区(add)

----添加新的分区有2中情况：

(1)原分区里边界是maxvalue或者default。 这种情况下，我们需要把边界分区drop掉，加上新分区后，在添加上新的分区。 或者采用split(range类型使用at，list使用values)对边界分区进行拆分。

(2)没有边界分区的。 这种情况下，直接添加分区就可以了。

----以下代码给test表添加一个分区

alter table test add partition test_part4 values less than (400000);

create tablespace test_ts14 datafile '/home/oracle/test_14.dbf' size 32m;

alter table test add partition test_part5 values less than (500000) tablespace test_ts14;

----注意：以上添加的分区界限应该高于最后一个分区界限。

----以下代码给sales表的p2分区添加一个p2sub3子分区

create tablespace test_ts15 datafile '/home/oracle/test_15.dbf' size 3g;

alter table sales modify partition p2 add subpartition p2sub3 values('complete') tablespace test_ts15;

--------------有边界分区添加新分区：

-----1> 创建分区表及创建索引

create table custaddr

( id varchar2 (15 byte) not null,

areacode varchar2(4 byte)

)

partition by list (areacode)

( partition t_list555 values ('555') tablespace test_ts15,

partition p_other values (default) tablespace test_ts15);

create index ix_custaddr_id on custaddr(id)

local ( partition t_list555 tablespace test_ts15,

partition p_other tablespace test_ts15);

----2> 插入测试数据

insert into custaddr values ('1','555');

insert into custaddr values ('2','552');

insert into custaddr values ('3','554');

commit;

select * from custaddr;

ID AREACODE

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

1 555

2 552

3 554

select * from custaddr partition(t_list555);

ID AREACODE

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

1 555

----3> 删除default分区

shall@ORCL>alter table custaddr drop partition p_other;

Table altered.

shall@ORCL>select * from custaddr;

ID AREACODE

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

1 555

shall@ORCL>select table_name,partition_name from user_tab_partitions where table_name='CUSTADDR';

TABLE_NAME PARTITION_NAME

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

CUSTADDR T_LIST555

----4> 添加新分区，default分区

shall@ORCL> alter table custaddr add partition t_list551 values('551') tablespace test_ts15;

shall@ORCL>alter table custaddr add partition p_other values (default) tablespace test_ts15;

shall@ORCL> select table_name,partition_name from user_tab_partitions where table_name='CUSTADDR';

TABLE_NAME PARTITION_NAME

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

CUSTADDR P_OTHER

CUSTADDR T_LIST551

CUSTADDR T_LIST555

----5> 对于局部索引，oracle会自动增加一个局部分区索引

shall@ORCL> select index_name,table_name,partitioning_type from user_part_indexes where index_name='IX_CUSTADDR_ID';

INDEX_NAME TABLE_NAME PARTITIONING_TYPE

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

IX_CUSTADDR_ID CUSTADDR LIST

shall@ORCL>select index_name,partition_name from user_ind_partitions where index_name='IX_CUSTADDR_ID';

INDEX_NAME PARTITION_NAME

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

IX_CUSTADDR_ID P_OTHER

IX_CUSTADDR_ID T_LIST551

IX_CUSTADDR_ID T_LIST555

----使用split分区拆分方式，接上面--2>进行下面测试

----3> 使用split方式添加分区

alter table custaddr split partition p_other values('552') into (partition t_list552 tablespace test_ts15, partition p_other tablespace test_ts15);

------注意，这里如果是range类型，使用at，list使用values

select table_name,partition_name from user_tab_partitions where table_name='CUSTADDR';

TABLE_NAME PARTITION_NAME

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

CUSTADDR P_OTHER

CUSTADDR T_LIST552

CUSTADDR T_LIST555

select index_name,partition_name from user_ind_partitions where index_name='IX_CUSTADDR_ID';

INDEX_NAME PARTITION_NAME

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

IX_CUSTADDR_ID P_OTHER

IX_CUSTADDR_ID T_LIST552

IX_CUSTADDR_ID T_LIST555

------注意：分区表会自动维护局部分区索引。全局索引会失效，需要rebuild

shall@ORCL>Select index_name,status From user_indexes Where table_name='CUSTADDR';

INDEX_NAME STATUS

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

IX_CUSTADDR_ID N/A

------查看数据

shall@ORCL>select * from custaddr;

ID AREACODE

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

1 555

2 552

3 554

shall@ORCL>select * from custaddr partition(t_list552);

ID AREACODE

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

2 552

shall@ORCL>select * from custaddr partition(t_list555);

ID AREACODE

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

1 555

shall@ORCL>select * from custaddr partition(p_other);

ID AREACODE

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

3 554

5.2 删除分区(drop)

----以下代码删除了sales表p2分区

alter table sales drop partition p2;

------alter table sales add partition p2 values less than(to_date('2016-07-01','yyyy-mm-dd')) tablespace test_ts08

( subpartition p2sub1 values('active') tablespace test_ts08,

subpartition p2sub2 values ('inactive') tablespace test_ts08,

subpartition p2sub3 values('complete') tablespace test_ts15

);

----删除sales表p2sub3子分区

alter table sales drop subpartition p2sub3;

----注意：如果删除的分区是表中唯一的分区，那么此分区将不能被删除，要想删除此分区，必须删除表。

----同样会自动维护局部分区索引，同时会使全局索引unusable,需要重建

5.3 截断分区(truncate)

截断某个分区是指删除某个分区中的数据，并不会删除分区，也不会删除其它分区中的数据。当表中即使只有一个分区时，也可以截断该分区。通过以下代码截断分区：

alter table sales truncate partition p2;

----当然也可以截断子分区

alter table sales truncate subpartition p2sub2;

Truncate相对delete操作很快，数据仓库中的大量数据的批量数据加载可能会有用到;截断分区同样会自动维护局部分区索引，同时会使全局索引unusable,需要重建

5.4 合并分区(merge)

相邻的分区可以merge为一个分区，新分区的下边界为原来边界值较低的分区，上边界为原来边界值较高的分区，原先的局部索引相应也会合并，全局索引会失效，需要rebuild。

alter database datafile '/home/oracle/test_08.dbf' resize 500m;

alter table sales merge partitions p1,p2 into partition p2;

5.5 拆分分区(split)

拆分分区将一个分区拆分两个新分区，拆分后原来分区不再存在。注意不能对HASH类型的分区进行拆分。

alter table sales split partition p2 at (to_date('2016-06-01','yyyy-mm-dd')) into (partition p3,partition p4);

5.6 重命名分区(rename)

alter table sales rename partition p3 to p13;

5.7 移动分区(move)

alter table test move partition test_part1 tablespace test_ts15;

alter table test move partition test_part1 tablespace test_ts01;

注意：分区移动会自动维护局部分区索引，不会自动维护全局索引，所以需要我们重新rebuild分区索引，具体需要rebuild哪些索引，可以通过dba_part_indexes,dba_ind_partitions去判断。

shall@ORCL>Select index_name,status From user_indexes Where table_name='CUSTADDR';

INDEX_NAME STATUS

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

IX_CUSTADDR_ID N/A

6.相关查询

6.1 查询表上有多少个分区

select * from user_tab_partitions where table_name='SALES';

select * from dba_tab_partitions where table_name='SALES';

6.2 查询索引信息

select object_name,object_type,tablespace_name,sum(value) from v$segment_statistics

where statistic_name IN ('physical reads','physical write','logical reads')and object_type='INDEX'

group by object_name,object_type,tablespace_name

order by 4 desc;

6.3 查询所有分区表信息

select * from dba_part_tables;

select * from all_part_tables; ---当前用户可访问的所有分区表信息

select * from user_part_tables; ---当前用户的所有分区表信息

6.4 查询子分区信息

select * from dba_tab_subpartitions;

select * from all_tab_subpartitions;

select * from user_tab_subpartitions;

6.5 查询分区列信息

select * from dba_part_key_columns;

select * from all_part_key_columns;

select * from user_part_key_columns;

6.6 查询子分区列信息

select * from dba_subpart_key_columns;

select * from all_subpart_key_columns;

select * from user_subpart_key_columns;

6.7 查询所有的分区表

select * from dba_tables where partitioned='YES';

select * from all_tables where partitioned='YES';

select * from user_tables where partitioned='YES';

7.普通表转分区表方法

reference http://blog.csdn.net/tianlesoftware/article/details/6218704

将普通表转换成分区表有4种方法：

1. Export/import method

2. Insert with a subquery method

3. Partition exchange method

4. DBMS_REDEFINITION

7.1 Export/import method(导入导出)

----创建普通表：

sys@ORCL>create table shall(id int,name varchar2(20));

sys@ORCL>insert into shall values(100,'zhong');

sys@ORCL>insert into shall values(101,'Jack');

sys@ORCL>insert into shall values(204,'shell');

sys@ORCL>commit;

----导出表

[oracle@zyx ~]$ exp \'/ as sysdba\' tables=shall file=shall.dmp

Export: Release 11.2.0.4.0 - Production on Tue Jun 28 12:32:03 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

About to export specified tables via Conventional Path ...

. . exporting table SHALL 3 rows exported

Export terminated successfully without warnings.

[oracle@zyx ~]$

----删除原普通表

sys@ORCL>drop table shall;

----重新创建为分区表

create table shall(id int,name varchar2(20))

partition by range(id)

( partition shall_part1 values less than (100),

partition shall_part2 values less than (200),

partition shall_part3 values less than (maxvalue)

);

----导入数据

[oracle@zyx ~]$ imp \'/ as sysdba\' file=shall.dmp tables=shall fromuser=sys touser=sys ignore=y;

Import: Release 11.2.0.4.0 - Production on Tue Jun 28 12:38:02 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path

. importing SYS's objects into SYS

. . importing table "SHALL" 3 rows imported

Import terminated successfully without warnings.

----测试

sys@ORCL>select * from shall;

ID NAME

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

100 zhong

101 Jack

204 shell

sys@ORCL>select * from shall partition(shall_part1);

no rows selected

sys@ORCL>select * from shall partition(shall_part2);

ID NAME

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

100 zhong

101 Jack

sys@ORCL>select * from shall partition(shall_part3);

ID NAME

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

204 shell

sys@ORCL>select * from shall partition(shall_part3) union all select * from shall partition(shall_part2);

ID NAME

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

204 shell

100 zhong

101 Jack

sys@ORCL>select table_name,partition_name from user_tab_partitions where table_name='SHALL';

TABLE_NAME PARTITION_NAME

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

SHALL SHALL_PART1

SHALL SHALL_PART2

SHALL SHALL_PART3

7.2 Insert with a subquery method(插入查询)

这种方法就是使用insert 来实现。 当然在创建分区表的时候可以一起插入数据，也可以创建好后在insert 进去。这种方法采用DDL语句，不产生UNDO，只产生少量REDO，建表完成后数据已经在分布到各个分区中。

----创建普通表：

sys@ORCL>create table test2(id int,name varchar2(20));

sys@ORCL>insert into test2 values(100,'zhong');

sys@ORCL>insert into test2 values(140,'Jack');

sys@ORCL>insert into test2 values(240,'shell');

sys@ORCL>commit;

----创建分区表

create table part(id int,name varchar2(20))

partition by range(id)

( partition part1 values less than (100),

partition part2 values less than (200),

partition part3 values less than (maxvalue)

);

----插入数据

sys@ORCL>insert into part select * from test2;

sys@ORCL>commit;

sys@ORCL>select * from part;

----删除原普通表，并将分区表更名为原普通表

sys@ORCL>drop table test2; ----确定不需要就删除，不确定就rename to old

sys@ORCL>alter table part rename to test2;

----检查测试

sys@ORCL>select * from test2;

sys@ORCL>select * from test2 partition (part1);

no rows selected

sys@ORCL>select * from test2 partition (part2);

ID NAME

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

100 zhong

140 Jack

sys@ORCL>select * from test2 partition (part3);

ID NAME

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

240 shell

sys@ORCL>select table_name,partition_name from user_tab_partitions where table_name='TEST2';

TABLE_NAME PARTITION_

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

TEST2 PART1

TEST2 PART2

TEST2 PART3

7.3 Partition exchange method(交换分区)

这种方法只是对数据字典中分区和表的定义进行了修改，没有数据的修改或复制，效率最高。适用于包含大数据量的表转到分区表中的一个分区的操作。尽量在闲时进行操作。

交换分区的操作步骤如下：

1. 创建分区表，假设有2个分区，P1，P2.

2. 创建表A存放P1规则的数据。

3. 创建表B 存放P2规则的数据。

4. 用表A 和P1 分区交换。 把表A的数据放到到P1分区

5. 用表B 和p2 分区交换。 把表B的数据存放到P2分区。

----创建分区表

create table p_emp (sal number(7,2))

partition by range(sal)

( partition emp_p1 values less than (2000),

partition emp_p2 values less than (4000)

);

----创建测试表

sys@ORCL>create table emp1 as select sal from scott.emp where sal<2000; orcl="">select count(*) from emp1;

COUNT(*)

----------

8

sys@ORCL>create table emp2 as select sal from scott.emp where sal between 2000 and 3999;

sys@ORCL>select count(*) from emp2;

COUNT(*)

----------

5

----将两个基本表与两个分区进行交换

----如果插入的数据不满足分区规则，会报ORA-14400错误

sys@ORCL>alter table p_emp exchange partition emp_p1 with table emp1;

sys@ORCL>select count(*) from emp1;

COUNT(*)

----------

0

sys@ORCL>select count(*) from p_emp;

COUNT(*)

----------

8

sys@ORCL>alter table p_emp exchange partition emp_p2 with table emp2;

sys@ORCL>select count(*) from p_emp;

COUNT(*)

----------

13

sys@ORCL>select count(*) from emp2;

COUNT(*)

----------

0

----检查测试

sys@ORCL>select count(*) from p_emp partition (emp_p1);

COUNT(*)

----------

8

sys@ORCL>select table_name,partition_name from user_tab_partitions where table_name='P_EMP';

TABLE_NAME PARTITION_NAME

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

P_EMP EMP_P1

P_EMP EMP_P2

7.4 DBMS_REDEFINITION(在线重定义)

在线重定义能保证数据的一致性，在大部分时间内，表都可以正常进行DML操作。只在切换的瞬间锁表，具有很高的可用性。这种方法具有很强的灵活性，对各种不同的需要都能满足。而且，可以在切换前进行相应的授权并建立各种约束，可以做到切换完成后不再需要任何额外的管理操作。

reference http://blog.csdn.net/tianlesoftware/article/details/6218693

这个功能只在9.2.0.4以后的版本才有，在线重定义表具有以下功能：

(1)修改表的存储参数;

(2)将表转移到其他表空间;

(3)增加并行查询选项;

(4)增加或删除分区;

(5)重建表以减少碎片;

(6)将堆表改为索引组织表或相反的操作;

(7)增加或删除一个列。

使用在线重定义的一些限制条件：

(1) There must be enough space to hold two copies of the table.

(2) Primary key columns cannot be modified.

(3) Tables must have primary keys.

(4) Redefinition must be done within the same schema.

(5) New columns added cannot be made NOT NULL until after the redefinition operation.

(6) Tables cannot contain LONGs, BFILEs or User Defined Types.

(7) Clustered tables cannot be redefined.

(8) Tables in the SYS or SYSTEM schema cannot be redefined.

(9) Tables with materialized view logs or materialized views defined on them cannot be redefined.

(10) Horizontal sub setting of data cannot be performed during the redefinition.

在Oracle 10.2.0.4和11.1.0.7 版本下，在线重定义可能会遇到如下bug：

Bug 7007594 - ORA-600 [12261]

http://blog.csdn.net/tianlesoftware/archive/2011/03/02/6218681.aspx

在线重定义的大致操作流程如下：

(1)创建基础表A，如果存在，就不需要操作。

(2)创建临时的分区表B。

(3)开始重定义，将基表A的数据导入临时分区表B。

(4)结束重定义，此时在DB的 Name Directory里，已经将2个表进行了交换。即此时基表A成了分区表，我们创建的临时分区表B 成了普通表。 此时我们可以删除我们创建的临时表B。它已经是普通表。

----创建测试表

sys@ORCL>create user shall identified by shall;

sys@ORCL>grant connect,resource to shall;

sys@ORCL>grant select on dba_objects to shall;

shall@ORCL>create table zhong(id number(10) primary key,z_date date);

shall@ORCL>insert into zhong select rownum,created from dba_objects;

86435 rows created.

shall@ORCL>create index ind_zhong_z_date on zhong(z_date);

----收集统计信息

sys@ORCL>exec dbms_stats.gather_table_stats('shall','zhong',cascade => true);

----创建临时分区表

create table par_table (id number primary key,z_time date)

partition by range(z_time)

( partition part1 values less than (to_date('2013-7-1','yyyy-mm-dd')),

partition part2 values less than (to_date('2014-7-1','yyyy-mm-dd')),

partition part3 values less than (maxvalue)

);

----进行重定义操作

------检查重定义的合理性

sys@ORCL>exec dbms_redefinition.can_redef_table('shall','zhong');

PL/SQL procedure successfully completed.

------如果没有问题，开始重定义，这个过程可能要等一会

------这里注意：如果分区表和原表列名相同，可以用如下方式进行：

begin

dbms_redefinition.start_redef_table

( uname => 'SHALL',

orig_table => 'zhong',

int_table => 'par_table');

end;

/

------如果分区表的列名和原来的不一致，那么在开始重定义的时候，需要重新指定映射关系：

exec dbms_redefinition.start_redef_table ( 'SHALL', 'zhong', 'par_table', 'id id,z_date z_time', dbms_redefinition.cons_use_pk);

------这一步操作结束后，数据就已经同步到这个临时分区表里了

shall@ORCL>select count(*) from par_table partition(part2);

COUNT(*)

----------

86198

----同步新表，这是可选操作

begin

dbms_redefinition.sync_interim_table

( uname => 'SHALL',

orig_table => 'zhong',

int_table => 'par_table');

end;

/

----创建索引(在线重定义数据后，索引需要单独建立)

shall@ORCL>create index ind_par_date on par_table(z_time);

----收集新表统计信息

sys@ORCL>exec dbms_stats.gather_table_stats('shall','par_table',cascade => true);

----结束重定义

begin

dbms_redefinition.finish_redef_table

( uname => 'SHALL',

orig_table => 'zhong',

int_table => 'par_table');

end;

/

------结束重定义的意义：基表zhong 和临时分区表par_table 进行了交换。 此时临时分区表par_table成了普通表，我们的基表zhong成了分区表。我们在重定义的时候，基表zhong是可以进行DML操作的。 只有在2个表进行切换的时候会有短暂的锁表。

----验证：

shall@ORCL>select count(*) from par_table partition(part2);

select count(*) from par_table partition(part2)

*

ERROR at line 1:

ORA-14501: object is not partitioned

shall@ORCL>select count(*) from par_table;

COUNT(*)

----------

86435

shall@ORCL>drop table par_table;

shall@ORCL>alter index ind_par_date rename to ind_zhong_z_date;

shall@ORCL>select table_name,partition_name from user_tab_partitions where table_name='ZHONG';

TABLE_NAME PARTITION_NAME

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

ZHONG PART1

ZHONG PART2

ZHONG PART3

shall@ORCL>select count(*) from zhong;

COUNT(*)

----------

86435

shall@ORCL>select count(*) from zhong partition(part1);

COUNT(*)

----------

0

shall@ORCL>select count(*) from zhong partition(part2);

COUNT(*)

----------

86198

shall@ORCL>select count(*) from zhong partition(part3);

COUNT(*)

----------

237

8.分区表索引

分区索引分为本地(local index)索引和全局索引(global index)。局部索引比全局索引容易管理, 而全局索引比较快。

与索引有关的表：

dba_part_indexes ：分区索引的概要统计信息，可以得知每个表上有哪些分区索引，分区索引的类型(local/global)

dba_ind_partitions： 每个分区索引的分区级统计信息

dba_indexes/dba_part_indexes： 可以得到每个表上有哪些非分区索引

Local索引肯定是分区索引，Global索引可以选择是否分区，如果分区，只能是有前缀的分区索引。

分区索引分2类：有前缀(prefix)的分区索引和无前缀(nonprefix)的分区索引:

(1)有前缀的分区索引只包含了分区键，并且将其作为引导列的索引。

如：

create index i_id_global on PDBA(id) global ----引导列

partition by range(id) ----分区键

( partition p1 values less than (200),

partition p2 values less than (maxvalue)

);

这里的ID 就是分区键，并且分区键id 也是索引的引导列。

(2)无前缀的分区索引的列不是以分区键开头，或者不包含分区键列。

如：

create index ix_custaddr_local_id_p on custaddr(id)

local ( partition t_list556 tablespace test_ts15,

partition p_other tablespace test_ts15

);

这个分区是按照areacode来的。但是索引的引导列是ID。 所以它就是非前缀分区索引。

全局分区索引不支持非前缀的分区索引，如果创建，报错如下：

create index i_time_global on PDBA(id) global ----索引引导列

partition by range(time) ----分区键

( partition p1 values less than (TO_DATE('2010-12-1', 'YYYY-MM-DD')),

partition p2 values less than (maxvalue)

);

partition by range(time)

*

第 2 行出现错误:

ORA-14038: GLOBAL 分区索引必须加上前缀

8.1 Local本地索引

对于local索引，当表的分区发生变化时，索引的维护由Oracle自动进行。

注意事项：

1> 局部索引一定是分区索引，分区键等同于表的分区键。

2> 前缀和非前缀索引都可以支持索引分区消除，前提是查询的条件中包含索引分区键。

3> 局部索引只支持分区内的唯一性，无法支持表上的唯一性，因此如果要用局部索引去给表做唯一性约束，则约束中必须要包括分区键列。

4> 局部分区索引是对单个分区的，每个分区索引只指向一个表分区;全局索引则不然，一个分区索引能指向n个表分区，同时，一个表分区，也可能指向n个索引分区，对分区表中的某个分区做truncate或者move，shrink等，可能会影响到n个全局索引分区，正因为这点，局部分区索引具有更高的可用性。

5> 位图索引必须是局部分区索引。

6> 局部索引多应用于数据仓库环境中。

7> B树索引和位图索引都可以分区，但是HASH索引不可以被分区。

示例：

shall@ORCL>drop index IX_CUSTADDR_ID;

shall@ORCL> create index ix_custaddr_local_id on custaddr(id) local;

和下面SQL 效果相同，因为local索引就是分区索引：

shall@ORCL>Select index_name,status From user_indexes Where table_name='CUSTADDR';

shall@ORCL>drop index IX_CUSTADDR_LOCAL_ID;

create index ix_custaddr_local_id_p on custaddr(id)

local (

partition t_list555 tablespace test_ts15,

partition p_other tablespace test_ts15

);

create index ix_custaddr_local_id_p on custaddr(id)

*

ERROR at line 1:

ORA-14024: number of partitions of LOCAL index must equal that of the underlying table

shall@ORCL>select partition_name from user_tab_partitions where table_name=upper('custaddr');

PARTITION_NAME

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

P_OTHER

T_LIST552

T_LIST555

create index ix_custaddr_local_id_p on custaddr(id)

local (

partition t_list552 tablespace test_ts15,

partition t_list555 tablespace test_ts15,

partition p_other tablespace test_ts15

);

create index ix_custaddr_local_areacode on custaddr(areacode) local;

验证2个索引的类型：

select index_name,table_name,partitioning_type,locality,ALIGNMENT from user_part_indexes where table_name='CUSTADDR';

index_name table_name partition locali alignment

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

ix_custaddr_local_areacode custaddr list local prefixed

ix_custaddr_local_id custaddr list local non_prefixed

因为我们的custaddr表是按areacode进行分区的，所以索引ix_custaddr_local_areacode是有前缀的索引(prefixed)。而ix_custaddr_local_id是非前缀索引。

4.2 Global索引

对于global索引，可以选择是否分区，而且索引的分区可以不与表分区相对应。全局分区索引只能是B树索引，到目前为止(10gR2)，oracle只支持有前缀的全局索引。

另外oracle不会自动的维护全局分区索引，当我们在对表的分区做修改之后，如果对分区进行维护操作时不加上update global indexes的话，通常会导致全局索引的INVALDED，必须在执行完操作后 REBUILD。

注意事项：

1> 全局索引可以分区，也可以是不分区索引，全局索引必须是前缀索引，即全局索引的索引列必须是以索引分区键作为其前几列。

2> 全局索引可以依附于分区表，也可以依附于非分区表。

3> 全局分区索引的索引条目可能指向若干个分区，因此，对于全局分区索引，即使只截断一个分区中的数据，都需要rebulid若干个分区甚至是整个索引。

4> 全局索引多应用于oltp系统中。

5> 全局分区索引只按范围或者散列分区，hash分区是10g以后才支持。

6> oracle9i以后对分区表做move或者truncate的时可以用update global indexes语句来同步更新全局分区索引，用消耗一定资源来换取高度的可用性。

7> 表用a列作分区，索引用b做局部分区索引，若where条件中用b来查询，那么oracle会扫描所有的表和索引的分区，成本会比分区更高，此时可以考虑用b做全局分区索引。

注意：Oracle只支持2中类型的全局分区索引：

range partitioned 和 Hash Partitioned.

官网的说明如下：

Global Partitioned Indexes

Oracle offers two types of global partitioned index: range partitioned and hash partitioned.

(1)Global Range Partitioned Indexes

Global range partitioned indexes are flexible in that the degree of partitioning and the partitioning key are independent from the table's partitioning method. They are commonly used for OLTP environments and offer efficient access to any individual record.

The highest partition of a global index must have a partition bound, all of whose values are MAXVALUE. This ensures that all rows in the underlying table can be represented in the index. Global prefixed indexes can be unique or nonunique.

You cannot add a partition to a global index because the highest partition always has a partition bound of MAXVALUE. If you wish to add a new highest partition, use the ALTER INDEX SPLIT PARTITION statement. If a global index partition is empty, you can explicitly drop it by issuing the ALTER INDEX DROP PARTITION statement. If a global index partition contains data, dropping the partition causes the next highest partition to be marked unusable. You cannot drop the highest partition in a global index.

(2)Global Hash Partitioned Indexes

Global hash partitioned indexes improve performance by spreading out contention when the index is monotonically growing. In other words, most of the index insertions occur only on the right edge of an index.

(3)Maintenance of Global Partitioned Indexes

By default, the following operations on partitions on a heap-organized table mark all global indexes as unusable:

ADD (HASH)

COALESCE (HASH)

DROP

EXCHANGE

MERGE

MOVE

SPLIT

TRUNCATE

----示例1 全局索引，全局索引对所有分区类型都支持：

Select index_name,status From user_indexes Where table_name='CUSTADDR';

drop index IX_CUSTADDR_LOCAL_ID_P;

create index ix_custaddr_global_id on custaddr(id) global;

----示例2：全局分区索引，只支持Range 分区和Hash 分区：

1> 创建2个测试分区表：

create table pdba (id number, time date)

partition by range (time)

( partition p1 values less than (to_date('2016-1-1', 'yyyy-mm-dd')),

partition p2 values less than (to_date('2016-6-1', 'yyyy-mm-dd')),

partition p3 values less than (to_date('2016-12-1', 'yyyy-mm-dd')),

partition p4 values less than (maxvalue)

);

create table Thash

( id number primary key,

item_id number(8) not null

)

partition by hash(id)

( partition part_01,

partition part_02,

partition part_03

);

2> 创建分区索引

----示例2：全局分区索引

create index i_id_global on PDBA(id) global

partition by range(id)

( partition p1 values less than (200),

partition p2 values less than (maxvalue)

);

----这个是有前缀的分区索引。

create index i_time_global on PDBA(id) global

partition by range(time)

( partition p1 values less than (TO_DATE('2010-12-1', 'YYYY-MM-DD')),

partition p2 values less than (maxvalue)

);

partition by range(time)

*

第 2 行出现错误:

ORA-14038: GLOBAL 分区索引必须加上前缀

create index i_time_global on PDBA(time) global

partition by range(time)

( partition p1 values less than (TO_DATE('2016-12-1', 'YYYY-MM-DD')),

partition p2 values less than (maxvalue)

);

----有前缀的分区索引

select index_name,table_name,partitioning_type,locality,ALIGNMENT from user_part_indexes where table_name='PDBA';

index_name table_name partition locali alignment

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

i_id_global pdba range global prefixed

i_time_global pdba range global prefixed

CREATE INDEX ix_hash ON PDBA (id,time) GLOBAL

PARTITION BY HASH (id)

( PARTITION p1,

PARTITION p2,

PARTITION p3,

PARTITION p4);

----只要索引的引导列包含分区键，就是有前缀的分区索引。

select index_name,table_name,partitioning_type,locality,ALIGNMENT from user_part_indexes where table_name='PDBA';

index_name table_name partition locali alignment

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

i_id_global pdba range global prefixed

i_time_global pdba range global prefixed

ix_hash pdba hash global prefixed

4.3 索引重建问题

1> 分区索引重建

对于分区索引，不能整体进行重建，只能对单个分区进行重建。语法如下：

Alter index idx_name rebuild partition index_partition_name [online nologging]

说明：

online:表示重建的时候不会锁表。

nologging：表示建立索引的时候不生成日志，加快速度。

Select index_name,status From user_indexes Where table_name='PDBA';

select PARTITION_NAME from user_tab_partitions where table_name='PDBA';

select partition_name from user_ind_partitions where index_name='I_ID_GLOBAL';

alter index I_ID_GLOBAL rebuild partition p1;

alter index I_TIME_GLOBAL rebuild partition p2 online;

如果要重建整个分区索引，只能drop表原索引，在重新创建：

drop index I_ID_GLOBAL;

create index i_id_global on PDBA(id) local tablespace test_ts15;

select partition_name,tablespace_name from user_ind_partitions where index_name='I_ID_GLOBAL';

----在线重建操作要求较大的临时表空间和排序区：

select index_name,partition_name from user_ind_partitions where index_name='I_TIME_GLOBAL';

INDEX_NAME PARTITION_NAME

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

I_TIME_GLOBAL P1

I_TIME_GLOBAL P2

alter index I_TIME_GLOBAL rebuild partition p1 online nologging;

alter index I_TIME_GLOBAL rebuild partition p2 online nologging;

2> 全局索引重建

Oracle 会自动维护分区索引，对于全局索引，如果在对分区表操作时，没有指定update index，则会导致全局索引失效，需要重建。

Select index_name,status From user_indexes Where table_name='PDBA';

select index_name,table_name,status from user_indexes where INDEX_NAME='I_ID_GLOBAL';

index_name table_name status

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

I_ID_GLOBAL pdba valid

----删除一个分区：

select PARTITION_NAME from user_tab_partitions where table_name='PDBA';

select partition_name from user_ind_partitions where index_name='I_ID_GLOBAL';

alter table pdba drop partition p2;

select index_name,table_name,status from user_indexes where INDEX_NAME='I_ID_GLOBAL';

index_name table_name status

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

I_ID_GLOBAL pdba valid

----split 分区：

alter table pdba split partition P4 at(TO_DATE('2016-12-21 00:00:00','YYYY-MM-DD HH24:MI:SS')) into (partition P4, partition P5);

select PARTITION_NAME from user_tab_partitions where table_name='PDBA';

select partition_name from user_ind_partitions where index_name='I_ID_GLOBAL';

select index_name,table_name,status from user_indexes where INDEX_NAME='I_ID_GLOBAL';

index_name table_name status

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

I_ID_GLOBAL pdba valid

----drop 分区时使用update indexes

alter table pdba drop partition P4 UPDATE INDEXES;

select index_name,table_name,status from user_indexes where INDEX_NAME='I_ID_GLOBAL';

index_name table_name status

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

I_ID_GLOBAL pdba valid

----做了几个drop分区操作，全局索引没有失效，有点奇怪。 不过如果在生产环境中，还是小心点。

重建全局索引命令如下：

Alter index idx_name rebuild [online nologging]

示例：

Select index_name,status From user_indexes Where table_name='PDBA';

select PARTITION_NAME from user_tab_partitions where table_name='PDBA';

select partition_name from user_ind_partitions where index_name='I_ID_GLOBAL';

SQL> Alter index I_ID_GLOBAL rebuild online nologging;

索引已更改。

select table_name,partition_name,tablespace_name from user_tab_partitions where table_name='PDBA';

通过user_tab_partitions 表可以查看到每个分区对应的tablesapce_name. 但是，如果通过all_tables 表，却查不到分区表对应表空间的信息。

分区表：看不到tablespace_name的

SQL> select owner,table_name,tablespace_name,cluster_name from all_tables where table_name='PDBA';

普通表：是可以看到tablespace_name的

SQL> select owner,table_name,tablespace_name,cluster_name from all_tables where table_name='test1';

3> 分区索引与全局索引 重建案例2

reference http://blog.csdn.net/weiwangsisoftstone/article/details/37615245

----创建分区表：

create table test(

id number,

name varchar2(20)

)

partition by range(id)

( partition p1 values less than (1000),

partition p2 values less than (2000),

partition p3 values less than (maxvalue)

);

----创建分区索引

----LOCAL索引结构

create index ind_test_id_local on test(id) local;

----重新在name列上创建一个GLOBAL的索引

create index ind_test_name_global on test(name) global;

Select index_name,status From user_indexes Where table_name='TEST';

select PARTITION_NAME from user_tab_partitions where table_name='TEST';

select partition_name from user_ind_partitions where index_name='IND_TEST_ID_LOCAL';

select partition_name from user_ind_partitions where index_name='IND_TEST_NAME_GLOBAL';

insert into test values(999,'p1');

insert into test values(1999,'p2');

insert into test values(2999,'p3');

SQL> select * from test;

ID NAME

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

999 p1

1999 p2

2999 p3

----查询当前用户下有哪些是分区表：

SELECT table_name, partitioning_type,partition_count FROM USER_PART_TABLES;

TABLE_NAME PARTITION PARTITION_COUNT

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

TEST RANGE 3

----查询当前用户下有哪些分区索引：

SELECT index_name,table_name FROM USER_PART_INDEXES;

INDEX_NAME TABLE_NAME

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

ID_LOCAL TEST

----索引对应的分区以及索引的状态

select index_name,partition_name,status from user_ind_partitions;

INDEX_NAME PARTITION_NAME STATUS

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

ID_LOCAL P2 USABLE

ID_LOCAL P1 USABLE

ID_LOCAL P3 USABLE

----移动分区表使索引失效

alter table test move partition p1 tablespace users;

alter table test move partition p2 tablespace users;

----本地分区失效

select index_name,partition_name,status from user_ind_partitions;

INDEX_NAME PARTITION_NAME STATUS

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

IND_TEST_ID_LOCAL P3 USABLE

IND_TEST_ID_LOCAL P2 UNUSABLE

IND_TEST_ID_LOCAL P1 UNUSABLE

----重建分区索引

alter index IND_TEST_ID_LOCAL rebuild partition p1;

----索引状态已更改。

select index_name,partition_name,status from user_ind_partitions;

INDEX_NAME PARTITION_NAME STATUS

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

IND_TEST_ID_LOCAL P1 USABLE

IND_TEST_ID_LOCAL P3 USABLE

IND_TEST_ID_LOCAL P2 UNUSABLE

alter table test modify partition p2 rebuild unusable local indexes;

SQL> select index_name,partition_name,status from user_ind_partitions;

INDEX_NAME PARTITION_NAME STATUS

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

IND_TEST_ID_LOCAL P2 USABLE

IND_TEST_ID_LOCAL P1 USABLE

IND_TEST_ID_LOCAL P3 USABLE

----全局索引的状态:

select index_name,table_name,status from user_indexes where index_name='IND_TEST_NAME_GLOBAL';

INDEX_NAME TABLE_NAME STATUS

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

IND_TEST_NAME_GLOBAL TEST UNUSABLE

----重建全局分区索引

alter index IND_TEST_NAME_GLOBAL rebuild;

----索引状态已更改。

select index_name,table_name,status from user_indexes where index_name='IND_TEST_NAME_GLOBAL';

INDEX_NAME TABLE_NAME STATUS

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

IND_TEST_NAME_GLOBAL TEST VALID

----删除分区也会导致全局分区索引失效

alter table test truncate partition p1;

select index_name,partition_name,status from user_ind_partitions;

INDEX_NAME PARTITION_NAME STATUS

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

IND_TEST_ID_LOCAL P2 USABLE

IND_TEST_ID_LOCAL P1 USABLE

IND_TEST_ID_LOCAL P3 USABLE

select index_name,table_name,status from user_indexes where index_name='IND_TEST_NAME_GLOBAL';

INDEX_NAME TABLE_NAME STATUS

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

IND_TEST_NAME_GLOBAL TEST UNUSABLE

看完上述内容，你们掌握oracle分区表的作用是什么的方法了吗？如果还想学到更多技能或想了解更多相关内容，欢迎关注创新互联行业资讯频道，感谢各位的阅读！