update語句的優化方式

有二張表，表中資料如下所示：

SQL> select * from a;

ID COL1 COL2

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

1 test1 remark1

2 test2 remark2

3 test3 remark3

4 test4 remark4

SQL> select * from b;

ID COLD1 COLD2

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

1 B2 remark3

2 C1 remark2

3 B4 remark3

現要通過欄位id關聯，將表a中的col2欄位更新為B中對應的cold2值。

SQL> set autotrace on;

方式一：傳統的update語句，a，b表直接通過id關聯

SQL> edit

已寫入 file afiedt.buf

1 update a set a.col2=(

2 select b.cold2 from b

3 where a.id=b.id)

4 where exists(

5 select 1 from b

6* where a.id=b.id)

SQL> /

已更新3行。

執行計劃

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

Plan hash value: 2449172722

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

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

| 0 | UPDATE STATEMENT | | 3 | 105 | 7 (15)| 00:00:01 |

| 1 | UPDATE | A | | | | |

|* 2 | HASH JOIN SEMI | | 3 | 105 | 7 (15)| 00:00:01 |

| 3 | TABLE ACCESS FULL| A | 4 | 88 | 3 (0)| 00:00:01 |

| 4 | TABLE ACCESS FULL| B | 3 | 39 | 3 (0)| 00:00:01 |

|* 5 | TABLE ACCESS FULL | B | 1 | 22 | 3 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("A"."ID"="B"."ID")

5 - filter("B"."ID"=:B1)

Note

-----

- dynamic sampling used for this statement

統計資訊

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

10 recursive calls

5 db block gets

62 consistent gets

0 physical reads

0 redo size

909 bytes sent via SQL*Net to client

1025 bytes received via SQL*Net from client

4 SQL*Net roundtrips to/from client

3 sorts (memory)

0 sorts (disk)

3 rows processed

SQL> rollback;

回退已完成。

方式二：merge 更新方式，通過分析計劃資料可知，merge更新要快於方式一

SQL> merge into a

2 using b

3 on(a.id=b.id)

4 when matched then update set a.col2=b.cold2;

3 行已合併。

執行計劃

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

Plan hash value: 1518874097

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

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

| 0 | MERGE STATEMENT | | 3 | 54 | 7 (15)| 00:00:01 |

| 1 | MERGE | A | | | | |

| 2 | VIEW | | | | | |

|* 3 | HASH JOIN | | 3 | 222 | 7 (15)| 00:00:01 |

| 4 | TABLE ACCESS FULL| B | 3 | 93 | 3 (0)| 00:00:01 |

| 5 | TABLE ACCESS FULL| A | 4 | 172 | 3 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

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

Note

-----

- dynamic sampling used for this statement

統計資訊

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

36 recursive calls

5 db block gets

49 consistent gets

0 physical reads

0 redo size

910 bytes sent via SQL*Net to client

990 bytes received via SQL*Net from client

4 SQL*Net roundtrips to/from client

3 sorts (memory)

0 sorts (disk)

3 rows processed

方式三：inline view 更新方式

SQL> update

2 (select a.col2,b.cold2 from a,b where a.id=b.id)

3 set col2=cold2;

set col2=cold2

*

第 3 行出現錯誤:

ORA-01779: 無法修改與非鍵值儲存表對應的列

注：關聯表B的id必須是主鍵。

SQL> alter table b add constraint pk_b primary key(id);

表已更改。

SQL> edit

已寫入 file afiedt.buf

1 update

2 (select a.col2,b.cold2 from a,b where a.id=b.id)

3* set col2=cold2

SQL> /

已更新3行。

執行計劃

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

Plan hash value: 996040790

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

------

|

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

------

| 0 | UPDATE STATEMENT | | 3 | 132 | 6 (17)| 00:0

0:01 |

| 1 | UPDATE | A | | | |

|

| 2 | MERGE JOIN | | 3 | 132 | 6 (17)| 00:0

0:01 |

| 3 | TABLE ACCESS BY INDEX ROWID| B | 3 | 66 | 2 (0)| 00:0

0:01 |

| 4 | INDEX FULL SCAN | PK_B | 3 | | 1 (0)| 00:0

0:01 |

|* 5 | SORT JOIN | | 4 | 88 | 4 (25)| 00:0

0:01 |

| 6 | TABLE ACCESS FULL | A | 4 | 88 | 3 (0)| 00:0

0:01 |

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

------

Predicate Information (identified by operation id):

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

5 - access("A"."ID"="B"."ID")

filter("A"."ID"="B"."ID")

Note

-----

- dynamic sampling used for this statement

統計資訊

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

198 recursive calls

5 db block gets

57 consistent gets

0 physical reads

0 redo size

910 bytes sent via SQL*Net to client

982 bytes received via SQL*Net from client

4 SQL*Net roundtrips to/from client

10 sorts (memory)

0 sorts (disk)

3 rows processed

SQL> rollback;

回退已完成。

三、結論

標準update語法

單表更新或較簡單的語句採用使用此方案更優。

inline view更新法

兩表關聯且被更新表通過關聯表主鍵關聯的，採用此方案更優。

merge更新法

兩表關聯且被更新表不是通過關聯表主鍵關聯的，採用此方案更優。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/21251711/viewspace-1102660/，如需轉載，請註明出處，否則將追究法律責任。

update語句的優化方式

相關文章