文档章节

非分区表进行分区的方法

Vimeo
 Vimeo
发布于 2016/02/18 14:38
字数 2020
阅读 106
收藏 0


非分区表进行成分区的方法,From Oracle support:(文档 ID 1985005.1)


 A. 通过 Export/import 方法

 B. 通过 Insert with a subquery 方法

 C. 通过 Partition Exchange 方法

 D. 通过 DBMS_REDEFINITION 方法



A. 通过 Export/import 方法


这种方法的实现是先 export 一个非分区表,创建一个新的分区表,然后 import 数据到新创建的分区表中。


1) Export 您的非分区表:


$ exp usr/pswd tables=numbers file=exp.dmp

2) Drop 掉该非分区表:


SQL> drop table numbers;

3) 重新创建该表成为一个分区表:


SQL> create table numbers (qty number(3), name varchar2(15)) partition by range (qty)(partition p1 values less than (501),

partition p2 values less than (maxvalue));

4) 通过 import 的 ignore=y 方式来还原备份的数据:


$ imp usr/pswd file=exp.dmp ignore=y

ignore=y 语句会让 import 忽略掉表的创建,直接加载所有数据。


如果使用 Data Pump export/import(expdp/impdp)您可以采用 impdp 的 table_exists_action 选项,例如 table_exists_action = APPEND 或者 table_exists_action = REPLACE。


您也可以参考 Note 552424.1 Export/Import DataPump Parameter ACCESS_METHOD - How to Enforce a Method of Loading and Unloading Data?


B. 通过 Insert with a subquery 方法


1) 创建一个分区表:


SQL> create table partbl (qty number(3), name varchar2(15)) partition by range (qty) (partition p1 values less than (501),partition p2 values less than (maxvalue));


2) 将原来非分区表中的数据通过子查询 insert 到新创建的分区表中:


SQL> insert into partbl (qty, name) select * from origtbl;


3) 如果您想让新建的分区表与原表名相同,那么 drop 掉原来的非分区表然后重命名新表:


SQL> drop table origtbl;

SQL> alter table partbl rename to origtbl;

您可以通过 direct path insert 和利用并行来改善 insert 的性能。如下的例子演示了如何实现并且如何从执行计划中来验证。


传统的 insert


SQL> insert into partbl (qty, name) select * from origtbl;

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

| Id  | Operation                | Name    |

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

|   0 | INSERT STATEMENT         |         |

|   1 |  LOAD TABLE CONVENTIONAL |         |

|   2 |   TABLE ACCESS FULL      | ORIGTBL |

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

Direct load insert 方式


SQL> insert /*+APPEND*/ into partbl (qty, name) select * from origtbl;

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

| Id  | Operation          | Name    |

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

|   0 | INSERT STATEMENT   |         |

|   1 |  LOAD AS SELECT    |         |

|   2 |   TABLE ACCESS FULL| ORIGTBL |

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

Direct load insert 并且在查询部分开启并行


SQL> insert /*+APPEND PARALLEL*/ into partbl (qty, name) select * from origtbl;

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

| Id  | Operation             | Name     |

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

|   0 | INSERT STATEMENT      |          |

|   1 |  LOAD AS SELECT       |          |

|   2 |   PX COORDINATOR      |          |

|   3 |    PX SEND QC (RANDOM)| :TQ10000 |

|   4 |     PX BLOCK ITERATOR |          |

|*  5 |      TABLE ACCESS FULL| ORIGTBL  |

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

注意以上执行计划中 LOAD AS SELECT 在 PX COORDINATOR 的上面。


Direct load insert 并且在查询部分和 insert 部分都开启并行


SQL>alter session enable parallel dml;

SQL> insert /*+APPEND PARALLEL*/ into partbl (qty, name) select * from origtbl;

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

| Id  | Operation             | Name     |

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

|   0 | INSERT STATEMENT      |          |

|   1 |  PX COORDINATOR       |          |

|   2 |   PX SEND QC (RANDOM) | :TQ10000 |

|   3 |    LOAD AS SELECT     |          |

|   4 |     PX BLOCK ITERATOR |          |

|*  5 |      TABLE ACCESS FULL| ORIGTBL  |

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

注意在以上执行计划中 LOAD AS SELECT 在 PX COORDINATOR 的下面。


