************************************************************************************ 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"