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

2014-9-10 11:46
oracle 数据移植 copy from ,create table as,insert into,,和 表对象的验证

摘要 : 在日常工作中有时经常要进行数据的迁移,有跨版本的还有表的复制,oracle提供了很多工具exp/imp, expdump/impdump rman .....等还有几个在SQL/PLUS中直接执行的,在很多时候这种方法更为直接简单 一:create table ...

时光与梦2014-9-10 11:4613497
原作者: Liuxuebo
在日常工作中有时经常要进行数据的迁移,有跨版本的还有表的复制,oracle提供了很多工具exp/imp, expdump/impdump
rman .....等还有几个在SQL/PLUS中直接执行的,在很多时候这种方法更为直接简单
一:create table as select * from XXX 这种方法是创建一个不存在的表和已存在的表结构完全一样,可以选择要复制的数据
    这种方法性能最快

二:copy命令处理大批量数据复制,这种方法可以进行垮台复制和也可以跨用户复制
COPY {FROM database | TO database | FROM database TO database} {APPEND|CREATE|INSERT|REPLACE} destination_table [(column, column, column, ...)]
USING query

分部分来解释:
COPY – 这个不太需要解释,主命令,声明要执行COPY操作
From Database – 源数据库
To Database – 目标数据库
 此处注意花括号中有三种可选的写法(以”|”隔开),如果源数据表和目标数据表在同一个Schema中,则可以只写
    From Database,也可以只写To Database,当然还可以是第三种写法,把From Database和To Database写全。
    但如果源数据表和目标数据表不在同一个Schema中,则必须用第三种写法,即把From Database和To Database都写全
 From Database和To Database的格式是一样的:USERID/PASSWORD@SID,这个大家都应该很熟悉了。
{APPEND|CREATE|INSERT|REPLACE} – 声明操作数据的方式,下面分别解释一下:
Append – 向已有的目标表中追加记录,如果目标表不存在,自动创建,这种情况下和Create等效。
Create – 创建目标表并且向其中追加记录,如果目标表已经存在,则会返回错误。
Insert – 向已有的目标表中插入记录,与Append不同的是,如果目标表不存在,不自动创建而是返回错误。
Replace – 用查询出来的数据覆盖已有的目标表中的数据,如果目标表不存在,自动创建。
destination_table – 目标表的名字
[(column, column, column, ...)] – 可以指定目标表中列的名字,如果不指定,则自动使用Query中的列名。
USING query – 查询语句,交流的数据来自这儿。
2.2 使用范例
通过几个例子来看一下SQL*Plus Copy 命令的使用:
2.2.1 在同一个服务器的同一个Schema中复制数据:
同时指定From database和To database
SQL> copy from scott/tiger@lsj to  scott/tiger@lsj create dept1 using select * from dept;
只指定From Database
SQL> copy from scott/tiger@lsj create dept2 using select * from dept;
只指定To Database
SQL> copy to scott/tiger@lsj create dept3 using select * from dept;
2.2.2 在同一个服务器的不同Schema中复制数据:
这种情况下必须同时指定From Database和To Database
SQL> copy from scott/tiger@lsj to  lsjdemo/lsjdemo@lsj create dept using select * from dept;
注意这种情况下,using select * from dept 中并不需要使用scott.demp的形式。
2.2.3 在不同的服务器间复制数据:
SQL> conn lsj/lsj@sunserve
已连接。
SQL> copy from scott/tiger@lsj to  lsj/lsj@sunserve create dept using select * from dept;
三:insert into a select * from b where .....
此种方法是向一个已存在的表中插入需求的数据,且从另一个表中查出
实验:
SQL> select count(*) from test;
  COUNT(*)
----------
   2410688
SQL> set timing on
SQL> create table test1 as select *from test;
Table created.
Elapsed: 00:00:25.89
SQL> copy from scott/tiger@orcl create test2 using select * from test;
Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)
Table TEST2 created.
   2410688 rows selected from scott@orcl.
   2410688 rows inserted into TEST2.
   2410688 rows committed into TEST2 at DEFAULT HOST connection.
   
