I am working on a query qhich has lot of RIGHT and LEFT Outer joins. This takes almost 2 hours to execute.
SELECT mstcurrency.pkcurid, mstcurrency.curswift_code, mstproducts.pkprdid, mstproducts.prdname,
mstproducts.fkcnvid_prdbaseuom,cpuser.uspname || ' ' || cpuser.uspsurname as uspname, cpuser.pkuspid,
cpuser.uspnotify_numberAScpusercontactdetails, cpuser.fktrdid_usptradhouseid as cpthid, mstcompanies.pkcmpid,
mstcompanies.cmpname,mstcompanies.cmpaccount_type, mstfacilityviews.facname, mstfacilityviews.pkfacid
FROM mstuserprofiles cpuser RIGHT OUTER JOIN mstcompanies ON cpuser.fktrdid_usptradhouseid =
mstcompanies.fktrdid_cmptradhouseid AND cpuser.pkuspid = mstcompanies.cmpcontact_userid LEFT OUTER JOIN mstcurrency ON
mstcompanies.fktrdid_cmptradhouseid= mstcurrency.fktrdid_curtradhouseid AND mstcompanies.fkcurid_cmpcurid =
mstcurrency.pkcuridLEFTOUTER JOIN mstproducts ON mstcompanies.fktrdid_cmptradhouseid =
mstproducts.fktrdid_prdtradhouseid AND mstcompanies.cmpmain_product = mstproducts.pkprdid RIGHT OUTER JOIN
mstuserprofilesLEFT OUTER JOIN mstfacilityviews ON mstuserprofiles.uspdefaultfacility = mstfacilityviews.pkfacid ON
mstcompanies.pkcmpid= mstuserprofiles.uspdefaultcounterparty AND mstcompanies.fktrdid_cmptradhouseid =
mstuserprofiles.fktrdid_usptradhouseid
WHERE mstuserprofiles.pkuspid = 101 AND mstuserprofiles.fktrdid_usptradhouseid = 1