I have this query in my application
SELECT
/*+ parallel (4) */
a.orgkey AS cifid,
a.strfield19 AS old_cif_id,
a.cust_first_name,
a.cust_middle_name,
a.cust_last_name,
a.primary_sol_id AS sol_id,
(
SELECT lang.localetext
FROM crmuser.categories cat,
crmuser.category_lang lang
WHERE cat.categoryid=lang.categoryid
AND cat.categorytype= 'PERSONSALUTATION'
AND cat.bank_id =
(
SELECT bank_id
FROM migadm.migr_det)
AND lang.bank_id =
(
SELECT bank_id
FROM migadm.migr_det)
AND cat.value= a.salutation) AS saluatation,
a.segmentation_class AS segmentation_class,
CASE
WHEN corp_id IS NULL THEN
(
SELECT lang.localetext
FROM crmuser.categories cat,
crmuser.category_lang lang
WHERE cat.categoryid=lang.categoryid
AND cat.categorytype= 'SEGMENTATION_CLASS'
AND cat.bank_id =
(
SELECT bank_id
FROM migadm.migr_det)
AND lang.bank_id =
(
SELECT bank_id
FROM migadm.migr_det)
AND cat.value= a.segmentation_class)
ELSE
(
SELECT lang.localetext
FROM crmuser.categories cat,
crmuser.category_lang lang
WHERE cat.categoryid=lang.categoryid
AND cat.categorytype= 'CORP_SEGMENTATION_CLASS'
AND cat.bank_id =
(
SELECT bank_id
FROM migadm.migr_det)
AND lang.bank_id =
(
SELECT bank_id
FROM migadm.migr_det)
AND cat.value= a.segmentation_class)
END AS segmentation_desc,
a.subsegment AS subsegment,
CASE
WHEN corp_id IS NULL THEN
(
SELECT lang.localetext
FROM crmuser.categories cat,
crmuser.category_lang lang
WHERE cat.categoryid=lang.categoryid
AND cat.categorytype= 'SUB_SEGMENT'
AND cat.bank_id =
(
SELECT bank_id
FROM migadm.migr_det)
AND lang.bank_id =
(
SELECT bank_id
FROM migadm.migr_det)
AND cat.value= a.subsegment)
ELSE
(
SELECT lang.localetext
FROM crmuser.categories cat,
crmuser.category_lang lang
WHERE cat.categoryid=lang.categoryid
AND cat.categorytype= 'CORP_SUB_SEGMENT'
AND cat.bank_id =
(
SELECT bank_id
FROM migadm.migr_det)
AND lang.bank_id =
(
SELECT bank_id
FROM migadm.migr_det)
AND cat.value= a.subsegment)
END AS subsegment_desc,
CASE
WHEN a.corp_id IS NULL THEN a.strfield10
ELSE c.struserfield4
END AS reserve_bank_code,
CASE
WHEN a.corp_id IS NULL THEN a.strfield11
ELSE c.struserfield5
END AS form50_code,
a.sector AS pri_sic_code,
a.subsector AS sec_sic_code,
CASE
WHEN a.corp_id IS NULL THEN a.struserfield3
ELSE c.struserfield3
END AS kyc_indicator,
a.defaultaddresstype AS defaultaddresstype,
b.address_line1,
b.address_line2,
b.address_line3,
CASE
WHEN a.corp_id IS NULL THEN
(
SELECT (ad.address_line1
||' '
||ad.address_line2
||' '
||ad.address_line3)
FROM crmuser.address ad
WHERE a.orgkey=ad.orgkey
AND ad. preferredaddress='Y'
AND
(
SELECT migration_date
FROM migadm.migr_det) BETWEEN ad.start_date AND ad.end_date)
ELSE
(
SELECT (ad.address_line1
||' '
||ad.address_line2
||' '
||ad.address_line3)
FROM crmuser.address ad
WHERE a.orgkey=ad.orgkey
AND ad.preferredaddress='Y' and
(
SELECT migration_date
FROM migadm.migr_det) BETWEEN ad.start_date AND ad.end_date)
END AS postal_adress,
b.zip AS postal_code,
a.gender AS gender,
a.cust_dob AS date_of_birth,
(
SELECT p.email
FROM crmuser.phoneemail p
WHERE p.orgkey = a.orgkey
AND p.preferredflag='Y'
AND phoneoremail='EMAIL'
AND ROWNUM =1) email_id,
(
SELECT p.phonenocountrycode
FROM crmuser.phoneemail p
WHERE p.orgkey = a.orgkey
AND p.preferredflag='Y'
AND phoneoremail='PHONE'
AND ROWNUM =1) AS phone_cntry_code,
(
SELECT p.phonenocitycode
FROM crmuser.phoneemail p
WHERE p.orgkey = a.orgkey
AND p.preferredflag='Y'
AND phoneoremail='PHONE'
AND ROWNUM =1) AS phone_city_code,
(
SELECT '+'
|| p.phonenocountrycode
|| '('
|| p.phonenocitycode
|| ')'
|| p.phonenolocalcode
FROM crmuser.phoneemail p
WHERE p.orgkey = a.orgkey
AND p.preferredflag = 'Y'
AND phoneoremail='PHONE') phone_no,
--CASE WHEN (SELECT COUNT(1) FROM crmuser.miscellaneousinfo m WHERE a.orgkey=m.orgkey and type = 'CURRENCY') = 1 THEN (SELECT strtext10 FROM crmuser.miscellaneousinfo m WHERE a.orgkey=m.orgkey and type = 'CURRENCY')
-- WHEN (SELECT COUNT(1) FROM crmuser.corpmiscellaneousinfo m WHERE a.orgkey=m.orgkey and type = 'CURRENCY') = 1 THEN (SELECT str1 FROM crmuser.corpmiscellaneousinfo m WHERE a.orgkey=m.orgkey and type = 'CURRENCY')
-- ELSE
migadm.getcurrencies(a.orgkey)
--END
AS crncy_code,
CASE
WHEN a.corp_id IS NULL THEN a.struserfield15
ELSE c.struserfield6
END AS wht_indicator,
a.cust_swift_code_desc AS cust_swift_code_desc,
customernreflg AS customernreflg,
(
SELECT employerid
FROM crmuser.miscellaneousinfo s
WHERE s.orgkey = a.orgkey
AND TYPE ='CURRENT_EMPLOYMENT') AS group_number,
(
SELECT strtext4
FROM crmuser.miscellaneousinfo s
WHERE s.orgkey = a.orgkey
AND TYPE ='CURRENT_EMPLOYMENT' ) AS employment_details,
--(select EMPLOYERSNAME from crmuser.DEMOGRAPHIC s where s.orgkey = a.orgkey ) AS employment_details,
(
SELECT employeeid
FROM crmuser.miscellaneousinfo s
WHERE s.orgkey = a.orgkey
AND TYPE ='CURRENT_EMPLOYMENT' ) AS employeeid,
--(select EMPLOYERID from crmuser.MISCELLANEOUSINFO s where s.orgkey = a.orgkey ) AS employerid,
a.manager AS manager,
CASE
WHEN a.corp_id IS NULL THEN d.annual_salary_income
ELSE c.average_annualincome
END AS annual_income,
c.registration_number
FROM crmuser.corporate c
right join crmuser.address b
ON b.orgkey=c.corp_key
right join crmuser.accounts a
ON b.orgkey=a.orgkey
left join crmuser.demographic d
ON a.orgkey=d.orgkey
WHERE a.bank_id =
(
SELECT bank_id
FROM migadm.migr_det)
AND a.entity_cre_flag = 'Y'
AND b.preferredaddress = 'Y'
It takes more than 20 minutes to give results. Can someone please help me in tuning this query. I don't have any idea about sql. I can give the explain plan needed but I don't know how to copy here the 111 rows from plan table
