查找一个表的一列插入到另一个表

create table t as select * from all_objects;

create table t1 as select * from all_objects;

alter table t add id number;

现在要往t中的id列用object_id填充

不使用存贮过程：

1.使用update

update t a set a.id=(select object_id from t1 b where a.object_id=b.object_id);

已用时间: 00: 00: 00.26
SQL> update t a set a.id=(select object_id from t2 b where a.object_id=b.object_id);

已更新53842行。

已用时间: 00: 11: 40.23

执行计划
----------------------------------------------------------
Plan hash value: 848927671

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 55929 | 1420K| 228 (1)| 00:00:03 |
| 1 | UPDATE | T | | | | |
| 2 | TABLE ACCESS FULL| T | 55929 | 1420K| 228 (1)| 00:00:03 |
|* 3 | TABLE ACCESS FULL| T2 | 621 | 8073 | 228 (1)| 00:00:03 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("B"."OBJECT_ID"=:B1)

Note
-----
- dynamic sampling used for this statement

统计信息
----------------------------------------------------------
316 recursive calls
163844 db block gets
54634781 consistent gets
0 physical reads
28650136 redo size
687 bytes sent via SQL*Net to client
647 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
53842 rows processed

2.s使用Merge into

1 merge into t a using t2 b
2 on (a.object_id=b.object_id)
3 when matched then
4* update set a.id=b.object_id
SQL> /

53842 行已合并。

已用时间: 00: 00: 02.68

执行计划
----------------------------------------------------------
Plan hash value: 68457001

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

| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time
|

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

| 0 | MERGE STATEMENT | | 55929 | 1420K| | 1474 (1)| 00:00
:18 |

| 1 | MERGE | T | | | | |
|

| 2 | VIEW | | | | | |
|

|* 3 | HASH JOIN | | 55929 | 18M| 10M| 1474 (1)| 00:00
:18 |

| 4 | TABLE ACCESS FULL| T2 | 62109 | 9583K| | 228 (1)| 00:00
:03 |

| 5 | TABLE ACCESS FULL| T | 55929 | 9995K| | 228 (1)| 00

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("A"."OBJECT_ID"="B"."OBJECT_ID")

Note
-----
- dynamic sampling used for this statement

统计信息
----------------------------------------------------------
287 recursive calls
55737 db block gets
1999 consistent gets
0 physical reads
12575944 redo size
686 bytes sent via SQL*Net to client
669 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
9 sorts (memory)
0 sorts (disk)
53842 rows processed

这种情况下,merge into的效率比update 的效率高多了

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22418990/viewspace-1054728/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22418990/viewspace-1054728/