一个有意思的sql
程序员文章站
2022-03-01 17:28:32
...
SQL_ID 117xkgymmyby3, child number 0
-------------------------------------
MERGE INTO tb_finance_user_fund_statics T1 USING
tb_finance_user_fund_statics T2 ON ( T1.account_id=T2.account_id
and T1.invest_day = T2.invest_day + 1 and
T1.invest_day=trunc(sysdate, 'dd') ) WHEN MATCHED THEN UPDATE SET
T1.today_return_amount = T1.total_return_amount -
T2.total_return_amount, T1.update_time=sysdate
Plan hash value: 580082660
----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | | | | 72055 (100)| | | |
| 1 | MERGE | TB_FINANCE_USER_FUND_STATICS | | | | | | | |
| 2 | VIEW | | | | | | | | |
|* 3 | HASH JOIN | | 17697 | 2661K| 3368K| 72055 (2)| 00:14:25 | | |
| 4 | PARTITION RANGE SINGLE | | 38253 | 2913K| | 520 (1)| 00:00:07 | KEY | KEY |
| 5 | TABLE ACCESS BY LOCAL INDEX ROWID| TB_FINANCE_USER_FUND_STATICS | 38253 | 2913K| | 520 (1)| 00:00:07 | KEY | KEY |
|* 6 | INDEX RANGE SCAN | IDX_INVESTDAY | 1055 | | | 114 (0)| 00:00:02 | KEY | KEY |
| 7 | PARTITION RANGE ALL | | 252K| 18M| | 70320 (2)| 00:14:04 | 1 |1048575|
|* 8 | TABLE ACCESS FULL | TB_FINANCE_USER_FUND_STATICS | 252K| 18M| | 70320 (2)| 00:14:04 | 1 |1048575|
----------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."ACCOUNT_ID"="T2"."ACCOUNT_ID" AND "T1"."INVEST_DAY"=INTERNAL_FUNCTION("T2"."INVEST_DAY")+1)
6 - access("T1"."INVEST_DAY"=TRUNC([email protected]!,'fmdd'))
8 - filter(INTERNAL_FUNCTION("T2"."INVEST_DAY")+1=TRUNC([email protected]!,'fmdd'))
看到第8步的关联，oracle进行了谓词传递，把t2.invest_day给传递了，这个应该在条件上添加个冗余的过滤条件，t2.invest_day=trunc(sysdate,dd’)-1应该会好