FW: Join order and intermediate results
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: "Oracle-L" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 1 Oct 2004 12:16:24 -0400
oops. didn't include list.
-----Original Message-----
From: Mark W. Farnham [mailto:mwf@xxxxxxxx]
Sent: Friday, October 01, 2004 12:12 PM
To: SMILEYJ@xxxxxxxx
Subject: RE: Join order and intermediate results
select ab.a1, ab.b1, cd.c1, dd.d1
from
(select rownum abr, a.a1, b.b1 from a,b where a.a1 = b.b1) ab,
(select rownum cdr, c.c1, d.d1 from c,d where c.c1 = d.d1) cd
where .....
for example, forces Oracle to produce the inline views in order to get valid
values for ab.abr and cd.cbr.
Give it a whirl. I'm not sure who documented this first, but I think there
is something about it on asktom.
I think it was the very first thing I tried when they created "inline
views." Hmm. Maybe I tried orderby
on the inline views first. Let me know if you get different results than I
expect.
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Smiley John - IL
Sent: Friday, October 01, 2004 11:43 AM
To: 'mwf@xxxxxxxx'; oracle-l@xxxxxxxxxxxxx
Subject: RE: Join order and intermediate results
Sorry if I'm being dense, but how is including rownum going affect the
execution plan? Example?
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Mark W. Farnham
Sent: Friday, October 01, 2004 10:37 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Join order and intermediate results
Including the rownum in the inline views (or whatever we're calling them
this week) is the standard trick to force what you seem to want.
-----Original Message-----
<snip>
When that didn't work and I made sure the table, index, and column stats
were correct, I tried various hints for join order and join method. When
that didn't work, I decided to smack Oracle in the face with the answer like
this:
SELECT ...
FROM (SELECT ... FROM A, B WHERE ...) AB, (SELECT ... FROM C, D WHERE ...)
CD
WHERE ...
<snip>
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » FW: Join order and intermediate results
