Oracle update性能优化

落日映苍穹つ 2022-07-13 14:16 200阅读 0赞

当执行批量刷新数据时，以前我写过最好是写成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;

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