SQL> create table test3 as select * from test where 1=2;
Table created.
Elapsed: 00:00:00.06
SQL> commit;
Commit complete.
Elapsed: 00:00:00.01
SQL> select * from test3;
no rows selected
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 3306317399
---------------------------------------------------------------------------
| Id  | Operation   | Name  | Rows  | Bytes | Cost (%CPU)| Time   |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |   | 1 |   207 | 2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TEST3 | 1 |   207 | 2   (0)| 00:00:01 |
---------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
  19  recursive calls
   0  db block gets
  30  consistent gets
   0  physical reads
   0  redo size
       1343  bytes sent via SQL*Net to client
512  bytes received via SQL*Net from client
   1  SQL*Net roundtrips to/from client
   0  sorts (memory)
   0  sorts (disk)
   0  rows processed
SQL> insert into test3 select * from test;
2410688 rows created.
Elapsed: 00:00:23.51
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
----------------------------------------------------------------------------------
| Id  | Operation   | Name  | Rows  | Bytes | Cost (%CPU)| Time  |
----------------------------------------------------------------------------------
|   0 | INSERT STATEMENT  |  | 75334 |  7136K|   301   (1)| 00:00:04 |
|   1 |  LOAD TABLE CONVENTIONAL | TEST3 |  |  |       |   |
|   2 |   TABLE ACCESS FULL  | TEST  | 75334 |  7136K|   301   (1)| 00:00:04 |
----------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
       1262  recursive calls
     302089  db block gets
     100850  consistent gets
   1  physical reads
  279857200  redo size
849  bytes sent via SQL*Net to client
792  bytes received via SQL*Net from client
   3  SQL*Net roundtrips to/from client
  17  sorts (memory)
   0  sorts (disk)
    2410688  rows processed

.总结
在大量数据中(千万级上亿条):
 Create Table…as select…是最快的,而且生成的Undo和Redo信息最少,所以只要可能,请尽量使用这种方案。但这种方案有一定的限制,即目标表必须是不存在的,不能用它向已有的目标表中追加记录。
 Insert into … select … 是最慢的,而且生成最多的Undo和Redo信息,对I/O的压力最大,优势在于大家对它比较熟悉,使用起来比较简单,适合于处理少量的数据,若要处理大量的数据,不推荐使用这种方案。
 Copy Command可以处理Create Table不能处理的情况,即向已有的数据表中追加记录,相对于insert来说,效率更高一些,生成更少的Redo信息,不生成Undo信息,所以在执行大量的数据追加时,推荐使用Copy Command命令。
小量数据中:
   insert into比较快
