oracle10g-分区表的管理

一、 分区表的限制 ........................................................................ 2 二、 Oracle 10g提供的几种分区类型 .......................................... 2 三、 相关的视图(dba_,all_,user_) ................................................. 2 四、 Range分区............................................................................. 2 五、 Hash分区 .............................................................................. 2 六、 List分区 ................................................................................. 3 七、 range-hash ............................................................................. 3 八、 range-list ................................................................................ 4 九、 普通表转分区表方法 ............................................................ 4 十、 分区表的其他操作 .............................................................. 10 十一、 分区表的索引 ................................................................... 12

一、 分区表的限制

在oracle 10g中最多支持：1024k-1个分区

二、 Oracle 10g提供的几种分区类型

    

范围分区（range）; 哈希分区（hash）; 列表分区（list）;

范围－哈希复合分区（range-hash）; 范围－列表复合分区（range-list）;

三、 相关的视图(dba_,all_,user_)

dba_tab_partitons dba_tab_subpartitons dba_ind_partitons

四、 Range分区

create table pdba (id number, time date) tablespace DATA1TBS01 partition by range (time) (

partition p1 values less than (to_date('2010-10-1', 'yyyy-mm-dd')), partition p2 values less than (to_date('2010-11-1', 'yyyy-mm-dd')), partition p3 values less than (to_date('2010-12-1', 'yyyy-mm-dd')), partition p4 values less than (maxvalue) ) ;

五、 Hash分区

create table test (

transaction_id number primary key, item_id number(8) not null ) tablespace DATA1TBS01

partition by hash(transaction_id)

(

partition part_01 tablespace tablespace01, partition part_02 tablespace tablespace02, partition part_03 tablespace tablespace03 );

六、 List分区

create table custaddr(

id varchar2(15 byte) not null, areacode varchar2(4 byte) ) tablespace DATA1TBS01 partition by list (areacode) (

partition t_list025 values ('025'), partition t_list372 values ('372') , partition t_list510 values ('510'), partition p_other values (default) );

七、 range-hash

create table emp_sub_template ( deptno number,

empname varchar(32), grade number)

tablespace DATA1TBS01 partition by range(deptno)

subpartition by hash(empname) subpartition template

(subpartition a tablespace ts1, subpartition b tablespace ts2, subpartition c tablespace ts3, subpartition d tablespace ts4 )

(partition p1 values less than (1000), partition p2 values less than (2000), partition p3 values less than (maxvalue) );

八、 range-list

create table quarterly_regional_sales

(deptno number, item_no varchar2(20),

txn_date date, txn_amount number, state varchar2(2)) tablespace DATA1TBS01

partition by range (txn_date) subpartition by list (state)

(partition q1_1999 values less than (to_date('1-apr-1999','dd-mon-yyyy')) (subpartition q1_1999_northwest values ('or', 'wa'),

subpartition q1_1999_southwest values ('az', 'ut', 'nm'), subpartition q1_1999_northeast values ('ny', 'vm', 'nj'), subpartition q1_1999_southeast values ('fl', 'ga'), subpartition q1_1999_northcentral values ('sd', 'wi'), subpartition q1_1999_southcentral values ('ok', 'tx') ),

partition q4_1999 values less than ( to_date('1-jan-2000','dd-mon-yyyy')) (subpartition q4_1999_northwest values ('or', 'wa'),

subpartition q4_1999_southwest values ('az', 'ut', 'nm'), subpartition q4_1999_northeast values ('ny', 'vm', 'nj'), subpartition q4_1999_southeast values ('fl', 'ga'), subpartition q4_1999_northcentral values ('sd', 'wi'), subpartition q4_1999_southcentral values ('ok', 'tx') ) );

九、 普通表转分区表方法

1. 插入：Insert with a subquery method

sql> create table pdba (id, time) partition by range (time)

(partition p1 values less than (to_date('2010-10-1', 'yyyy-mm-dd')), partition p2 values less than (to_date('2010-11-1', 'yyyy-mm-dd')), partition p3 values less than (to_date('2010-12-1', 'yyyy-mm-dd')), partition p4 values less than (maxvalue)) as select id, time_fee from dba;

表已创建。

