oracle千万行update优化,Oracle update性能优化

当执行批量刷新数据时，以前我写过最好是写成merge into，当然还有一种方式，如下：

SQL> create table test1 as select * from dba_objects where rownum <100;

SQL> create table test2 as select * from dba_objects where rownum <1000;

SQL> create unique index ind_t1_object_id on test1(object_id);

SQL> create unique index ind_t2_object_id on test2(object_id);

SQL> exec dbms_stats.gather_table_stats(user,'test1');

SQL> exec dbms_stats.gather_table_stats(user,'test2');

SQL> set autotrace traceonly

SQL> update test1 t1

set t1.object_name = (select t2.object_name

from test2 t2

where t1.object_id = t2.object_id)

where exists (select 1 from test2 t3 where t3.object_id = t1.object_id);--千万不能丢掉exists，否则很多匹配不上的记录object_name就为null。

已更新99行。

执行计划

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

Plan hash value: 1549919212

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

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

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

|  0 | UPDATE STATEMENT            |                  |    99 |  1584 |  304  (33)| 00:00:04 |

|  1 |  UPDATE                      | TEST1            |      |      |            |          |

|*  2 |  HASH JOIN SEMI            |                  |    99 |  1584 |    7  (15)| 00:00:01 |

|  3 |    TABLE ACCESS FULL        | TEST1            |    99 |  1188 |    3  (0)| 00:00:01 |

|  4 |    INDEX FAST FULL SCAN      | IND_T2_OBJECT_ID |  999 |  3996 |    3  (0)| 00:00:01 |

|  5 |  TABLE ACCESS BY INDEX ROWID| TEST2            |    1 |    20 |    2  (0)| 00:00:01 |

|*  6 |    INDEX RANGE SCAN          | IND_T2_OBJECT_ID |    1 |      |    1  (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("T3"."OBJECT_ID"="T1"."OBJECT_ID")

6 - access("T2"."OBJECT_ID"=:B1)

统计信息

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

3  recursive calls

103  db block gets

217  consistent gets

0  physical reads

23656  redo size

559  bytes sent via SQL*Net to client

922  bytes received via SQL*Net from client

3  SQL*Net roundtrips to/from client

2  sorts (memory)

0  sorts (disk)

99  rows processed

SQL> commit;

SQL> update (select t1.object_name, t2.object_name new_object_name

from test1 t1, test2 t2

where t1.object_id = t2.object_id)

set object_name = new_object_name;

已更新99行。

执行计划

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

Plan hash value: 1124869545

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

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

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

|  0 | UPDATE STATEMENT    |      |    99 |  3168 |    10  (10)| 00:00:01 |

|  1 |  UPDATE            | TEST1 |      |      |            |          |

|*  2 |  HASH JOIN        |      |    99 |  3168 |    10  (10)| 00:00:01 |

|  3 |    TABLE ACCESS FULL| TEST1 |    99 |  1188 |    3  (0)| 00:00:01 |

|  4 |    TABLE ACCESS FULL| TEST2 |  999 | 19980 |    6  (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

统计信息

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

3  recursive calls

103  db block gets

25  consistent gets

0  physical reads

23736  redo size

561  bytes sent via SQL*Net to client

858  bytes received via SQL*Net from client

3  SQL*Net roundtrips to/from client

2  sorts (memory)

0  sorts (disk)

99  rows processed

SQL> commit;

总结：可以看到，第二种写法比第一种写法少扫描一张表，逻辑读也少了很多。