update优化案例

发布时间：2024-11-28 04:14

参考他人成功案例，学习优化技巧 #生活技巧# #工作学习技巧# #简历制作建议#

UPDATE mid_os_userinfo_day_tmp b

SET (twoorderdate, twoservicetype, twoordertype, twoorderway) =

(SELECT a.createtime,

a.serviceitem,

opertype,

CASE

WHEN a.bindtypeid = 11404 THEN

WHEN a.bindtypeid = 115 OR a.bindtypeid = 116 OR

a.bindtypeid = 130 OR a.bindtypeid = 131 THEN

WHEN a.bindtypeid = 3505 OR a.bindtypeid = 4645 OR

a.bindtypeid = 3504 THEN

WHEN a.bindtypeid = 4944 OR a.bindtypeid = 4945 OR

a.bindtypeid = 11804 THEN

WHEN a.bindtypeid = 12044 OR a.bindtypeid = 13004 OR

a.bindtypeid = 13244 OR a.bindtypeid = 13245 THEN

ELSE

END AS bindtypeid2

FROM mid_os_userinfo_day_tmp02 a

WHERE a.usernumber = b.mobileno)

WHERE EXISTS (SELECT 1

FROM mid_os_userinfo_day_tmp02 a

WHERE a.usernumber = b.mobileno);

这个sql 跑了一晚上还没跑出来
mid_os_userinfo_day_tmp 200w
mid_os_userinfo_day_tmp02 70w

执行计划为：

Plan hash value: 4034942297

| 0 | UPDATE STATEMENT | | | | 172 (100)| | | |

| 2 | NESTED LOOPS | | 21 | 1743 | 4 (25)| 00:00:01 | | |

| 3 | SORT UNIQUE | | 82 | 1476 | 2 (0)| 00:00:01 | | |

| 4 | TABLE ACCESS FULL | MID_OS_USERINFO_DAY_TMP02 | 82 | 1476 | 2 (0)| 00:00:01 | | |

| 5 | PARTITION HASH ITERATOR | | 8 | 520 | 0 (0)| | KEY | KEY |

|* 6 | TABLE ACCESS FULL | MID_OS_USERINFO_DAY_TMP | 8 | 520 | 0 (0)| | KEY | KEY |

| 7 | TABLE ACCESS BY INDEX ROWID| MID_OS_USERINFO_DAY_TMP02 | 1 | 65 | 1 (0)| 00:00:01 | | |

|* 8 | INDEX RANGE SCAN | MID_OS_USERINFO_DAY_TMP02_IDX | 1 | | 1 (0)| 00:00:01 | | |

Query Block Name / Object Alias (identified by operation id):

1 - SEL$10439C08

4 - SEL$10439C08 / A@SEL$2

6 - SEL$10439C08 / B@UPD$1

7 - SEL$1 / A@SEL$1

8 - SEL$1 / A@SEL$1

Predicate Information (identified by operation id):

6 - filter("A"."USERNUMBER"="B"."MOBILENO")

8 - access("A"."USERNUMBER"=:B1)

Column Projection Information (identified by operation id):

2 - (upd=4,5,6,7; cmp=3) "A"."USERNUMBER"[VARCHAR2,32], "SYS_ALIAS_1".ROWID[ROWID,10],

"B"."MOBILENO"[VARCHAR2,32], "TWOORDERDATE"[DATE,7], "TWOORDERTYPE"[NUMBER,22], "TWOSERVICETYPE"[VARCHAR2,20],

"TWOORDERWAY"[NUMBER,22]

