普通表转分区表.docx
- 文档编号:2805342
- 上传时间:2022-11-15
- 格式:DOCX
- 页数:19
- 大小:22.57KB
普通表转分区表.docx
《普通表转分区表.docx》由会员分享,可在线阅读,更多相关《普通表转分区表.docx(19页珍藏版)》请在冰豆网上搜索。
普通表转分区表
普通表转分区表
普通表转分区表方法
将普通表转换成分区表有4种方法:
1.Export/importmethod
2.Insertwithasubquerymethod
3.Partitionexchangemethod
4.DBMS_REDEFINITION
具体参考:
How to Partition a Non-partitioned Table [ID 1070693.6]
逻辑导出导入这里就不做说明,我们看看其他三种方法。
2.1插入:
Insertwithasubquerymethod
这种方法就是使用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.1Oracle11g的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.2Oracle10g版本
在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.交换分区:
Partitionexchangemethod
这种方法只是对数据字典中分区和表的定义进行了修改,没有数据的修改或复制,效率最高。
适用于包含大数据量的表转到分区表中的一个分区的操作。
尽量在闲时进行操作。
交换分区的操作步骤如下:
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 表已创建。 SQL> CREATE TABLE dba_p2 as SELECT id,time_fee FROM dba_old WHERE time_fee 表已创建。 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 ---------- -------------- 99999929-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-144
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 普通 分区表