| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Outer Joins are Evil?
The reasons given for this seem to be -
1. Simplifies user navigation of the structures - i.e. avoids outer
joins.
2. Outer joins are slow and should be avoided at all costs.
3. If an FK is missing it is populated with a default value which will
relate to an actual row in the target table, hence no rows will ever be
dropped - again, supposedly this is to simply SQL and avoid outer joins.
For many cases (I am very tempted to say 'most', but I am sure I would be inundated with counter-examples), outer-joins are not worse than regular (inner) joins. Consider this one :
select
from T1, T2
where T1.C1 = <some value>
and T1.C2 = T2.C2 (+)
Assume that T2 is indexed on C2. For a regular join, let's say that the
optimizer decides to use a nested loop with T1 as the driving table, for
eachrow which satisfies the condition on C1 it will use the value of C2 to
join on T2.
In a regular join, you search the index, if you find the key then you access
the ad hoc block from T2 and return the data. If you don't find it end of
thestory. With the outer join, if you don't find it the column in the index,
you can return the data even faster than in the case of the inner join with
amatch, since you have no table access.
The main worry I have with outer joins is that I'd rather have more outer joins than Oracle allows me too ...
HTH, Stephane Faroult
 |
 |