3 - (#keys=1) "A"."USERNUMBER"[VARCHAR2,32]

4 - "A"."USERNUMBER"[VARCHAR2,32]

5 - "SYS_ALIAS_1".ROWID[ROWID,10], "B"."MOBILENO"[VARCHAR2,32], "TWOORDERDATE"[DATE,7], "TWOORDERTYPE"[NUMBER,22],

"TWOSERVICETYPE"[VARCHAR2,20], "TWOORDERWAY"[NUMBER,22]

6 - "SYS_ALIAS_1".ROWID[ROWID,10], "B"."MOBILENO"[VARCHAR2,32], "TWOORDERDATE"[DATE,7], "TWOORDERTYPE"[NUMBER,22],

"TWOSERVICETYPE"[VARCHAR2,20], "TWOORDERWAY"[NUMBER,22]

7 - "A".ROWID[ROWID,10], "A"."USERNUMBER"[VARCHAR2,32], "A"."CREATETIME"[DATE,7], "OPERTYPE"[NUMBER,22],

"A"."BINDTYPEID"[NUMBER,22], "A"."SERVICEITEM"[VARCHAR2,20]

8 - "A".ROWID[ROWID,10], "A"."USERNUMBER"[VARCHAR2,32]

这个sql 以前只要跑1分钟，昨天跑了一晚上还跑出来（我只是把mid_os_userinfo_day_tmp改为分区表了）具体为什么出现这个原因，值得研究

这里还是先解决问题：

分析：
1，从sql来看，需要把mid_os_userinfo_day_tmp02这个表的数据更新到mid_os_userinfo_day_tmp这个表中
但是这里mid_os_userinfo_day_tmp02这个表访问两次（可以改sql）

2，执行计划这里是为nl 被驱动表竟然没有索引，不慢才怪，当然这是第一点
第二点更新的数据有60w,为hash比较好

解决方案：把sql 改为merge

MERGE INTO tmp1 b USING

(SELECT a.createtime,

a.serviceitem,

opertype,

a.usernumber,

CASE

WHEN a.bindtypeid = 11404 THEN

WHEN a.bindtypeid = 115 OR a.bindtypeid = 116 OR

a.bindtypeid = 130 OR a.bindtypeid = 131 THEN

WHEN a.bindtypeid = 3505 OR a.bindtypeid = 4645 OR

a.bindtypeid = 3504 THEN

WHEN a.bindtypeid = 4944 OR a.bindtypeid = 4945 OR

a.bindtypeid = 11804 THEN

WHEN a.bindtypeid = 12044 OR a.bindtypeid = 13004 OR

a.bindtypeid = 13244 OR a.bindtypeid = 13245 THEN

ELSE

END AS bindtypeid2

FROM tmp a )x

ON (x.usernumber = b.mobileno)

WHEN MATCHED THEN

UPDATE SET twoorderdate=x.createtime,twoservicetype=x.serviceitem,twoordertype=x.opertype,twoorderway=x.bindtypeid2

Plan hash value: 3384886965

| 0 | MERGE STATEMENT | | | | | 23912 (100)| |

| 1 | MERGE | TMP1 | | | | | |

| 2 | VIEW | | | | | | |

|* 3 | HASH JOIN | | 1764K| 442M| 25M| 23912 (1)| 00:04:47 |

| 4 | TABLE ACCESS FULL| TMP | 345K| 21M| | 828 (2)| 00:00:10 |

| 5 | TABLE ACCESS FULL| TMP1 | 1764K| 333M| | 4254 (3)| 00:00:52 |

Query Block Name / Object Alias (identified by operation id):

1 - MRG$1

3 - SEL$F5BB74E1

4 - SEL$F5BB74E1 / A@SEL$2

5 - SEL$F5BB74E1 / B@SEL$1

Predicate Information (identified by operation id):

3 - access("A"."USERNUMBER"="B"."MOBILENO")

Column Projection Information (identified by operation id):

1 - SYSDEF[4], SYSDEF[32720], SYSDEF[1], SYSDEF[76], SYSDEF[32720]

2 - "X"."CREATETIME"[DATE,7], "X"."SERVICEITEM"[VARCHAR2,20],

"X"."OPERTYPE"[NUMBER,22], "X"."BINDTYPEID2"[NUMBER,2]

3 - (#keys=1) "A"."USERNUMBER"[VARCHAR2,32], "B"."MOBILENO"[VARCHAR2,32],

"A"."SERVICEITEM"[VARCHAR2,20], "A"."CREATETIME"[DATE,7],

"OPERTYPE"[NUMBER,22], "A"."BINDTYPEID"[NUMBER,22], "B".ROWID[ROWID,10],

"B"."PUSHMAIL_SUBSCRIBER"[NUMBER,22], "B"."STATEDATE"[NUMBER,22],

"B"."FIRSTORDERDATE"[DATE,7], "B"."SERVICEID"[NUMBER,22],

"B"."FIRSTORDERTYPE"[NUMBER,22], "B"."FIRSTSERVICETYPE"[VARCHAR2,20],

"B"."FIRSTORDERWAY"[NUMBER,22], "B"."TWOORDERDATE"[DATE,7],

"B"."TWOORDERTYPE"[NUMBER,22], "B"."TWOSERVICETYPE"[VARCHAR2,20],

"B"."TWOORDERWAY"[NUMBER,22], "B"."STATUS"[NUMBER,22],

"B"."CREATETIME"[DATE,7], "B"."PROVCODE"[NUMBER,22]

4 - "A"."USERNUMBER"[VARCHAR2,32], "A"."CREATETIME"[DATE,7],

"OPERTYPE"[NUMBER,22], "A"."BINDTYPEID"[NUMBER,22],

"A"."SERVICEITEM"[VARCHAR2,20]

5 - "B".ROWID[ROWID,10], "B"."MOBILENO"[VARCHAR2,32],

"B"."STATEDATE"[NUMBER,22], "B"."FIRSTORDERDATE"[DATE,7],

"B"."SERVICEID"[NUMBER,22], "B"."FIRSTORDERTYPE"[NUMBER,22],

"B"."FIRSTSERVICETYPE"[VARCHAR2,20], "B"."FIRSTORDERWAY"[NUMBER,22],

"B"."TWOORDERDATE"[DATE,7], "B"."TWOORDERTYPE"[NUMBER,22],

"B"."TWOSERVICETYPE"[VARCHAR2,20], "B"."TWOORDERWAY"[NUMBER,22],

"B"."STATUS"[NUMBER,22], "B"."CREATETIME"[DATE,7],

"B"."PROVCODE"[NUMBER,22], "B"."PUSHMAIL_SUBSCRIBER"[NUMBER,22]

这里执行计划位hash了，一分钟左右就可以了又回到原来的时间了。

遗留问题：

可以研究下为什么mid_os_userinfo_day_tmp改为分区了，后执行计划变成那样了
可以用10053来看CBO选择的成本计算

网址：update优化案例 https://www.yuejiaxmz.com/news/view/295980

上一篇：数字化工作区可以改善员工体验？

下一篇：强化保育工作细则优化幼儿一日生

update优化案例

相关内容

随便看看

最新动态分享

热点动态分享

专题

推荐动态分享