RE: [Q] does outer join cause full table scan?
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 30 Mar 2004 13:47:39 -0500
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
-----------------------------------------------------------------
Other related posts:
