调整方法二：建立临时表＋Merge
（２）
-- 回到本例，设置pa.col2为非空
-- 验证merge没有往pa插入数据
SQL> alter table pa modify col2 not null
2 /
表已更改。
已用时间: 00: 00: 01.08
SQL> drop table temp_table
2 /
表已丢弃。
已用时间: 00: 00: 00.00
SQL> create global temporary table temp_table
2 (id char(10),new_col1 number(10,2))
3 /
表已创建。
已用时间: 00: 00: 00.00
SQL> insert /*+parallel(4)*/ into temp_table
2 select /*+use_hash(pa p)*/
3 pa.id,pa.col1*c.col1
4 from pa,p,c
5 where p.id=pa.id and p.cid=c.cid
6 and pa.id not in (select /*+hash_aj*/ id
7 from d)
8 /
已创建200000行。
已用时间: 00: 00: 09.06
Execution Plan
----------------------------------------------------------
0 INSERT STATEMENT Optimizer=CHOOSE (Cost=1973 Card=1 Bytes=85)
1 0 NESTED LOOPS (Cost=1973 Card=1 Bytes=85)
2 1 HASH JOIN (Cost=1971 Card=1 Bytes=60)
3 2 HASH JOIN (ANTI) (Cost=1543 Card=1 Bytes=36)
4 3 TABLE ACCESS (FULL) OF 'PA' (Cost=347 Card=1000000 Bytes=24000000)
5 3 INDEX (FAST FULL SCAN) OF 'PK_D' (UNIQUE) (Cost=284
Card=1000000 Bytes=12000000)
6 2 TABLE ACCESS (FULL) OF 'P' (Cost=425 Card=1000000 Bytes=24000000)
7 1 TABLE ACCESS (BY INDEX ROWID) OF 'C' (Cost=2 Card=1 Bytes=25)
8 7 INDEX (UNIQUE SCAN) OF 'PK_C' (UNIQUE) (Cost=1 Card=1)
Statistics
----------------------------------------------------------
44 recursive calls
3348 db block gets
680381 consistent gets
0 physical reads
570536 redo size
482 bytes sent via SQL*Net to client
494 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
200000 rows processed
SQL> merge into pa
2 using temp_table
3 on (pa.id = temp_table.id)
4 when matched then
5 update set pa.col1 = temp_table.new_col1
6 when not matched then
7 insert /*+append*/ (pa.id) values(temp_table.id)
8 /
200000 行已合并。
已用时间: 00: 00: 07.06
Execution Plan
----------------------------------------------------------
0 MERGE STATEMENT Optimizer=CHOOSE (Cost=1004 Card=1000002 Bytes=49000098)
1 0 MERGE OF 'PA'
2 1 VIEW
3 2 HASH JOIN (OUTER) (Cost=1004 Card=1000002 Bytes=49000098)
4 3 TABLE ACCESS (FULL) OF 'TEMP_TABLE' (Cost=11 Card=175005 Bytes=4375125)
5 3 TABLE ACCESS (FULL) OF 'PA' (Cost=347 Card=1000000 Bytes=24000000)
Statistics
----------------------------------------------------------
28 recursive calls
204650 db block gets
4190 consistent gets
0 physical reads
48986184 redo size
481 bytes sent via SQL*Net to client
500 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
200000 rows processed
SQL>
我们看到，插入临时表中花费的时间为00: 00: 09.06、merge语句执行的时间为00: 00: 07.06，总共的时间大致为17秒左右，调整的效果不错。来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/6906/viewspace-21519/ ，如需转载，请注明出处，否则将追究法律责任。
