update语句的优化方式

最新推荐文章于 2023-07-22 17:18:00 发布

crbzijz188696507

最新推荐文章于 2023-07-22 17:18:00 发布

阅读量814

点赞数

有二张表，表中数据如下所示：

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21251711/viewspace-1102660/

crbzijz188696507

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
update语句的优化方式

有二张表，表中数据如下所示： SQL> select * from a; ID COL1 COL2...
复制链接

扫一扫