Streamlining a SQL Query

[fibonacci_jones]

I can't go into too much detail about the database schema... but does anyone have any ideas as to how I can streamline this query even further?



SELECT * from (SELECT DISTINCT ltrim(rtrim(ee.ety_entity)) as ety_entity
,ltrim(rtrim(ee.ety_name)) as ety_name
,NVL(ltrim(rtrim(u1.prju_data)), '-1') as AML_Exemption
,NVL(ltrim(rtrim(u2.prju_data)), '-1') as AML_Verification_Doc_ID
,NVL(ltrim(rtrim(u3.prju_data)), '-1') as Pulse_AML_Entity_ID
,NVL(ltrim(rtrim(u4.prju_data)), '-1') as Instructing_Person
FROM PRJ_PROJECT prj INNER JOIN ety_entity ee ON prj.prj_ety_entity = ee.ety_entity
LEFT OUTER JOIN

(prju_project_user_column u1 INNER JOIN
prjc_project_column_cntl c1 ON u1.prju_prjc_number = c1.prjc_number
AND c1.prjc_description = 'AML Verification Doc ID'
) ON prj.prj_project_num = u1.prju_prj_project_num AND
prj.prj_off_office = u1.prju_prj_off_office
LEFT OUTER JOIN
(prju_project_user_column u2 INNER JOIN
prjc_project_column_cntl c2 ON u2.prju_prjc_number = c2.prjc_number
AND c2.prjc_description = 'AML Instructing Doc ID'
) ON prj.prj_project_num = u2.prju_prj_project_num AND
prj.prj_off_office = u2.prju_prj_off_office
LEFT OUTER JOIN
(prju_project_user_column u3 INNER JOIN
prjc_project_column_cntl c3 ON u3.prju_prjc_number = c3.prjc_number
AND c3.prjc_description = 'Pulse AML Contact ID'
) ON prj.prj_project_num = u3.prju_prj_project_num AND
prj.prj_off_office = u3.prju_prj_off_office
LEFT OUTER JOIN
(prju_project_user_column u4 INNER JOIN
prjc_project_column_cntl c4 ON u4.prju_prjc_number = c4.prjc_number
AND c4.prjc_description = 'Instructing Person'
) ON prj.prj_project_num = u4.prju_prj_project_num AND
prj.prj_off_office = u4.prju_prj_off_office

WHERE ety_status = 'C' AND prj.prj_status in ('C')
AND prj_off_office = '20' ) x WHERE ROWNUM <= 10 AND UPPER(x.AML_Exemption) NOT LIKE '%YYY EXEMPT%' AND
TRIM(UPPER(x.Instructing_Person)) LIKE '%XXX%'
ORDER BY ety_entity asc, aml_verification_doc_id desc, instructing_person asc, Pulse_AML_Entity_ID desc
') oq
LEFT OUTER JOIN (
SELECT ee.eFolderID, MAX(eEventTime) as dateVerified
FROM eEvent ee
INNER JOIN AML_Contact_ID aci on ee.efolderid = aci.efolderid
WHERE eActionName IN ('Verify AML ID','AML exempt')
GROUP BY ee.eFolderID, eActionName ) p
ON p.efolderid = cast(oq.Pulse_AML_Entity_ID as int)



I'm thinking that possibly the LEFT OUTER JOINs can be conglomerated somehow... but it's not as easy as you think... but I'd like to get my query working in under 30 seconds seeing the database is quite large...

Thanks in advance,

Fib Jones...