Home » SQL & PL/SQL » SQL & PL/SQL » sql performance (merged) (19c)
| sql performance (merged) [message #690268] |
Tue, 11 February 2025 18:02  |
ora9a
Messages: 43
Registered: June 2010
|
Member |
|
|
I have this update which takes a long time to run. Is there any improvements that can be made, particularly with all the AND/ORs. The staging table has about 30k records.
UPDATE
STAGING STG
SET
( STG.TITLE,
STG.FORENAME,
STG.MIDDLE_NAME,
STG.SURNAME,
STG.GENDER,
STG.DATE_OF_BIRTH,
STG.ADDRESS_LINE_1,
STG.ADDRESS_LINE_2,
STG.ADDRESS_LINE_3,
STG.ADDRESS_CITY,
STG.ADDRESS_COUNTY_CODE,
STG.ADDRESS_POST_CODE,
STG.ADDRESS_NATION_CODE,
STG.TT_ADDRESS_LINE_1,
STG.TT_ADDRESS_LINE_2,
STG.TT_ADDRESS_LINE_3,
STG.TT_ADDRESS_CITY,
STG.TT_ADDRESS_COUNTY_CODE,
STG.TT_ADDRESS_POST_CODE,
STG.TT_ADDRESS_NATION_CODE,
STG.TELEPHONE,
STG.MOBILE_TELEPHONE,
STG.EMAIL_ADDRESS,
STG.DOMICILE,
STG.NATIONALITY,
STG.ENTRY_TERM,
STG.RESIDENCY_CODE,
STG.COUNTRY_OF_BIRTH,
STG.COURSE_KEY,
STG.COURSE_CODE,
STG.YEAR_OF_ENTRY,
STG.MONTH_OF_ENTRY,
STG.STATUS,
STG.APP_SOURCE,
STG.SSDT_CODE_ENTRY,
STG.DATE_EXTRACT
) = (SELECT TITLE,
FORENAME,
MIDDLE_NAME,
SURNAME,
GENDER,
DATE_OF_BIRTH,
ADDRESS_LINE_1,
ADDRESS_LINE_2,
ADDRESS_LINE_3,
ADDRESS_CITY,
ADDRESS_COUNTY_CODE,
ADDRESS_POST_CODE,
ADDRESS_NATION_CODE,
TT_ADDRESS_LINE_1,
TT_ADDRESS_LINE_2,
TT_ADDRESS_LINE_3,
TT_ADDRESS_CITY,
TT_ADDRESS_COUNTY_CODE,
TT_ADDRESS_POST_CODE,
TT_ADDRESS_NATION_CODE,
TELEPHONE,
MOBILE_TELEPHONE,
EMAIL_ADDRESS,
DOMICILE,
NATIONALITY,
ENTRY_TERM,
RESIDENCY_CODE,
COUNTRY_OF_BIRTH,
COURSE_KEY,
COURSE_CODE,
YEAR_OF_ENTRY,
MONTH_OF_ENTRY,
3,
APP_SOURCE,
SSDT_CODE_ENTRY,
SYSDATE
FROM
TEMPSTAGING
WHERE
(STG.APPLICANT_NO = APPLICANT_NO AND
STG.CHOICE_TYPE = CHOICE_TYPE AND
STG.CHOICE_TYPE_NO = CHOICE_TYPE_NO AND
STG.pidm = pidm)
AND
(
(
STG.TITLE <> TITLE
OR
(STG.TITLE is null and TITLE is not Null)
OR
(STG.TITLE is not null and TITLE is Null)
)
OR
(
STG.FORENAME <> FORENAME
OR
(STG.FORENAME is null and FORENAME is not Null)
OR
(STG.FORENAME is not null and FORENAME is Null)
)
OR
(
STG.MIDDLE_NAME <> MIDDLE_NAME
OR
(STG.MIDDLE_NAME is null and MIDDLE_NAME is not Null)
OR
(STG.MIDDLE_NAME is not null and MIDDLE_NAME is Null)
)
OR
(
STG.SURNAME <> SURNAME
OR
(STG.SURNAME is null and SURNAME is not Null)
OR
(STG.SURNAME is not null and SURNAME is Null)
)
OR
(
STG.GENDER <> GENDER
OR
(STG.GENDER is null and GENDER is not Null)
OR
(STG.GENDER is not null and GENDER is Null)
)
OR
(
STG.DATE_OF_BIRTH <> DATE_OF_BIRTH
OR
(STG.DATE_OF_BIRTH is null and DATE_OF_BIRTH is not Null)
OR
(STG.DATE_OF_BIRTH is not null and DATE_OF_BIRTH is Null)
)
OR
(
STG.ADDRESS_LINE_1 <> ADDRESS_LINE_1
OR
(STG.ADDRESS_LINE_1 is null and ADDRESS_LINE_1 is not Null)
OR
(STG.ADDRESS_LINE_1 is not null and ADDRESS_LINE_1 is Null)
)
OR
(
STG.ADDRESS_LINE_2 <> ADDRESS_LINE_2
OR
(STG.ADDRESS_LINE_2 is null and ADDRESS_LINE_2 is not Null)
OR
(STG.ADDRESS_LINE_2 is not null and ADDRESS_LINE_2 is Null)
)
OR
(
STG.ADDRESS_LINE_3 <> ADDRESS_LINE_3
OR
(STG.ADDRESS_LINE_3 is null and ADDRESS_LINE_3 is not Null)
OR
(STG.ADDRESS_LINE_3 is not null and ADDRESS_LINE_3 is Null)
)
OR
(
STG.ADDRESS_CITY <> ADDRESS_CITY
OR
(STG.ADDRESS_CITY is null and ADDRESS_CITY is not Null)
OR
(STG.ADDRESS_CITY is not null and ADDRESS_CITY is Null)
)
OR
(
STG.ADDRESS_COUNTY_CODE <> ADDRESS_COUNTY_CODE
OR
(STG.ADDRESS_COUNTY_CODE is null and ADDRESS_COUNTY_CODE is not Null)
OR
(STG.ADDRESS_COUNTY_CODE is not null and ADDRESS_COUNTY_CODE is Null)
)
OR
(
STG.ADDRESS_POST_CODE <> ADDRESS_POST_CODE
OR
(STG.ADDRESS_POST_CODE is null and ADDRESS_POST_CODE is not Null)
OR
(STG.ADDRESS_POST_CODE is not null and ADDRESS_POST_CODE is Null)
)
OR
(
STG.ADDRESS_NATION_CODE <> ADDRESS_NATION_CODE
OR
(STG.ADDRESS_NATION_CODE is null and ADDRESS_NATION_CODE is not Null)
OR
(STG.ADDRESS_NATION_CODE is not null and ADDRESS_NATION_CODE is Null)
)
OR
(
STG.TELEPHONE <> TELEPHONE
OR
(STG.TELEPHONE is null and TELEPHONE is not Null)
OR
(STG.TELEPHONE is not null and TELEPHONE is Null)
)
OR
(
STG.MOBILE_TELEPHONE <> MOBILE_TELEPHONE
OR
(STG.MOBILE_TELEPHONE is null and MOBILE_TELEPHONE is not Null)
OR
(STG.MOBILE_TELEPHONE is not null and MOBILE_TELEPHONE is Null)
)
OR
(
STG.EMAIL_ADDRESS <> EMAIL_ADDRESS
OR
(STG.EMAIL_ADDRESS is null and EMAIL_ADDRESS is not Null)
OR
(STG.EMAIL_ADDRESS is not null and EMAIL_ADDRESS is Null)
)
OR
(
STG.DOMICILE <> DOMICILE
OR
(STG.DOMICILE is null and DOMICILE is not Null)
OR
(STG.DOMICILE is not null and DOMICILE is Null)
)
OR
(
STG.NATIONALITY <> NATIONALITY
OR
(STG.NATIONALITY is null and NATIONALITY is not Null)
OR
(STG.NATIONALITY is not null and NATIONALITY is Null)
)
OR
(
STG.COUNTRY_OF_BIRTH <> COUNTRY_OF_BIRTH
OR
(STG.COUNTRY_OF_BIRTH is null and COUNTRY_OF_BIRTH is not Null)
OR
(STG.COUNTRY_OF_BIRTH is not null and COUNTRY_OF_BIRTH is Null)
)
)
AND
(
(
STG.TT_ADDRESS_LINE_1 <> TT_ADDRESS_LINE_1
OR
(STG.TT_ADDRESS_LINE_1 is null and TT_ADDRESS_LINE_1 is not Null)
OR
(STG.TT_ADDRESS_LINE_1 is not null and TT_ADDRESS_LINE_1 is Null)
)
OR
(
STG.TT_ADDRESS_LINE_2 <> TT_ADDRESS_LINE_2
OR
(STG.TT_ADDRESS_LINE_2 is null and TT_ADDRESS_LINE_2 is not Null)
OR
(STG.TT_ADDRESS_LINE_2 is not null and TT_ADDRESS_LINE_2 is Null)
)
OR
(
STG.TT_ADDRESS_LINE_3 <> TT_ADDRESS_LINE_3
OR
(STG.TT_ADDRESS_LINE_3 is null and TT_ADDRESS_LINE_3 is not Null)
OR
(STG.TT_ADDRESS_LINE_3 is not null and TT_ADDRESS_LINE_3 is Null)
)
OR
(
STG.TT_ADDRESS_CITY <> TT_ADDRESS_CITY
OR
(STG.TT_ADDRESS_CITY is null and TT_ADDRESS_CITY is not Null)
OR
(STG.TT_ADDRESS_CITY is not null and TT_ADDRESS_CITY is Null)
)
OR
(
STG.TT_ADDRESS_COUNTY_CODE <> TT_ADDRESS_COUNTY_CODE
OR
(STG.TT_ADDRESS_COUNTY_CODE is null and TT_ADDRESS_COUNTY_CODE is not Null)
OR
(STG.TT_ADDRESS_COUNTY_CODE is not null and TT_ADDRESS_COUNTY_CODE is Null)
)
OR
(
STG.TT_ADDRESS_POST_CODE <> TT_ADDRESS_POST_CODE
OR
(STG.TT_ADDRESS_POST_CODE is null and TT_ADDRESS_POST_CODE is not Null)
OR
(STG.TT_ADDRESS_POST_CODE is not null and TT_ADDRESS_POST_CODE is Null)
)
OR
(
STG.TT_ADDRESS_NATION_CODE <> TT_ADDRESS_NATION_CODE
OR
(STG.TT_ADDRESS_NATION_CODE is null and TT_ADDRESS_NATION_CODE is not Null)
OR
(STG.TT_ADDRESS_NATION_CODE is not null and TT_ADDRESS_NATION_CODE is Null)
)
)
AND
(
(
STG.ENTRY_TERM <> ENTRY_TERM
OR
(STG.ENTRY_TERM is null and ENTRY_TERM is not Null)
OR
(STG.ENTRY_TERM is not null and ENTRY_TERM is Null)
)
OR
(
STG.RESIDENCY_CODE <> RESIDENCY_CODE
OR
(STG.RESIDENCY_CODE is null and RESIDENCY_CODE is not Null)
OR
(STG.RESIDENCY_CODE is not null and RESIDENCY_CODE is Null)
)
OR
(
STG.COURSE_CODE <> COURSE_CODE
OR
(STG.COURSE_CODE is null and COURSE_CODE is not Null)
OR
(STG.COURSE_CODE is not null and COURSE_CODE is Null)
)
OR
(
STG.COURSE_KEY <> COURSE_KEY
OR
(STG.COURSE_KEY is null and COURSE_KEY is not Null)
OR
(STG.COURSE_KEY is not null and COURSE_KEY is Null)
)
OR
(
STG.YEAR_OF_ENTRY <> YEAR_OF_ENTRY
OR
(STG.YEAR_OF_ENTRY is null and YEAR_OF_ENTRY is not Null)
OR
(STG.YEAR_OF_ENTRY is not null and YEAR_OF_ENTRY is Null)
)
OR
(
STG.MONTH_OF_ENTRY <> MONTH_OF_ENTRY
OR
(STG.MONTH_OF_ENTRY is null and MONTH_OF_ENTRY is not Null)
OR
(STG.MONTH_OF_ENTRY is not null and MONTH_OF_ENTRY is Null)
)
OR
(
STG.SSDT_CODE_ENTRY <> SSDT_CODE_ENTRY
OR
(STG.SSDT_CODE_ENTRY is null and SSDT_CODE_ENTRY is not Null)
OR
(STG.SSDT_CODE_ENTRY is not null and SSDT_CODE_ENTRY is Null)
)
)
)
WHERE
(STG.PIDM, STG.APPLICANT_NO, STG.CHOICE_TYPE, STG.CHOICE_TYPE_NO) IN
(SELECT PIDM, APPLICANT_NO, CHOICE_TYPE, CHOICE_TYPE_NO
FROM
SALFORD.UCASTEMPSTAGING
WHERE
(STG.APPLICANT_NO = APPLICANT_NO AND
STG.CHOICE_TYPE = CHOICE_TYPE AND
STG.CHOICE_TYPE_NO = CHOICE_TYPE_NO AND
STG.pidm = pidm
)
AND
(
(
STG.TITLE <> TITLE
OR
(STG.TITLE is null and TITLE is not Null)
OR
(STG.TITLE is not null and TITLE is Null)
)
OR
(
STG.FORENAME <> FORENAME
OR
(STG.FORENAME is null and FORENAME is not Null)
OR
(STG.FORENAME is not null and FORENAME is Null)
)
OR
(
STG.MIDDLE_NAME <> MIDDLE_NAME
OR
(STG.MIDDLE_NAME is null and MIDDLE_NAME is not Null)
OR
(STG.MIDDLE_NAME is not null and MIDDLE_NAME is Null)
)
OR
(
STG.SURNAME <> SURNAME
OR
(STG.SURNAME is null and SURNAME is not Null)
OR
(STG.SURNAME is not null and SURNAME is Null)
)
OR
(
STG.GENDER <> GENDER
OR
(STG.GENDER is null and GENDER is not Null)
OR
(STG.GENDER is not null and GENDER is Null)
)
OR
(
STG.DATE_OF_BIRTH <> DATE_OF_BIRTH
OR
(STG.DATE_OF_BIRTH is null and DATE_OF_BIRTH is not Null)
OR
(STG.DATE_OF_BIRTH is not null and DATE_OF_BIRTH is Null)
)
OR
(
STG.ADDRESS_LINE_1 <> ADDRESS_LINE_1
OR
(STG.ADDRESS_LINE_1 is null and ADDRESS_LINE_1 is not Null)
OR
(STG.ADDRESS_LINE_1 is not null and ADDRESS_LINE_1 is Null)
)
OR
(
STG.ADDRESS_LINE_2 <> ADDRESS_LINE_2
OR
(STG.ADDRESS_LINE_2 is null and ADDRESS_LINE_2 is not Null)
OR
(STG.ADDRESS_LINE_2 is not null and ADDRESS_LINE_2 is Null)
)
OR
(
STG.ADDRESS_LINE_3 <> ADDRESS_LINE_3
OR
(STG.ADDRESS_LINE_3 is null and ADDRESS_LINE_3 is not Null)
OR
(STG.ADDRESS_LINE_3 is not null and ADDRESS_LINE_3 is Null)
)
OR
(
STG.ADDRESS_CITY <> ADDRESS_CITY
OR
(STG.ADDRESS_CITY is null and ADDRESS_CITY is not Null)
OR
(STG.ADDRESS_CITY is not null and ADDRESS_CITY is Null)
)
OR
(
STG.ADDRESS_COUNTY_CODE <> ADDRESS_COUNTY_CODE
OR
(STG.ADDRESS_COUNTY_CODE is null and ADDRESS_COUNTY_CODE is not Null)
OR
(STG.ADDRESS_COUNTY_CODE is not null and ADDRESS_COUNTY_CODE is Null)
)
OR
(
STG.ADDRESS_POST_CODE <> ADDRESS_POST_CODE
OR
(STG.ADDRESS_POST_CODE is null and ADDRESS_POST_CODE is not Null)
OR
(STG.ADDRESS_POST_CODE is not null and ADDRESS_POST_CODE is Null)
)
OR
(
STG.ADDRESS_NATION_CODE <> ADDRESS_NATION_CODE
OR
(STG.ADDRESS_NATION_CODE is null and ADDRESS_NATION_CODE is not Null)
OR
(STG.ADDRESS_NATION_CODE is not null and ADDRESS_NATION_CODE is Null)
)
OR
(
STG.TELEPHONE <> TELEPHONE
OR
(STG.TELEPHONE is null and TELEPHONE is not Null)
OR
(STG.TELEPHONE is not null and TELEPHONE is Null)
)
OR
(
STG.MOBILE_TELEPHONE <> MOBILE_TELEPHONE
OR
(STG.MOBILE_TELEPHONE is null and MOBILE_TELEPHONE is not Null)
OR
(STG.MOBILE_TELEPHONE is not null and MOBILE_TELEPHONE is Null)
)
OR
(
STG.EMAIL_ADDRESS <> EMAIL_ADDRESS
OR
(STG.EMAIL_ADDRESS is null and EMAIL_ADDRESS is not Null)
OR
(STG.EMAIL_ADDRESS is not null and EMAIL_ADDRESS is Null)
)
OR
(
STG.DOMICILE <> DOMICILE
OR
(STG.DOMICILE is null and DOMICILE is not Null)
OR
(STG.DOMICILE is not null and DOMICILE is Null)
)
OR
(
STG.NATIONALITY <> NATIONALITY
OR
(STG.NATIONALITY is null and NATIONALITY is not Null)
OR
(STG.NATIONALITY is not null and NATIONALITY is Null)
)
OR
(
STG.COUNTRY_OF_BIRTH <> COUNTRY_OF_BIRTH
OR
(STG.COUNTRY_OF_BIRTH is null and COUNTRY_OF_BIRTH is not Null)
OR
(STG.COUNTRY_OF_BIRTH is not null and COUNTRY_OF_BIRTH is Null)
)
)
AND
(
(
STG.TT_ADDRESS_LINE_1 <> TT_ADDRESS_LINE_1
OR
(STG.TT_ADDRESS_LINE_1 is null and TT_ADDRESS_LINE_1 is not Null)
OR
(STG.TT_ADDRESS_LINE_1 is not null and TT_ADDRESS_LINE_1 is Null)
)
OR
(
STG.TT_ADDRESS_LINE_2 <> TT_ADDRESS_LINE_2
OR
(STG.TT_ADDRESS_LINE_2 is null and TT_ADDRESS_LINE_2 is not Null)
OR
(STG.TT_ADDRESS_LINE_2 is not null and TT_ADDRESS_LINE_2 is Null)
)
OR
(
STG.TT_ADDRESS_LINE_3 <> TT_ADDRESS_LINE_3
OR
(STG.TT_ADDRESS_LINE_3 is null and TT_ADDRESS_LINE_3 is not Null)
OR
(STG.TT_ADDRESS_LINE_3 is not null and TT_ADDRESS_LINE_3 is Null)
)
OR
(
STG.TT_ADDRESS_CITY <> TT_ADDRESS_CITY
OR
(STG.TT_ADDRESS_CITY is null and TT_ADDRESS_CITY is not Null)
OR
(STG.TT_ADDRESS_CITY is not null and TT_ADDRESS_CITY is Null)
)
OR
(
STG.TT_ADDRESS_COUNTY_CODE <> TT_ADDRESS_COUNTY_CODE
OR
(STG.TT_ADDRESS_COUNTY_CODE is null and TT_ADDRESS_COUNTY_CODE is not Null)
OR
(STG.TT_ADDRESS_COUNTY_CODE is not null and TT_ADDRESS_COUNTY_CODE is Null)
)
OR
(
STG.TT_ADDRESS_POST_CODE <> TT_ADDRESS_POST_CODE
OR
(STG.TT_ADDRESS_POST_CODE is null and TT_ADDRESS_POST_CODE is not Null)
OR
(STG.TT_ADDRESS_POST_CODE is not null and TT_ADDRESS_POST_CODE is Null)
)
OR
(
STG.TT_ADDRESS_NATION_CODE <> TT_ADDRESS_NATION_CODE
OR
(STG.TT_ADDRESS_NATION_CODE is null and TT_ADDRESS_NATION_CODE is not Null)
OR
(STG.TT_ADDRESS_NATION_CODE is not null and TT_ADDRESS_NATION_CODE is Null)
)
)
AND
(
(
STG.ENTRY_TERM <> ENTRY_TERM
OR
(STG.ENTRY_TERM is null and ENTRY_TERM is not Null)
OR
(STG.ENTRY_TERM is not null and ENTRY_TERM is Null)
)
OR
(
STG.RESIDENCY_CODE <> RESIDENCY_CODE
OR
(STG.RESIDENCY_CODE is null and RESIDENCY_CODE is not Null)
OR
(STG.RESIDENCY_CODE is not null and RESIDENCY_CODE is Null)
)
OR
(
STG.COURSE_CODE <> COURSE_CODE
OR
(STG.COURSE_CODE is null and COURSE_CODE is not Null)
OR
(STG.COURSE_CODE is not null and COURSE_CODE is Null)
)
OR
(
STG.COURSE_KEY <> COURSE_KEY
OR
(STG.COURSE_KEY is null and COURSE_KEY is not Null)
OR
(STG.COURSE_KEY is not null and COURSE_KEY is Null)
)
OR
(
STG.YEAR_OF_ENTRY <> YEAR_OF_ENTRY
OR
(STG.YEAR_OF_ENTRY is null and YEAR_OF_ENTRY is not Null)
OR
(STG.YEAR_OF_ENTRY is not null and YEAR_OF_ENTRY is Null)
)
OR
(
STG.MONTH_OF_ENTRY <> MONTH_OF_ENTRY
OR
(STG.MONTH_OF_ENTRY is null and MONTH_OF_ENTRY is not Null)
OR
(STG.MONTH_OF_ENTRY is not null and MONTH_OF_ENTRY is Null)
)
OR
(
STG.SSDT_CODE_ENTRY <> SSDT_CODE_ENTRY
OR
(STG.SSDT_CODE_ENTRY is null and SSDT_CODE_ENTRY is not Null)
OR
(STG.SSDT_CODE_ENTRY is not null and SSDT_CODE_ENTRY is Null)
)
)
);
|
|
|
|
|
|
| Re: sql performance (merged) [message #690278 is a reply to message #690270] |
Thu, 13 February 2025 04:06  |
cookiemonster
Messages: 13965
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Here's a simplified version of what you're doing:
UPDATE STAGING STG
SET (STG.STATUS,
STG.DATE_EXTRACT,
<other cols>) = (SELECT 3,
sysdate
<equivalent other cols>
FROM TEMPSTAGING
WHERE STG.APPLICANT_NO = APPLICANT_NO
AND STG.CHOICE_TYPE = CHOICE_TYPE
AND STG.CHOICE_TYPE_NO = CHOICE_TYPE_NO
AND STG.pidm = pidm
AND <any of the other cols differs TEMPSTAGING vs STAGING>
)
WHERE (STG.PIDM, STG.APPLICANT_NO, STG.CHOICE_TYPE, STG.CHOICE_TYPE_NO) IN
(SELECT PIDM, APPLICANT_NO, CHOICE_TYPE, CHOICE_TYPE_NO
FROM SALFORD.UCASTEMPSTAGING
WHERE STG.APPLICANT_NO = APPLICANT_NO
AND STG.CHOICE_TYPE = CHOICE_TYPE
AND STG.CHOICE_TYPE_NO = CHOICE_TYPE_NO
AND STG.pidm = pidm
AND <any of the other cols differs UCASTEMPSTAGING vs STAGING>
);
There's nothing you can do with the AND/OR's that's likely to improve matters.
I think if you want to speed things up you need to look at the overall process rather than this update in isolation.
Why are there 3 tables involved here? Why are you getting updated values from TEMPSTAGING while checking for existence in UCASTEMPSTAGING?
How many rows in the 3 tables? You say 30K but you haven't said which one.
How long is it actually taking to run?
How long do you want it to take?
|
|
|
|
Goto Forum:
Current Time: Sat Feb 22 22:01:06 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