另外一种可选的方式是直接通过 select 来创建新的分区表:一次性创建新的分区表并且加载数据。

执行计划同时显示 direct path load 并且 dml 以及 select 部分全部并行。


SQL>alter session enable parallel dml;

SQL> create table partbl (qty, name) partition by range (qty) (partition p1 values less than (501),partition p2 values less than (maxvalue))

  2  as select /*+PARALLEL*/ * from origtbl;

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

| Id  | Operation              | Name     |

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

|   0 | CREATE TABLE STATEMENT |          |

|   1 |  PX COORDINATOR        |          |

|   2 |   PX SEND QC (RANDOM)  | :TQ10000 |

|   3 |    LOAD AS SELECT      |          |

|   4 |     PX BLOCK ITERATOR  |          |

|*  5 |      TABLE ACCESS FULL | ORIGTBL  |

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

C. 通过 Partition Exchange 方法


ALTER TABLE EXCHANGE PARTITION 可以通过交换数据和索引 segment 来将一个分区(或子分区)转换成一个非分区表,也可以将一个非分区表转换成一个分区表的分区(或子分区)。 除了需要更新索引以外,ALTER TABLE ... EXCHANGE PARTITION 命令是一个字典操作不需要数据移动。更多关于此方法的信息参见 Oracle 联机文档(比如 11.2)和 Note 198120.1。


此方法简要步骤如下:


1) 根据所需的分区来创建新的分区表

2) 保持需要交换的非分区表与分区表的分区有相同的结构,并且确保您需要交换的非分区表具有您想要交换的内容

3) 执行:Alter table exchange partition partition_name with table exchange table



注意在交换过程中,所有交换的数据必须满足分区表的分区定义,否则如下错误将抛出:ORA-14099: all rows in table do not qualify for specified partition.


这是因为默认情况下分区交换是有校验的。



例子(基于 SCOTT 示例 schema)

---------


本例创建了与分区表 p_emp 的分区相同结构的交换表。


SQL> CREATE TABLE p_emp

2 (sal NUMBER(7,2))

3 PARTITION BY RANGE(sal)

4 (partition emp_p1 VALUES LESS THAN (2000),

5 partition emp_p2 VALUES LESS THAN (4000));


Table created.



SQL> SELECT * FROM emp;


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

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

      7369 SMITH      CLERK           7902 17-DEC-80        800                    20

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30

      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30

      7566 JONES      MANAGER         7839 02-APR-81       2975                    20

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30

      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30

      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10

      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20

      7839 KING       PRESIDENT            17-NOV-81       5000                    10

      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30

      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

      7900 JAMES      CLERK           7698 03-DEC-81        950                    30

      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10


14 rows selected.


SQL> CREATE TABLE exchtab1 as SELECT sal FROM emp WHERE sal<2000;


Table created.


SQL> CREATE TABLE exchtab2 as SELECT sal FROM emp WHERE sal BETWEEN 2000 AND 3999;


Table created.

--- 官方这里应该是有错误的,应该将where后的sal(加粗部分) 改成 * 才能实现与原emp表一致的分区----


SQL> alter table p_emp exchange partition emp_p1 with table exchtab1;


Table altered.


SQL> alter table p_emp exchange partition emp_p2 with table exchtab2;


Table altered.

D. 通过 DBMS_REDEFINITION 方法

(文档 ID 1481558.1)

PURPOSE


This Note has been written to provide some volume details and timings for the process

to convert a normal table to a partition table using the DBMS_REDEFINITION package.


The note will also include details of objects used by the process (Fast Refresh) and how

these become populated during the procedure.


The procedure is broken down into the following sections:


1.  Create the Partition Table structure required, known as the Interim table.

2.  Execute DBMS_REDEFINITION.can_redef_table...

3.  Execute DBMS_REDEFINITION.start_redef_table...

4.  Execute DBMS_REDEFINITION.sync_interim_table...

5.  Execute DBMS_REDEFINITION.finish_redef_table...

6.  Drop the interim table.


DETAILS


Worked example under 11.2.0.3



--   Initial setup of table to be partitioned.


CREATE TABLE unpar_table (

  a NUMBER, y number,

  name VARCHAR2(100), date_used date);


alter table unpar_table ADD (CONSTRAINT unpar_table_pk PRIMARY KEY (a,y));


--  load table with 1,000,000 rows


