Performance problem with left outer join in 11g
We are in the testing phase of upgrading our 10.2.0.3 databases to 11.1.0.7. One query is killing us and it is obstructing us to proceed further with the upgrade of the rest of the databases. Here is the query.
Long Running Query in 11g:
SELECT A.EMPLID, A.Z_MGR_EMAILID, B.Z_MGR_EMAILID
FROM (table1 A LEFT OUTER JOIN table1 B ON A.MANAGER_ID = B.EMPLID ), view1 A1, view1 B1
WHERE A.EMPLID = A1.EMPLID
AND A.EMPL_RCD = A1.EMPL_RCD
AND A1.OPRID = 'userid'
AND (B.EMPLID = B1.EMPLID OR B.EMPLID IS NULL )
AND (B.EMPL_RCD = B1.EMPL_RCD OR B.EMPL_RCD IS NULL )