SQL> select table_name,partition_name from user_tab_partitions where table_name='PDBA';

TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------

PDBA P1 PDBA P2 PDBA P3 PDBA P4 sql> select count(*) from pdba partition (p1);

count(*) ---------- 1718285

sql> select count(*) from pdba partition (p2);

count(*) ---------- 183667

sql> select count(*) from pdba partition (p3);

count(*) ---------- 188701

sql> select count(*) from pdba partition (p4);

count(*) ----------

622582

现在分区表已经建好了，但是表名不一样，需要用rename对表重命名一下： SQL> rename dba to dba_old; 表已重命名。

SQL> rename pdba to dba;

表已重命名。

SQL> select table_name,partition_name from user_tab_partitions where table_name='DBA';

TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------ DBA P1 DBA P2 DBA P3 DBA P4

2. 交换分区：Partition exchange method

这种方法只是对数据字典中分区和表的定义进行了修改，没有数据的修改或复制，效率最高。

适用于包含大数据量的表转到分区表中的一个分区的操作。尽量在闲时进行操作。 交换分区的操作步骤如下：

1. 创建分区表，假设有2个分区，P1，P2. 2. 创建表A存放P1规则的数据。 3. 创建表B 存放P2规则的数据。

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

创建分区表：

sql> create table p_dba (id number,time date) partition by range(time) (

partition p1 values less than (to_date('2010-09-1', 'yyyy-mm-dd')), partition p2 values less than (to_date('2010-11-1', 'yyyy-mm-dd')) );

创建2个分别对应分区的基表：

SQL> CREATE TABLE dba_p1 as SELECT id,time_fee FROM dba_old WHERE time_fee表已创建。

SQL> CREATE TABLE dba_p2 as SELECT id,time_fee FROM dba_old WHERE

time_feeTO_DATE('2010-09-1', 'YYYY-MM-DD');

表已创建。

讲2个基表与2个分区进行交换：

SQL> alter table p_dba exchange partition p1 with table dba_p1;

表已更改。

SQL> alter table p_dba exchange partition p2 with table dba_p2;

表已更改。

查询2个分区：

SQL> select count(*) from p_dba partition(p1);

COUNT(*) ---------- 1536020

SQL> select count(*) from p_dba partition(p2);

COUNT(*) ---------- 365932

注意：数据和之前的基表一致。

查询原来的2个基表：

SQL> select count(*) from dba_p2;

COUNT(*) ---------- 0

SQL> select count(*) from dba_p1;

COUNT(*) ---------- 0

注意： 2个基表的数据变成成0。

在这里我们看一个问题，一般情况下，我们在创建分区表的时候，都会有一个其他分区，用来存放不匹配分区规则的数据。 在这个例子中，我只创建了2个分区，没有创建maxvalue分区。 现在我来插入一条不满足规则的数据，看结果：

SQL> insert into p_dba values(999999,to_date('2012-12-29','yyyy-mm-dd')); insert into p_dba values(999999,to_date('2012-12-29','yyyy-mm-dd')) * 第 1 行出现错误:

ORA-14400: 插入的分区关键字未映射到任何分区

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

3. 使用在线重定义：DBMS_REDEFINITION

a) 创建基本表和索引

sql> conn icd/icd; 已连接。

sql> create table unpar_table ( id number(10) primary key,

create_date date ); 表已创建。

sql> insert into unpar_table select rownum, created from dba_objects; 已创建72288行。

sql> create index create_date_ind on unpar_table(create_date); 索引已创建。 sql> commit; 提交完成。

b) 收集表的统计信息

sql> exec dbms_stats.gather_table_stats('icd', 'unpar_table', cascade => true); pl/sql 过程已成功完成。

c) 创建临时分区表

sql> create table par_table (id number primary key, time date) partition by range (time) (partition p1 values less than (to_date('2004-7-1', 'yyyy-mm-dd')), partition p2 values less than (to_date('2005-1-1', 'yyyy-mm-dd')), partition p3 values less than (to_date('2005-7-1', 'yyyy-mm-dd')), partition p4 values less than (maxvalue)); 表已创建。

d) 进行重定义操作

1) 检查重定义的合理性

sql> exec dbms_redefinition.can_redef_table('icd', 'unpar_table');

