在日常工作中有时经常要进行数据的迁移,有跨版本的还有表的复制,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 ...,只是复制表的结构和数据,而对表上面的对象 索引 ,主键。。。都没有复制,所以在数据的移植中,使用此三种方法最后还要进行对象的创建 |