update 嵌套优化
来源：互联网 发布：淘宝代购是怎么赚钱的 编辑：程序博客网 时间：2024/06/03 21:25
update ICR_BASEINFO IB set IB.a7109='4', IB.a4109=(select a4109 from icr_baseinfobak ibk where a7101=IB.a7101 and sessionid=( select max(sessionid) from icr_baseinfobak where a7101=ibk.a7101)), IB.a4107=(select a4107 from icr_baseinfobak ibk where a7101=IB.a7101 and sessionid=( select max(sessionid) from icr_baseinfobak where a7101=ibk.a7101)), IB.a4312=(select a4312 from icr_baseinfobak ibk where a7101=IB.a7101 and sessionid=( select max(sessionid) from icr_baseinfobak where a7101=ibk.a7101)), IB.a1111=(select sum(actualcreditsum) from business_wastebook where businessdesc like '61%' and ActualCreditSum > 0 and IB.a7101=relativeserialno group by relativeserialno), IB.a7107=substr(IB.a7107,1,23)||'G' where IB.a7109='3' and exists( select 1 from BUSINESS_WASTEBOOK BW where IB.a7101=BW.RelativeSerialNo and BW.businessdesc like '61%' and BW.ActualCreditSum > 0 );Plan hash value: 957362180 -------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------------------------------------------| 0 | UPDATE STATEMENT | | 1 | 76 | 4699 (1)| 00:00:57 || 1 | UPDATE | ICR_BASEINFO | | | | || 2 | NESTED LOOPS SEMI | | 1 | 76 | 4699 (1)| 00:00:57 ||* 3 | TABLE ACCESS FULL | ICR_BASEINFO | 1 | 50 | 4692 (1)| 00:00:57 ||* 4 | TABLE ACCESS BY INDEX ROWID | BUSINESS_WASTEBOOK | 2618K| 64M| 7 (0)| 00:00:01 ||* 5 | INDEX RANGE SCAN | IDX03_BUSINESS_WASTEBOOK | 6 | | 3 (0)| 00:00:01 ||* 6 | TABLE ACCESS BY INDEX ROWID | ICR_BASEINFOBAK | 1 | 29 | 3 (0)| 00:00:01 || 7 | NESTED LOOPS | | 1 | 62 | 13 (0)| 00:00:01 || 8 | VIEW | VW_SQ_1 | 1 | 33 | 10 (0)| 00:00:01 || 9 | SORT GROUP BY | | 1 | 26 | 10 (0)| 00:00:01 || 10 | TABLE ACCESS BY INDEX ROWID| ICR_BASEINFOBAK | 7 | 182 | 10 (0)| 00:00:01 ||* 11 | INDEX RANGE SCAN | IDX001_ICR_BASEINFOBAK | 7 | | 3 (0)| 00:00:01 ||* 12 | INDEX RANGE SCAN | IDX001_ICR_BASEINFOBAK | 1 | | 2 (0)| 00:00:01 ||* 13 | TABLE ACCESS BY INDEX ROWID | ICR_BASEINFOBAK | 1 | 29 | 3 (0)| 00:00:01 || 14 | NESTED LOOPS | | 1 | 62 | 13 (0)| 00:00:01 || 15 | VIEW | VW_SQ_2 | 1 | 33 | 10 (0)| 00:00:01 || 16 | SORT GROUP BY | | 1 | 26 | 10 (0)| 00:00:01 || 17 | TABLE ACCESS BY INDEX ROWID| ICR_BASEINFOBAK | 7 | 182 | 10 (0)| 00:00:01 ||* 18 | INDEX RANGE SCAN | IDX001_ICR_BASEINFOBAK | 7 | | 3 (0)| 00:00:01 ||* 19 | INDEX RANGE SCAN | IDX001_ICR_BASEINFOBAK | 1 | | 2 (0)| 00:00:01 ||* 20 | TABLE ACCESS BY INDEX ROWID | ICR_BASEINFOBAK | 1 | 29 | 3 (0)| 00:00:01 || 21 | NESTED LOOPS | | 1 | 62 | 13 (0)| 00:00:01 || 22 | VIEW | VW_SQ_3 | 1 | 33 | 10 (0)| 00:00:01 || 23 | SORT GROUP BY | | 1 | 26 | 10 (0)| 00:00:01 || 24 | TABLE ACCESS BY INDEX ROWID| ICR_BASEINFOBAK | 7 | 182 | 10 (0)| 00:00:01 ||* 25 | INDEX RANGE SCAN | IDX001_ICR_BASEINFOBAK | 7 | | 3 (0)| 00:00:01 ||* 26 | INDEX RANGE SCAN | IDX001_ICR_BASEINFOBAK | 1 | | 2 (0)| 00:00:01 || 27 | SORT GROUP BY NOSORT | | 1 | 26 | 8 (0)| 00:00:01 ||* 28 | TABLE ACCESS BY INDEX ROWID | BUSINESS_WASTEBOOK | 1 | 26 | 8 (0)| 00:00:01 ||* 29 | INDEX RANGE SCAN | IDX03_BUSINESS_WASTEBOOK | 6 | | 4 (0)| 00:00:01 |------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):--------------------------------------------------- 3 - filter("IB"."A7109"='3') 4 - filter("BW"."BUSINESSDESC" LIKE '61%' AND "BW"."ACTUALCREDITSUM">0) 5 - access("IB"."A7101"="BW"."RELATIVESERIALNO") 6 - filter("SESSIONID"="VW_COL_1") 11 - access("A7101"=:B1) 12 - access("A7101"="IBK"."A7101") filter("A7101"=:B1) 13 - filter("SESSIONID"="VW_COL_1") 18 - access("A7101"=:B1) 19 - access("A7101"="IBK"."A7101") filter("A7101"=:B1) 20 - filter("SESSIONID"="VW_COL_1") 25 - access("A7101"=:B1) 26 - access("A7101"="IBK"."A7101") filter("A7101"=:B1) 28 - filter("BUSINESSDESC" LIKE '61%' AND "ACTUALCREDITSUM">0) 29 - access("RELATIVESERIALNO"=:B1)Plan hash value: 3174198480 ------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------------------------------------| 0 | UPDATE STATEMENT | | 1 | 76 | 4699 (1)| 00:00:57 || 1 | UPDATE | ICR_BASEINFO | | | | || 2 | NESTED LOOPS SEMI | | 1 | 76 | 4699 (1)| 00:00:57 ||* 3 | TABLE ACCESS FULL | ICR_BASEINFO | 1 | 50 | 4692 (1)| 00:00:57 ||* 4 | TABLE ACCESS BY INDEX ROWID | BUSINESS_WASTEBOOK | 2618K| 64M| 7 (0)| 00:00:01 ||* 5 | INDEX RANGE SCAN | IDX03_BUSINESS_WASTEBOOK | 6 | | 3 (0)| 00:00:01 || 6 | NESTED LOOPS | | 1 | 62 | 6 (0)| 00:00:01 || 7 | VIEW | VW_SQ_1 | 1 | 33 | 3 (0)| 00:00:01 || 8 | SORT GROUP BY | | 1 | 26 | 3 (0)| 00:00:01 || 9 | FIRST ROW | | 7 | 182 | 3 (0)| 00:00:01 ||* 10 | INDEX RANGE SCAN (MIN/MAX)| IDX001_ICR_BASEINFOBAK | 7 | 182 | 3 (0)| 00:00:01 || 11 | TABLE ACCESS BY INDEX ROWID | ICR_BASEINFOBAK | 1 | 29 | 3 (0)| 00:00:01 ||* 12 | INDEX RANGE SCAN | IDX001_ICR_BASEINFOBAK | 1 | | 2 (0)| 00:00:01 || 13 | NESTED LOOPS | | 1 | 62 | 6 (0)| 00:00:01 || 14 | VIEW | VW_SQ_2 | 1 | 33 | 3 (0)| 00:00:01 || 15 | SORT GROUP BY | | 1 | 26 | 3 (0)| 00:00:01 || 16 | FIRST ROW | | 7 | 182 | 3 (0)| 00:00:01 ||* 17 | INDEX RANGE SCAN (MIN/MAX)| IDX001_ICR_BASEINFOBAK | 7 | 182 | 3 (0)| 00:00:01 || 18 | TABLE ACCESS BY INDEX ROWID | ICR_BASEINFOBAK | 1 | 29 | 3 (0)| 00:00:01 ||* 19 | INDEX RANGE SCAN | IDX001_ICR_BASEINFOBAK | 1 | | 2 (0)| 00:00:01 || 20 | NESTED LOOPS | | 1 | 62 | 6 (0)| 00:00:01 || 21 | VIEW | VW_SQ_3 | 1 | 33 | 3 (0)| 00:00:01 || 22 | SORT GROUP BY | | 1 | 26 | 3 (0)| 00:00:01 || 23 | FIRST ROW | | 7 | 182 | 3 (0)| 00:00:01 ||* 24 | INDEX RANGE SCAN (MIN/MAX)| IDX001_ICR_BASEINFOBAK | 7 | 182 | 3 (0)| 00:00:01 || 25 | TABLE ACCESS BY INDEX ROWID | ICR_BASEINFOBAK | 1 | 29 | 3 (0)| 00:00:01 ||* 26 | INDEX RANGE SCAN | IDX001_ICR_BASEINFOBAK | 1 | | 2 (0)| 00:00:01 || 27 | SORT GROUP BY NOSORT | | 1 | 26 | 8 (0)| 00:00:01 ||* 28 | TABLE ACCESS BY INDEX ROWID | BUSINESS_WASTEBOOK | 1 | 26 | 8 (0)| 00:00:01 ||* 29 | INDEX RANGE SCAN | IDX03_BUSINESS_WASTEBOOK | 6 | | 4 (0)| 00:00:01 |------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id):--------------------------------------------------- 3 - filter("IB"."A7109"='3') 4 - filter("BW"."BUSINESSDESC" LIKE '61%' AND "BW"."ACTUALCREDITSUM">0) 5 - access("IB"."A7101"="BW"."RELATIVESERIALNO") 10 - access("A7101"=:B1) 12 - access("A7101"="IBK"."A7101" AND "SESSIONID"="VW_COL_1") filter("A7101"=:B1) 17 - access("A7101"=:B1) 19 - access("A7101"="IBK"."A7101" AND "SESSIONID"="VW_COL_1") filter("A7101"=:B1) 24 - access("A7101"=:B1) 26 - access("A7101"="IBK"."A7101" AND "SESSIONID"="VW_COL_1") filter("A7101"=:B1) 28 - filter("BUSINESSDESC" LIKE '61%' AND "ACTUALCREDITSUM">0) 29 - access("RELATIVESERIALNO"=:B1) create index IDX001_ICR_BASEINFOBAK on ICR_BASEINFOBAK (A7101,Sessionid) parallel 4 tablespace USERS select A7109,count(*) from ICR_BASEINFO group by A7109 order by count(*) desc select count(*) from BUSINESS_WASTEBOOK BW where "BW"."BUSINESSDESC" LIKE '61%' AND "BW"."ACTUALCREDITSUM">0 create index BUSINESS_WASTEBOOK_IDX1 on BUSINESS_WASTEBOOK(RELATIVESERIALNO,BUSINESSDESC,ACTUALCREDITSUM) tablespace users parallel 4 Plan hash value: 1331132036 -----------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------------------------------------------------------------| 0 | UPDATE STATEMENT | | 1 | 76 | 4695 (1)| 00:00:57 || 1 | UPDATE | ICR_BASEINFO | | | | || 2 | NESTED LOOPS SEMI | | 1 | 76 | 4695 (1)| 00:00:57 ||* 3 | TABLE ACCESS FULL | ICR_BASEINFO | 1 | 50 | 4692 (1)| 00:00:57 ||* 4 | INDEX RANGE SCAN | BUSINESS_WASTEBOOK_IDX1 | 2618K| 64M| 3 (0)| 00:00:01 || 5 | NESTED LOOPS | | 1 | 62 | 6 (0)| 00:00:01 || 6 | VIEW | VW_SQ_1 | 1 | 33 | 3 (0)| 00:00:01 || 7 | SORT GROUP BY | | 1 | 26 | 3 (0)| 00:00:01 || 8 | FIRST ROW | | 7 | 182 | 3 (0)| 00:00:01 ||* 9 | INDEX RANGE SCAN (MIN/MAX)| IDX001_ICR_BASEINFOBAK | 7 | 182 | 3 (0)| 00:00:01 || 10 | TABLE ACCESS BY INDEX ROWID | ICR_BASEINFOBAK | 1 | 29 | 3 (0)| 00:00:01 ||* 11 | INDEX RANGE SCAN | IDX001_ICR_BASEINFOBAK | 1 | | 2 (0)| 00:00:01 || 12 | NESTED LOOPS | | 1 | 62 | 6 (0)| 00:00:01 || 13 | VIEW | VW_SQ_2 | 1 | 33 | 3 (0)| 00:00:01 || 14 | SORT GROUP BY | | 1 | 26 | 3 (0)| 00:00:01 || 15 | FIRST ROW | | 7 | 182 | 3 (0)| 00:00:01 ||* 16 | INDEX RANGE SCAN (MIN/MAX)| IDX001_ICR_BASEINFOBAK | 7 | 182 | 3 (0)| 00:00:01 || 17 | TABLE ACCESS BY INDEX ROWID | ICR_BASEINFOBAK | 1 | 29 | 3 (0)| 00:00:01 ||* 18 | INDEX RANGE SCAN | IDX001_ICR_BASEINFOBAK | 1 | | 2 (0)| 00:00:01 || 19 | NESTED LOOPS | | 1 | 62 | 6 (0)| 00:00:01 || 20 | VIEW | VW_SQ_3 | 1 | 33 | 3 (0)| 00:00:01 || 21 | SORT GROUP BY | | 1 | 26 | 3 (0)| 00:00:01 || 22 | FIRST ROW | | 7 | 182 | 3 (0)| 00:00:01 ||* 23 | INDEX RANGE SCAN (MIN/MAX)| IDX001_ICR_BASEINFOBAK | 7 | 182 | 3 (0)| 00:00:01 || 24 | TABLE ACCESS BY INDEX ROWID | ICR_BASEINFOBAK | 1 | 29 | 3 (0)| 00:00:01 ||* 25 | INDEX RANGE SCAN | IDX001_ICR_BASEINFOBAK | 1 | | 2 (0)| 00:00:01 || 26 | SORT GROUP BY NOSORT | | 1 | 26 | 4 (0)| 00:00:01 ||* 27 | INDEX RANGE SCAN | BUSINESS_WASTEBOOK_IDX1 | 1 | 26 | 4 (0)| 00:00:01 |----------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):--------------------------------------------------- 3 - filter("IB"."A7109"='3') 4 - access("IB"."A7101"="BW"."RELATIVESERIALNO" AND "BW"."BUSINESSDESC" LIKE '61%' AND "BW"."ACTUALCREDITSUM">0 AND "BW"."ACTUALCREDITSUM" IS NOT NULL) filter("BW"."BUSINESSDESC" LIKE '61%' AND "BW"."ACTUALCREDITSUM">0) 9 - access("A7101"=:B1) 11 - access("A7101"="IBK"."A7101" AND "SESSIONID"="VW_COL_1") filter("A7101"=:B1) 16 - access("A7101"=:B1) 18 - access("A7101"="IBK"."A7101" AND "SESSIONID"="VW_COL_1") filter("A7101"=:B1) 23 - access("A7101"=:B1) 25 - access("A7101"="IBK"."A7101" AND "SESSIONID"="VW_COL_1") filter("A7101"=:B1) 27 - access("RELATIVESERIALNO"=:B1 AND "BUSINESSDESC" LIKE '61%' AND "ACTUALCREDITSUM">0 AND "ACTUALCREDITSUM" IS NOT NULL) filter("BUSINESSDESC" LIKE '61%' AND "ACTUALCREDITSUM">0)阅读全文
0 0
- update 嵌套优化
- mysql 嵌套 update
- MySQL update 嵌套 select
- mongodb update多层嵌套数组
- update优化案例
- 优化update-虚拟表
- update的优化
- 数据库update优化
- Oracle update性能优化
- Unity Update优化
- 嵌套循环的优化。。。
- 循环嵌套优化！！
- 嵌套loop性能优化
- ABAP嵌套优化
- ListView嵌套ListView优化
- ListView嵌套Recylerview优化
- 嵌套 for循环优化
- 循环嵌套性能优化
- 设有n个正整数，将他们连接成一排，组成一个最大的多位整数。
- 二维数组斜行遍历
- bootstrap数据验证插件BootstrapValidator
- Unexpected Exception caught setting 异常解决记录
- properties文件的配置
- update 嵌套优化
- Ubuntu服务器部署静态网页
- 配置maven环境变量出现的问题
- Shell编程之流程控制
- caffe-prototxt网络在线显示
- Linux中的五个数据段
- 谷歌增强现实技术ARCore
- STM32之NVIC函数一览
- 关于U盘在装系统之后之后容量变小且不能格式化的解决方法