begin

        for i in 1 .. 1000

        loop

            for j in 1 .. 1000

            loop

insert into unpar_table values ( i, j, dbms_random.random, sysdate-j );

            end loop;

        end loop;

end;

   /

commit;


PL/SQL procedure successfully completed.

Elapsed: 00:01:56.90


Commit complete.


SQL> EXEC DBMS_STATS.gather_table_stats(user, 'unpar_table', cascade => TRUE);


SELECT   num_rows FROM user_tables WHERE table_name = 'UNPAR_TABLE';

  NUM_ROWS

----------

   1000000


Elapsed: 00:00:00.01


SQL> CREATE TABLE par_table (

    a NUMBER, y number,

    name VARCHAR2(100),date_used DATE)

   PARTITION BY RANGE (date_used)

    (PARTITION unpar_table_12 VALUES LESS THAN (TO_DATE('10/07/2012', 'DD/MM/YYYY')),

     PARTITION unpar_table_15 VALUES LESS THAN (TO_DATE('15/07/2012', 'DD/MM/YYYY')),

     PARTITION unpar_table_MX VALUES LESS THAN (MAXVALUE));


SQL> EXEC Dbms_Redefinition.can_redef_table(USER, 'unpar_table');


PL/SQL procedure successfully completed.


Elapsed: 00:00:00.07


--  This procedure (DBMS_REDEFINITION.start_redef_table) creates a materialized view based on a CTAS, as we can see below with 

--  the PREBUILT container table.

 

SQL> BEGIN

DBMS_REDEFINITION.start_redef_table(

uname => USER,

orig_table => 'unpar_table',

int_table => 'par_table');

END;

/


PL/SQL procedure successfully completed.


Elapsed: 00:00:06.69


select mview_name,container_name, build_mode from user_mviews;

MVIEW_NAME                     CONTAINER_NAME                 BUILD_MOD

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

PAR_TABLE                      PAR_TABLE                      PREBUILT


Elapsed: 00:00:00.13

 

--  Insert 1000 rows into the master table while the DBMS_REDEF is active.

--  This will use the mview log created by the DBMS_REDEFINITION.start_redef_table.

--  Check the MLOG$_<table name> table to confirm these online updates have been recorded.


SQL> begin

        for i in 1001 .. 1010

        loop

            for j in 1001 .. 1100

            loop

insert into unpar_table values ( i, j, dbms_random.random, sysdate-j );

            end loop;

        end loop;

    end;

   /

commit;


Elapsed: 00:00:00.24


SQL> select count(*) from MLOG$_UNPAR_TABLE;


  COUNT(*)

----------

      1000

 

--  Run the dbms_redefinition.sync_interim_table to populate the new table structure (implements a MVIEW FAST REFRESH)

--  with the online updates.  This will purge the mview log of each record applied.

--  This can be run many times and should be, before we do the Finish_REDEF_TABLE.


SQL> BEGIN

dbms_redefinition.sync_interim_table(

uname => USER,

orig_table => 'unpar_table',

int_table => 'par_table');

END;


PL/SQL procedure successfully completed.


Elapsed: 00:00:02.01


ALTER TABLE par_table ADD (CONSTRAINT par_table_pk2 PRIMARY KEY (a,y)); 



EXEC DBMS_STATS.gather_table_stats(USER, 'par_table', cascade => TRUE);


--  Finish_redef_table swaps the table names so the interim table becomes the original table name.

--  After completing this step, the original table is redefined with the attributes and data of the interim table. 

--  The original table is locked briefly during this procedure.


BEGIN

dbms_redefinition.finish_redef_table(

uname => USER,

orig_table => 'unpar_table',

int_table => 'par_table');

END;

/

 

--  Note, both tables will now be synchronised.


select count(*) from par_table ;

  COUNT(*)

----------

   1001000


select count(*) from unpar_table ;

  COUNT(*)

----------

   1001000

 

--  Dictionary views to confirm the change of strructure of our original table "UNPAR_TABLE".


SELECT partitioned FROM user_tables WHERE table_name = 'UNPAR_TABLE';


PAR

---

YES


SELECT partition_name, num_rows FROM user_tab_partitions WHERE table_name = 'UNPAR_TABLE';



PARTITION_NAME                   NUM_ROWS

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

UNPAR_TABLE_12                     980000

UNPAR_TABLE_15                       5000

