RE: [Q] does outer join cause full table scan?

From: Lex de Haan <lex.de.haan_at_naturaljoin.nl>
Date: Tue, 30 Mar 2004 20:55:06 +0200
Message-ID: <JFEEIGBIDOCCDALDIPLNOEMGCBAA.lex.de.haan@naturaljoin.nl>

a small correction: in an outer join, there is no choice for the driving table ...

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Bobak, Mark Sent: Tuesday, March 30, 2004 19:48
To: oracle-l_at_freelists.org
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_at_yahoo.com] Sent: Tuesday, March 30, 2004 1:29 PM
To: oracle-l_at_freelists.org
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_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/

FAQ is at http://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_at_freelists.org

put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://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_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Received on Tue Mar 30 2004 - 12:53:43 CST