此三种方法数据移植后表上对象的验证
实验前:
SQL> select INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME from user_indexes;
INDEX_NAME         INDEX_TYPE     TABLE_OWNER     TABLE_NAME
------------------------------ --------------------------- ------------------------------ ------------------------------
SYS_C0019652         NORMAL      SCOTT     THASH
I_THASH_GLOBAL_INDEX        NORMAL      SCOTT     THASH
IND_TEST_OBJECT_ID        NORMAL      SCOTT     TEST
I_ID_GLOBAL         NORMAL      SCOTT     PDBA
I_TIME_GLOBAL         NORMAL      SCOTT     PDBA
IX_HASH          NORMAL      SCOTT     PDBA
EMP$UPPER_JOB         FUNCTION-BASED NORMAL    SCOTT     EMP
PK_EMP          NORMAL      SCOTT     EMP
PK_DEPT          NORMAL      SCOTT     DEPT
9 rows selected.
试验后:
SQL> select INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME from user_indexes;
INDEX_NAME         INDEX_TYPE     TABLE_OWNER     TABLE_NAME
------------------------------ --------------------------- ------------------------------ ------------------------------
SYS_C0019652         NORMAL      SCOTT     THASH
I_THASH_GLOBAL_INDEX        NORMAL      SCOTT     THASH
IND_TEST_OBJECT_ID        NORMAL      SCOTT     TEST
I_ID_GLOBAL         NORMAL      SCOTT     PDBA
I_TIME_GLOBAL         NORMAL      SCOTT     PDBA
IX_HASH          NORMAL      SCOTT     PDBA
EMP$UPPER_JOB         FUNCTION-BASED NORMAL    SCOTT     EMP
PK_EMP          NORMAL      SCOTT     EMP
PK_DEPT          NORMAL      SCOTT     DEPT
9 rows selected.
Elapsed: 00:00:05.73
Execution Plan
----------------------------------------------------------
Plan hash value: 617980512
----------------------------------------------------------------------------------------------
| Id  | Operation      | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      | 2782 |  399K|  795   (2)| 00:00:10 |
|*  1 |  HASH JOIN RIGHT OUTER     |      | 2782 |  399K|  795   (2)| 00:00:10 |
|   2 |   INDEX FULL SCAN     | I_USER2 |  104 |  416 |    1   (0)| 00:00:01 |
|*  3 |   HASH JOIN OUTER     |      | 2782 |  388K|  793   (2)| 00:00:10 |
|*  4 |    HASH JOIN      |      | 2782 |  366K|  728   (1)| 00:00:09 |
|   5 |     TABLE ACCESS FULL     | USER$   |  104 | 1768 |    3   (0)| 00:00:01 |
|*  6 |     HASH JOIN      |      | 2782 |  320K|  724   (1)| 00:00:09 |
|*  7 |      HASH JOIN RIGHT OUTER    |      | 2782 |  230K|  517   (1)| 00:00:07 |
|   8 |       TABLE ACCESS FULL     | SEG$    | 6846 | 75306 |   56   (0)| 00:00:01 |
|*  9 |       HASH JOIN RIGHT OUTER    |      | 2782 |  201K|  460   (1)| 00:00:06 |
|  10 |        TABLE ACCESS FULL    | TS$     |   15 |   45 |    6   (0)| 00:00:01 |
|  11 |        NESTED LOOPS     |      |      |      |    |      |
|  12 |  NESTED LOOPS     |      | 2782 |  192K|  453   (1)| 00:00:06 |
|* 13 |   TABLE ACCESS FULL    | OBJ$    | 2782 |   97K|  258   (2)| 00:00:04 |
|* 14 |   INDEX UNIQUE SCAN    | I_IND1  |    1 |      |    0   (0)| 00:00:01 |
|* 15 |  TABLE ACCESS BY INDEX ROWID| IND$    |    1 |   35 |    1   (0)| 00:00:01 |
|  16 |      INDEX FAST FULL SCAN    | I_OBJ2  | 75136 | 2421K|  207   (1)| 00:00:03 |
|  17 |    INDEX FAST FULL SCAN     | I_OBJ1  | 75136 |  587K|   65   (2)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("ITO"."OWNER#"="ITU"."USER#"(+))
   3 - access("I"."INDMETHOD#"="ITO"."OBJ#"(+))
   4 - access("IO"."OWNER#"="IU"."USER#")
   6 - access("I"."BO#"="IO"."OBJ#")
   7 - access("I"."FILE#"="S"."FILE#"(+) AND "I"."BLOCK#"="S"."BLOCK#"(+) AND
       "I"."TS#"="S"."TS#"(+))
   9 - access("I"."TS#"="TS"."TS#"(+))
  13 - filter("O"."OWNER#"=USERENV('SCHEMAID') AND BITAND("O"."FLAGS",128)=0)
  14 - access("O"."OBJ#"="I"."OBJ#")
  15 - filter(BITAND("I"."FLAGS",4096)=0 AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR
       "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=8 OR
       "I"."TYPE#"=9))

Statistics
----------------------------------------------------------
   8  recursive calls
   0  db block gets
       2206  consistent gets
       2095  physical reads
   0  redo size
       1030  bytes sent via SQL*Net to client
523  bytes received via SQL*Net from client
   2  SQL*Net roundtrips to/from client
   0  sorts (memory)
   0  sorts (disk)
   9  rows processed
   
由此可以看出 copy from ,create as select ,inert into select ...,只是复制表的结构和数据,而对表上面的对象  索引
,主键。。。都没有复制,所以在数据的移植中,使用此三种方法最后还要进行对象的创建

鲜花

握手

雷人

路过

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

最新评论

引用 amingccc 2014-9-11 12:23
坚持学习,成就技术技术之王!
引用 sm_mhw@163.com 2014-11-27 08:54
感谢分享,跟三通一起成长!
引用 soso 2016-7-31 00:11
突然间爱上这里了! 感谢楼主分享的优秀资源
引用 kkltkklt 2016-8-3 10:00
要进行数据的迁移,有跨版本的还有表的复制,oracle提供了很多
引用 yangtuocun 2016-10-31 18:52
好好学习天天向上  
引用 wellhopehxr 2016-12-5 13:43

好资料,谢谢分享
引用 bikeman 2016-12-5 14:34
谢谢楼主分享!

查看全部评论(7)

返回顶部