rendered paste body************************************************************************************
query
merge into co_login_daily l
using (select id, trunc(created) created_date from user_info where created < to_date('26-DEC-2006','DD-MON-YYYY')) u
on (l.user_id=u.id and trunc(l.timestamp)=u.created_date)
when matched then update set l.logins=l.logins+1
when not matched then insert (l.timestamp, l.user_id, l.logins)
values(u.created_date, u.id, 1);
***********************************************************************************
Following is explain plan
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (
%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------
| 0 | MERGE STATEMENT | | | | | 10323
(100)| | | |
| 1 | MERGE | CO_LOGIN_DAILY | | | |
| | | |
| 2 | VIEW | | | | |
| | | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 3 | HASH JOIN OUTER | | 2571K| 73M| 63M| 10323
(17)| 00:01:05 | | |
|* 4 | TABLE ACCESS FULL | USER_INFO | 2571K| 34M| | 3943
(20)| 00:00:25 | | |
| 5 | PARTITION RANGE ALL| | 11M| 173M| | 1702
(26)| 00:00:11 | 1 | 246 |
| 6 | TABLE ACCESS FULL | CO_LOGIN_DAILY | 11M| 173M| | 1702
(26)| 00:00:11 | 1 | 246 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access(TRUNC(INTERNAL_FUNCTION("L"."TIMESTAMP"))=TRUNC(INTERNAL_FUNCTION(
"CREATED")) AND
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
"L"."USER_ID"="ID")
4 - filter("CREATED"<TO_DATE('2006-12-26 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))