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