pl/sql 过程已成功完成。

2) 如果1) 没有问题，开始重定义，这个过程可能要等一会。 这里要注意：如果分区表和原表列名相同，可以用如下方式进行： SQL> BEGIN

DBMS_REDEFINITION.start_redef_table( uname => 'ICD',

orig_table => 'unpar_table', int_table => 'par_table'); END; /

如果分区表的列名和原表不一致，那么在开始重定义的时候，需要重新指定映射关系： SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE( 'ICD',

'unpar_table', 'par_table',

'ID ID, create_date TIME', -- 在这里指定新的映射关系 DBMS_REDEFINITION.CONS_USE_PK);

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

3) 同步新表，这是可选的操作 SQL> BEGIN

dbms_redefinition.sync_interim_table( uname => 'ICD',

orig_table => 'unpar_table', int_table => 'par_table'); END; /

PL/SQL 过程已成功完成。

4) 创建索引，在线重定义只重定义数据，索引还需要单独建立。 sql> create index create_date_ind2 on par_table(time);

索引已创建。

5) 收集新表的统计信息

sql> exec dbms_stats.gather_table_stats('icd', 'par_table', cascade => true);

pl/sql 过程已成功完成。 6) 结束重定义 SQL> BEGIN

dbms_redefinition.finish_redef_table( uname => 'ICD',

orig_table => 'unpar_table', int_table => 'par_table'); END; /

PL/SQL 过程已成功完成。 结束重定义的意义：

基表unpar_table 和临时分区表par_table 进行了交换。 此时临时分区表par_table成了普通表，我们的基表unpar_table成了分区表。

我们在重定义的时候，基表unpar_table是可以进行DML操作的。 只有在2个表进行切换的时候会有短暂的锁表。

e) 删除临时表

SQL> DROP TABLE par_table; 表已删除。

f) 索引重命名

SQL> ALTER INDEX create_date_ind2 RENAME TO create_date_ind; 索引已更改。

g) 验证

sql> select partitioned from user_tables where table_name = 'UNPAR_TABLE'; par --- yes

sql> select partition_name from user_tab_partitions where table_name = 'UNPAR_TABLE'; partition_name

------------------------------ p1 p2

p3 p4

sql> select count(*) from unpar_table; count(*) ---------- 72288

sql> select count(*) from unpar_table partition (p4);

count(*) ---------- 72288

十、 分区表的其他操作

1. 添加新的分区

SQL> create table custaddr (

id varchar2(15 byte) not null, areacode varchar2(4 byte) )

partition by list (areacode) (

partition t_list556 values ('556') tablespace icd_service, partition p_other values (default)tablespace icd_service );

alter table custaddr drop partition p_other;

alter table custaddr add partition t_list551 values('551') tablespace icd_service; alter table custaddr add partition p_other values (default) tablespace icd_service;

通过以下语句查看结果

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

对于局部索引，oracle会自动增加一个局部分区索引。 通过以下语句查看结果

select owner,index_name,table_name,partitioning_type from dba_part_indexes where table_name='CUSTADDR';

2. 添加子分区

create table F_B_MAN_ACCT_INFO_L_10 (

BANK_ID VARCHAR2(7) not null, MAN_ACCT_NO VARCHAR2(40) not null, CURR_TYPE VARCHAR2(3) not null, COM_ID VARCHAR2(8) not null, BAL_ACCT_NO VARCHAR2(40) not null, CAP_ACCT_NO VARCHAR2(40) not null, MAN_ACCT_BAL NUMBER(20,2) not null, MAN_ACCT_FRZ_MON NUMBER(20,2) not null, ST_DATE VARCHAR2(8) not null )

tablespace users

PARTITION BY RANGE (ST_DATE) SUBPARTITION BY LIST (COM_ID) SUBPARTITION TEMPLATE (

SUBPARTITION c00000000 VALUES('00000000') tablespace DATA1TBS01 ) (

PARTITION d18991231 VALUES LESS THAN('19000101') );

alter table F_B_MAN_ACCT_INFO_L_10 MODIFY partition d18991231 add subpartition c00000001 VALUES('00000001') tablespace DATA1TBS01;

3. split 分区拆分

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

