三通IT学院 门户 数据库 查看内容

2014-8-16 09:01
Oracle 分区表的技术与应用总结 二

摘要 : 2.1插入: Insert with a subquery method 这种方法就是使用insert 来实现。 当然在创建分区表的时候可以一起插入数据,也可以创建好后在insert 进去。 这种方法采用DDL语句,不产生UNDO,只产生少量REDO,建表完 ...

时光与梦2014-8-16 09:0130314
原作者: Liuxuebo
2.1  插入: Insert with a subquery method
     这种方法就是使用insert 来实现。 当然在创建分区表的时候可以一起插入数据,也可以创建好后在insert 进去。 这种方法采用DDL语句,不产生UNDO,只产生少量REDO,建表完成后数据已经在分布到各个分区中。
   
SQL> select count(*) from dba;
  COUNT(*)
----------
   2713235
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
会话已更改。

SQL> select time_fee from dba where rownum<5;
TIME_FEE
-------------------
2011-02-17 19:29:09
2011-02-17 19:29:15
2011-02-17 19:29:18
2011-02-17 19:29:20
SQL>

2.1.1  Oracle 11g的Interval
       在11g里的Interval创建,这种方法对没有写全的分区会自动创建。 比如我这里只写了1月日期,如果插入的数据有其他月份的,会自动生成对应的分区。

/* Formatted on 2011/03/02 15:41:09 (QP5 v5.115.810.9015) */
CREATE TABLE intervaldave
PARTITION BY RANGE (time_fee)
   INTERVAL ( NUMTOYMINTERVAL (1, 'MONTH') )
   (PARTITION part1
       VALUES LESS THAN (TO_DATE ('01/12/2010', 'MM/DD/YYYY')))
AS
   SELECT   ID, TIME_FEE FROM DAVE;


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

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
INTERVALDAVE                   PART1
INTERVALDAVE                   SYS_P24
INTERVALDAVE                   SYS_P25
INTERVALDAVE                   SYS_P26
INTERVALDAVE                   SYS_P33
INTERVALDAVE                   SYS_P27
INTERVALDAVE                   SYS_P28

2.1.2  Oracle 10g 版本
       在10g里面,我需要写全所有的分区。

sql> create table pdba (id, time) partition by range (time)
  2 (partition p1 values less than (to_date('2010-10-1', 'yyyy-mm-dd')),
  3    partition p2 values less than (to_date('2010-11-1', 'yyyy-mm-dd')),
  4    partition p3 values less than (to_date('2010-12-1', 'yyyy-mm-dd')),
  5    partition p4 values less than (maxvalue))
  6    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
sql>

现在分区表已经建好了,但是表名不一样,需要用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.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
  2  (id number,time date)
  3  partition by range(time)
  4  (
  5  partition p1 values less than (to_date('2010-09-1', 'yyyy-mm-dd')),
  6  partition p2 values less than (to_date('2010-11-1', 'yyyy-mm-dd'))
  7  );
表已创建。

注意:我这里只创建了2个分区,没有创建存放其他数据的分区。

创建2个分别对应分区的基表:
SQL> CREATE TABLE dba_p1 as SELECT id,time_fee FROM dba_old WHERE  time_fee<TO_DATE('2010-09-1', 'YYYY-MM-DD');
表已创建。

SQL> CREATE TABLE dba_p2 as SELECT id,time_fee FROM dba_old WHERE  time_fee<TO_DATE('2010-11-1', 'YYYY-MM-DD') and time_fee>TO_DATE('2010-09-1', 'YYYY-MM-DD');
表已创建。

SQL> select count(*) from dba_p1;
  COUNT(*)
----------
   1536020
SQL> select count(*) from dba_p2;
  COUNT(*)
----------
    365932

SQL>

将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: 插入的分区关键字未映射到任何分区
SQL> insert into p_dba values(999999,to_date('2009-12-29','yyyy-mm-dd'));
已创建 1 行。
SQL> select * from p_dba where id=999999;

        ID TIME
---------- --------------
    999999 29-12月-09
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
会话已更改。
SQL> select * from p_dba where id=999999;

        ID TIME
---------- -------------------
    999999 2009-12-29 00:00:00
SQL>

通过这个测试可以清楚,如果插入的数据不满足分区规则,会报ORA-14400错误。

2.3 . 使用在线重定义:DBMS_REDEFINITION

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

关于DBMS_REDEFINITION的介绍,参考官方连接:
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_redefi.htm#CBBFDJBC

关于用在线重定义创建分区表,参考:
How To Partition Existing Table Using DBMS_Redefinition [ID 472449.1]
http://blog.csdn.net/tianlesoftware/archive/2011/03/02/6218693.aspx

这个功能只在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。它已经是普通表。


下面看一个示例:

1. 创建基本表和索引
sql> conn icd/icd;
已连接。
sql> create table unpar_table (
  2  id number(10) primary key,
  3  create_date date
  4  );
表已创建。
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;
提交完成。

2. 收集表的统计信息
sql> exec dbms_stats.gather_table_stats('icd', 'unpar_table', cascade => true);
pl/sql 过程已成功完成。

3. 创建临时分区表
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)
);
表已创建。

4. 进行重定义操作

4.1 检查重定义的合理性
sql> exec dbms_redefinition.can_redef_table('icd', 'unpar_table');
pl/sql 过程已成功完成。

4.2 如果4.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
);

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

