Tune the query [message #483201] |
Fri, 19 November 2010 03:32 |
oraranjangmailcom
Messages: 67 Registered: April 2010
|
Member |
|
|
Hi,
Can I have some help in tuning below query.
Query looks huge but actually logic is simple.
For some set of columns, certain conditions has to satisfy.
Hence for each set of conditions, a separate hit to the same table (OR) separate query.
Note: Same table has been utilised many times (name_asgnmt)in the query.
SELECT
chn.entity_id,
chn.entity_type_cd,
eng.full_nm,
eng.nm_search_txt,
fr.full_nm,
fr.nm_search_txt,
acc.full_nm,
acc.nm_search_txt,
acc_alt.full_nm,
acc_alt.nm_search_txt,
acc.last_updt_ts
FROM
(SELECT DISTINCT cn.entity_id,cn.entity_type_cd FROM customer_hierarchy_node cn,name_asgnmt na
WHERE cn.entity_id = na.entity_id
AND cn.eff_end_dt > sysdate
AND na.nm_asgnmt_subtyp_cd = 'O'
AND na.nm_asgnmt_typ_cd in ('L','I') ) CHN
LEFT OUTER JOIN
( SELECT entity_id as entity,full_nm,nm_search_txt FROM name_asgnmt
WHERE nm_asgnmt_subtyp_cd = 'O'
AND nm_asgnmt_typ_cd = 'L'
AND lang_cd = 'EN'
AND eff_end_dt > sysdate
AND entity_type_cd in ('CUSTOMER','CHN') ) eng ON eng.entity = chn.entity_id
LEFT OUTER JOIN
(SELECT entity_id as entity,full_nm,nm_search_txt,last_updt_ts FROM name_asgnmt
WHERE nm_asgnmt_subtyp_cd = 'O'
AND nm_asgnmt_typ_cd = 'L'
AND eff_end_dt > sysdate
AND entity_type_cd = 'ACCOUNT' ) acc on acc.entity = chn.entity_id
LEFT OUTER JOIN
(SELECT entity_id as entity, full_nm,nm_search_txt FROM name_asgnmt
WHERE nm_asgnmt_subtyp_cd = 'O'
AND nm_asgnmt_typ_cd = 'I'
AND eff_end_dt > sysdate
AND entity_type_cd = 'ACCOUNT' )acc_alt on acc_alt.entity = chn.entity_id
LEFT OUTER JOIN
(select entity_id as entity,full_nm,nm_search_txt FROM name_asgnmt
WHERE nm_asgnmt_subtyp_cd = 'O'
AND nm_asgnmt_typ_cd = 'L'
AND lang_cd = 'FR'
AND eff_end_dt > sysdate
AND entity_type_cd in ('CUSTOMER','CHN') )fr on fr.entity = chn.entity_id
Thanks,
Ranjan
|
|
|
|