UNPAR_TABLE_MX                      16000

 

--  At this point the Interim table can be dropped.


drop TABLE par_table  cascade constraints;

 


Note for previous versions.



For tests done under 11.1.0.7 the timings for the sync_interim_table where the interim table is large (1,000,000 in our case) the timings for this are considerably longer.  This was not reviewed in depth, but initial investigation looks like the mview refresh for DBMS_REDEFINITION has been updated during the MERGE cycle.


example:


11.1.0.7 

SYNC of 1000 rows --         Elapsed: 00:01:36.30     (1.5 minutes)


本文转载自:

Vimeo
粉丝 5
博文 107
码字总数 35893
作品 0
南昌
数据库管理员
私信 提问
加载中

评论(1)

jasonkavay
jasonkavay
非常不错!博主,我们做了一个帮助博主推广博客的app叫同行说,只需复制文章链接即可发布给更多程序员们看到哦,欢迎体验哈,一起发扬分享精神~
风险预警·11g容易被忽略的导入性能问题

前言:某大型国有银行一套关键系统10g升级到11g,老K负责升级后第一天早上的运行保障;在升级前甲方客户已经先后做了各种测试,以保证升级后不会存在任何性能问题。然而,事与愿违,老K刚到现...

DBA小y
2017/07/28
0
0
风险预警·11g容易被忽略的导入性能问题

前言 某大型国有银行一套关键系统10g升级到11g,老K负责升级后第一天早上的运行保障;在升级前甲方客户已经先后做了各种测试,以保证升级后不会存在任何性能问题。然而,事与愿违,老K刚到现...

DBA小y
2017/07/28
0
0
MaxCompute小文件问题优化方案

小文件背景知识 小文件定义 分布式文件系统按块Block存放,文件大小比块大小小的文件(默认块大小为64M),叫做小文件。 如何判断存在小文件数量多的问题 查看文件数量 判断小文件数量多的标...

云花
2018/12/20
0
0
Oracle 12.2新特性----在线把非分区表转为分区表

在Oracle12.2版本之前,如果想把一个非分区表转为分区表常用的有这几种方法:1、建好分区表然后insert into select 把数据插入到分区表中;2、使用在线重定义(DBMS_REDEFINITION)的方法。它们...

hbxztc
2017/07/21
0
0
MySQL 分区性能探究(持续更新中)

参考:http://imysql.com/node/30 <一> 数据准备 1.1 创建分区表 parttab create table parttab( c1 int DEFAULT null, c2 varchar(30) DEFAULT null, c3 date default null) engine = myisa......

平江夜弹
2015/10/15
114
2

没有更多内容

加载失败,请刷新页面

加载更多

0.01-Win10安装linux子系统

一、安装Debian子系统 -1、控制面板设置: -1.1、打开“控制面板” —— “程序” —— “启用或关闭Windows功能” —— 勾选 “适用于Linux的Windows子系统” -2、设置: -2.1、打开“设置”...

静以修身2025
昨天
2
0
init 0-6 (启动级别:init 0,1,2,3,4,5,6)

启动级别: init 0,1,2,3,4,5,6 这是个很久的知识点了,只是自己一直都迷迷糊糊的,今天在翻出来好好理解下。。 0: 停机 1:单用户形式,只root进行维护 2:多用户,不能使用net file system...

圣洁之子
昨天
2
0
Android Camera HAL浅析

1、Camera成像原理介绍 Camera工作流程图 Camera的成像原理可以简单概括如下: 景物(SCENE)通过镜头(LENS)生成的光学图像投射到图像传感器(Sensor)表面上,然后转为电信号,经过A/D(模数转...

天王盖地虎626
昨天
2
0
聊聊Elasticsearch的ProcessProbe

序 本文主要研究一下Elasticsearch的ProcessProbe ProcessProbe elasticsearch-7.0.1/server/src/main/java/org/elasticsearch/monitor/process/ProcessProbe.java public class ProcessProb......

go4it
昨天
3
0
mysql PL(procedure language)流程控制语句

在MySQL中,常见的过程式SQL语句可以用在存储体中。其中包括IF语句、CASE语句、LOOP语句、WHILE语句、ITERATE语句和LEAVE语句,它们可以进行流程控制。 IF语句相当于Java中的if()...else if(...

edison_kwok
昨天
3
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部