4.3 同步新表,这是可选的操作
SQL> BEGIN
  dbms_redefinition.sync_interim_table(
   uname => 'ICD',
   orig_table => 'unpar_table',
   int_table => 'par_table'
);
  END;
  /
PL/SQL 过程已成功完成。

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

4.5 收集新表的统计信息
sql> exec dbms_stats.gather_table_stats('icd', 'par_table', cascade => true);
pl/sql 过程已成功完成。

4.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个表进行切换的时候会有短暂的锁表。

5. 删除临时表
SQL> DROP TABLE par_table;
表已删除。

6. 索引重命名
SQL> ALTER INDEX create_date_ind2 RENAME TO create_date_ind;
索引已更改。

7. 验证
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
sql>


三. 分区表的其他操作
3.1 添加新的分区
添加新的分区有2中情况:
(1)原分区里边界是maxvalue或者default。 这种情况下,我们需要把边界分区drop掉,加上新分区后,在添加上新的分区。 或者采用split,对边界分区进行拆分。
(2)没有边界分区的。 这种情况下,直接添加分区就可以了。



以边界分区添加新分区示例:
(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
   );
表已创建。
SQL> create index ix_custaddr_id on custaddr(id)
  local (
   partition t_list556 tablespace icd_service,
   partition p_other tablespace icd_service
  );
索引已创建。

(2)插入几条测试数据:
SQL> insert into custaddr values('1','556');
已创建 1 行。
SQL> insert into custaddr values('2','551');
已创建 1 行。
SQL> insert into custaddr values('3','555');
已创建 1 行。
SQL> commit;
提交完成。
SQL> select * from custaddr;
ID              AREA
--------------- ----
1               556
2               551
3               555
SQL> select * from custaddr partition(t_list556);
ID              AREA
--------------- ----
1               556
SQL>

(3)删除default分区
sql> alter table custaddr drop partition p_other;
表已更改。
sql> select table_name,partition_name from user_tab_partitions where table_name='CUSTADDR';
table_name                     partition_name
------------------------------ ------------------------------
custaddr                       t_list556

(4)添加新分区
SQL> alter table custaddr add partition t_list551 values('551') tablespace icd_service;
表已更改。
SQL> select table_name,partition_name from user_tab_partitions where table_name='CUSTADDR';

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
CUSTADDR                       T_LIST556
CUSTADDR                       T_LIST551
(5)添加default 分区
SQL> alter table custaddr add partition p_other values (default)  tablespace icd_service;
表已更改。
SQL> select table_name,partition_name from user_tab_partitions where table_name='CUSTADDR';

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
CUSTADDR                       T_LIST556
CUSTADDR                       T_LIST551
CUSTADDR                       P_OTHER

(6)对于局部索引,oracle会自动增加一个局部分区索引。验证一下:
sql> select owner,index_name,table_name,partitioning_type from dba_part_indexes where index_name='ix_custaddr_id';
owner            index_name           table_name
---------------------- ------------------------------ ------------------
icd             ix_custaddr_id         custaddr
sql> select index_owner,index_name,partition_name from dba_ind_partitions  where index_name='ix_custaddr_id';
index_owner           index_name                  partition_name
------------------------------ ------------------------------ ------------------
icd                  ix_custaddr_id                 p_other
icd                  ix_custaddr_id                 t_list551
icd                  ix_custaddr_id                 t_list556

分区索引自动创建了。

3.2  split 分区拆分
       在3.1 中,我们说明了可以使用split的方式来添加分区。 这里我们用split方法继续上面的实验。

sql> 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。
SQL> select table_name,partition_name from user_tab_partitions where table_name='CUSTADDR';
TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
CUSTADDR                       T_LIST556
CUSTADDR                       T_LIST551
CUSTADDR                       T_LIST552
CUSTADDR                       P_OTHER

SQL> select index_owner,index_name,partition_name from dba_ind_partitions  where index_name='IX_CUSTADDR_ID';

index_owner             index_name                     partition_name
--------------------------- ------------------------------ ------------------
icd                            ix_custaddr_id                 p_other
icd                            ix_custaddr_id                 t_list551
icd                            ix_custaddr_id                 t_list552
icd                            ix_custaddr_id                 t_list556

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

鲜花

握手

雷人

路过

鸡蛋
收藏 分享 邀请
发表评论

最新评论

引用 目前还好 2014-8-15 10:10
早上辛苦啦 ,版主,好文赞一个!
引用 liubq919 2016-8-3 09:17
坚持学习,成就技术技术之王!
引用 renchao7 2016-8-16 11:05
目前还好 发表于 2014-8-15 10:10
早上辛苦啦 ,版主,好文赞一个!

谢谢  辛苦了
引用 renchao7 2016-8-16 11:06
早上辛苦啦 ,版主,好文赞一个!
引用 renchao7 2016-8-16 11:06
早上辛苦啦 ,版主,好文赞一个!
引用 NAXLCQ 2016-8-19 09:52
早上辛苦啦 ,版主,好文赞一个!
引用 kkltkklt 2016-8-19 09:55
RE: Oracle 分区表的技术与应用总结 二
引用 stit2012 2016-8-19 21:03
谢谢分享。。。
引用 caara1019 2016-8-25 17:02

早上辛苦啦 ,版主,好文赞一个!
引用 nick_liu24 2016-9-3 09:42
very good         
引用 小小学者 2016-9-3 09:44
very good        
引用 793985753 2016-9-4 13:50
1111111111111111111
引用 qq_3322 2016-9-9 09:00
早上辛苦啦 ,版主,好文赞一个!

查看全部评论(14)

返回顶部