--注意这里红色的地方，如果是Range类型的，使用at，List使用Values。

分区表会自动维护局部分区索引。全局索引会失效，需要进行rebuild。

4. 合并分区Merge

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

5. 移动分区

alter table custaddr move partition P_OTHER tablespace icd_service;

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

6. Truncate分区

alter table custaddr truncate partition(T_LIST556);

7. Drop分区

alter table custaddr drop partition T_LIST551;

十一、 分区表的索引

分区索引分为本地(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 icd_service, partition p_other tablespace icd_service )

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

全局分区索引不支持非前缀的分区索引，如果创建，报错如下： SQL> create index i_time_global on PDBA(id) global --索引引导列 2 partition by range(time) --分区建

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

5 );

partition by range(time)

* 第 2 行出现错误:

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

1. Local 本地索引

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

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

（2） 前缀和非前缀索引都可以支持索引分区消除，前提是查询的条件中包含索引分区键。 （3） 局部索引只支持分区内的唯一性，无法支持表上的唯一性，因此如果要用局部索引去给表做唯一性约束，则约束中必须要包括分区键列。

（4） 局部分区索引是对单个分区的，每个分区索引只指向一个表分区；全局索引则不然，一个分区索引能指向n个表分区，同时，一个表分区，也可能指向n个索引分区，对分区表中的某个分区做truncate或者move，shrink等，可能会影响到n个全局索引分区，正因为这点，局部分区索引具有更高的可用性。 （5） 位图索引必须是局部分区索引。 （6） 局部索引多应用于数据仓库环境中。

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

示例：

sql> create index ix_custaddr_local_id on custaddr(id) local;

索引已创建。

和下面SQL 效果相同，因为local索引就是分区索引： create index ix_custaddr_local_id_p on custaddr(id) local (

partition t_list556 tablespace icd_service, partition p_other tablespace icd_service )

SQL> create index ix_custaddr_local_areacode on custaddr(areacode) local; 索引已创建。

验证2个索引的类型：

SQL> 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是非前缀索引。

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.

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

sql> create index ix_custaddr_ global_id on custaddr(id) global; 索引已创建。

示例2：全局分区索引，只支持Range 分区和Hash 分区： （1）创建2个测试分区表：

sql> create table pdba (id number, time date) partition by range (time) (

partition p1 values less than (to_date('2010-10-1', 'yyyy-mm-dd')), partition p2 values less than (to_date('2010-11-1', 'yyyy-mm-dd')), partition p3 values less than (to_date('2010-12-1', 'yyyy-mm-dd')), partition p4 values less than (maxvalue) );

表已创建。

SQL> 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：全局分区索引

SQL> 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) );

索引已创建。

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

SQL> 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 分区索引必须加上前缀

SQL> create index i_time_global on PDBA(time) global 2 partition by range(time)

3 (partition p1 values less than (TO_DATE('2010-12-1', 'YYYY-MM-DD')), 4 partition p2 values less than (maxvalue) 5 );

索引已创建。

--有前缀的分区索引

SQL> 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

SQL> CREATE INDEX ix_hash ON PDBA (id,time) GLOBAL PARTITION BY HASH (id) (PARTITION p1, PARTITION p2, PARTITION p3, PARTITION p4);

索引已创建。

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

3. 索引重建问题

1) 分区索引

对于分区索引，不能整体进行重建，只能对单个分区进行重建。语法如下： Alter index idx_name rebuild partition index_partition_name [online nologging] 说明：

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

nologging：表示建立索引的时候不生成日志，加快速度。 alter index I_TIME_GLOBAL rebuild partition p1 online nologging; 如果要重建分区索引，只能drop表原索引，再重新创建：

SQL>create index loc_xxxx_col on xxxx(col) local tablespace SYSTEM; 这个操作要求较大的临时表空间和排序区。

2) 全局索引

Oracle 会自动维护分区索引，对于全局索引，如果在对分区表操作时，没有指定update index，则会导致全局索引失效，需要重建。 alter table pdba drop partition P4 UPDATE INDEXES; 重建全局索引命令如下：

Alter index idx_name rebuild [online nologging] 示例：

SQL> Alter index ix_pdba_global rebuild online nologging;