vicky10001
Warnings : 1
Active User
.jpg)
Joined: 13 Jul 2005
Posts: 136
|
|
|
|
We are going to tune this below query
MERGE INTO S_USERS s
USING
(
SELECT COL_GEID,N_EMP,COL_SSN,COL_SOEID,COL_RITS,COL_EMP_STATUS,
D_HIRED,COL_EXPENSE_CODE,COL_COUNTRY,COL_WORK_PHONE,T_EMAIL,
T_MGR_LAYER,COL_MGR_GEID,COL_MGMT_CODE,D_TERM,
COUNT(S_uid) cnt_users,COUNT(temp_S_uid) cnt_tmp_use
FROM
( SELECT temp_S_users.*, 1 temp_S_uid,
to_number(null) S_uid
FROM temp_S_users
UNION ALL
SELECT S_users.*, to_number(null) temp_S_uid,
2 S_uid
FROM S_users
)
GROUP BY COL_GEID,N_EMP,COL_SSN,COL_SOEID,COL_RITS,COL_EMP_STATUS,
D_HIRED,COL_EXPENSE_CODE,COL_COUNTRY,COL_WORK_PHONE,T_EMAIL,
T_MGR_LAYER,COL_MGR_GEID,COL_MGMT_CODE,D_TERM
HAVING COUNT(S_UID) <> COUNT(TEMP_S_UID) AND
COUNT(temp_S_uid) = 1
) t
ON(s.COL_geid = t.COL_geid)
WHEN MATCHED THEN UPDATE SET
s.N_EMP = t.N_EMP,
s.COL_SSN = t.COL_SSN,
s.COL_SOEID = t.COL_SOEID,
s.COL_RITS = t.COL_RITS,
s.COL_EMP_STATUS = t.COL_EMP_STATUS,
s.D_HIRED = t.D_HIRED,
s.COL_EXPENSE_CODE = t.COL_EXPENSE_CODE,
s.COL_COUNTRY = t.COL_COUNTRY,
s.COL_WORK_PHONE = t.COL_WORK_PHONE,
s.T_EMAIL = t.T_EMAIL,
s.T_MGR_LAYER = t.T_MGR_LAYER,
s.COL_MGR_GEID = t.COL_MGR_GEID,
s.COL_MGMT_CODE = t.COL_MGMT_CODE,
s.D_TERM = t.D_TERM
WHEN NOT MATCHED THEN INSERT
(
COL_GEID,N_EMP,COL_SSN,COL_SOEID,COL_RITS,COL_EMP_STATUS,
D_HIRED,COL_EXPENSE_CODE,COL_COUNTRY,COL_WORK_PHONE,T_EMAIL,
T_MGR_LAYER,COL_MGR_GEID,COL_MGMT_CODE,D_TERM
)
VALUES
(
t.COL_GEID,t.N_EMP,t.COL_SSN,t.COL_SOEID,t.COL_RITS,
t.COL_EMP_STATUS,t.D_HIRED,t.COL_EXPENSE_CODE,
t.COL_COUNTRY,t.COL_WORK_PHONE,t.T_EMAIL,t.T_MGR_LAYER,
t.COL_MGR_GEID,t.COL_MGMT_CODE,t.D_TERM
);
Any idea for this.... Thanks in advance... |
|
