Some time ago I encountered an issue with an outerjoin query. Although the execution plan was not that bad the respons time was really bad. I found out that the outerjoin in the query was causing the biggest problem. After doing some easy research I checked out the performance of a query with a direct join (a.col = b.col) and one without a join (a.col is not joined). Even if you would execute them seperate you would be much faster as doing the outerjoin. This brought me to the idea of doing these two queries for the sake of the data be retrieved by the outerjoin. By doing a union between these two queries and to get rid of the double records, I would have the same result as with the outerjoin. This is what I did, a collaegae of mine Jorrit Nijssen changed the code to a emp/dept example (thanks Jorrit). The base case looks like:

SELECT *

FROM

(SELECT d.deptno,

d.dname,

d.loc,

e.empno,

e.job,

e.mgr,

e.hiredate,

e.sal,

e.comm

FROM scott.dept d,

scott.emp e

WHERE e.deptno (+) = d.deptno

)

/

 

 

 

 

 

 

 

 

 

Based on this base query the following devisions can be made

prompt — Divide into two cases

prompt — departments whit employees

prompt — departments whitout employees

SELECT *

FROM

(SELECT d.deptno,

d.dname,

d.loc,

e.empno,

e.job,

e.mgr,

e.hiredate,

e.sal,

e.comm

, 1 id

FROM scott.dept d,

scott.emp e

WHERE e.deptno = d.deptno

union

SELECT d.deptno,

d.dname,

d.loc,

null,

null,

null,

null,

null,

null

,2

FROM scott.dept d

)

/

 

 

 

 

 

 

 

This gives of course double records. The trick I did was giving an extra column to the queries to know from which query the retrieved record is comming from (see above). Further more I had to get rid of the double records, by using an analytic function:

prompt — Too many rows

prompt — records from query id 1 will selected again in query id 2

prompt — Analyic functions to the resue?

SELECT deptno

, deptno_prev

, id

, dname

, loc

, empno

, job

, mgr

, hiredate

, sal

, comm

FROM

(SELECT a.deptno

, a.dname

, a.loc

, a.empno

, a.job

, a.mgr

, a.hiredate

, a.sal

, a.comm

, a.id

, lag(a.deptno,1,-99) over (partition by a.deptno order by a.deptno, a.id) deptno_prev

FROM

(SELECT d.deptno,

d.dname,

d.loc,

e.empno,

e.job,

e.mgr,

e.hiredate,

e.sal,

e.comm ,

1 id

FROM scott.dept d,

scott.emp e

WHERE e.deptno = d.deptno

UNION

SELECT d.deptno,

d.dname,

d.loc,

NULL,

NULL,

NULL,

NULL,

NULL,

NULL ,

2

FROM scott.dept d

WHERE 1=1

) a

)

WHERE 1=1

order by deptno, id, deptno_prev

/

prompt — Now with where clause

SELECT deptno

, deptno_prev

, id

, dname

, loc

, empno

, job

, mgr

, hiredate

, sal

, comm

FROM

(SELECT a.deptno

, a.dname

, a.loc

, a.empno

, a.job

, a.mgr

, a.hiredate

, a.sal

, a.comm

, a.id

, lag(a.deptno,1,-99) over (partition by a.deptno order by a.deptno, a.id) deptno_prev

FROM

(SELECT d.deptno,

d.dname,

d.loc,

e.empno,

e.job,

e.mgr,

e.hiredate,

e.sal,

e.comm ,

1 id

FROM scott.dept d,

scott.emp e

WHERE e.deptno = d.deptno

UNION

SELECT d.deptno,

d.dname,

d.loc,

NULL,

NULL,

NULL,

NULL,

NULL,

NULL ,

2

FROM scott.dept d

WHERE 1=1

) a

)

WHERE 1=1

AND ( id = 1 — department has employees

OR

( id = 2 AND NOT (deptno_prev = deptno) ) — Previous department in partition is either -99 (first record) or another deparment

)

order by deptno, id, deptno_prev

/

 

 

 

 

 

 

So now we have written our base query with an outerjoin to a query without an outerjoin. In some cases the described method can help you enhance such queries in a very significant way.

Regards, Gerwin