RE: [Q] does outer join cause full table scan?
- From: "Lex de Haan" <lex.de.haan@xxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 30 Mar 2004 21:37:13 +0200
Hi Mark,
yep -- you are right there. note however that due to the "dynamic role
reversal" feature, the concept of a driving table for hash joins is a little
bit slippery anyway ...
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Bobak, Mark
Sent: Tuesday, March 30, 2004 20:11
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: [Q] does outer join cause full table scan?
Lex,
Is that always true?
I seem to remember a discussion a while back on the OakTable list regarding
hash outer joins, and that Oracle would not use the deficient row source as
the driving table, though there was no good reason not to. The reply was
that it was true for current Oracle releases, and seemed to imply that it
was going to be fixed in a future release. I haven't tested 10g to see what
it does. (I'd go do a quick test now, but my 10g instance crashed the other
day when we lost a disk, and I haven't bothered to re-create it yet.)
It certainly makes sense in the context of a nested loops join, though.
-Mark
-----Original Message-----
From: Lex de Haan [mailto:lex.de.haan@xxxxxxxxxxxxxx]
Sent: Tuesday, March 30, 2004 1:55 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: [Q] does outer join cause full table scan?
a small correction: in an outer join, there is no choice for the driving
table ...
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Bobak, Mark
Sent: Tuesday, March 30, 2004 19:48
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: [Q] does outer join cause full table scan?
Um, no, outer join does not necessitate a full table scan. You mention
having indexes on your join columns. That's (probably) a good thing, but,
if you have something like:
select a.*, b.* from a, b
where a.join_id = b.join_id(+);
then, at a minimum, Oracle MUST FTS the driving table. That's because there
are no filter predicates. If the statement looked something like:
select a.*, b.* from a, b
where a.join_id = b.join_id(+)
and a.filter_column = 'some value';
AND if there is an index on a.filter_column, then Oracle MAY choose to drive
from table a, use that index, and join the filtered rowsource to table b
using the index on b.join_id.
Also, keep in mind, with CBO, Oracle decides whether it's reasonable to use
any given index. The above was just to illustrate that it's possible to do
an outer join and avoid FTS.
-Mark
-----Original Message-----
From: dba1 mcc [mailto:mccdba1@xxxxxxxxx]
Sent: Tuesday, March 30, 2004 1:29 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: [Q] does outer join cause full table scan?
We have sql statement outer join two tables. The join
columns have index on it. I use "tkprof" to check it
and it show full table scan.
Does outer join cause 'full table scan"?
Thanks.
__________________________________
Do you Yahoo!?
Yahoo! Finance Tax Center - File online. File on time.
http://taxes.yahoo.com/filing.html
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
