From oracle-l-bounce@freelists.org Tue Mar 30 12:45:01 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i2UIj1s18428 for ; Tue, 30 Mar 2004 12:45:01 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i2UIj0o18422 for ; Tue, 30 Mar 2004 12:45:00 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 26A066352F1; Tue, 30 Mar 2004 13:40:46 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 32383-79; Tue, 30 Mar 2004 13:40:46 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 6E1826352C6; Tue, 30 Mar 2004 13:40:45 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 30 Mar 2004 13:39:32 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id F31A5634239 for ; Tue, 30 Mar 2004 13:39:31 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 32114-68 for ; Tue, 30 Mar 2004 13:39:31 -0500 (EST) Received: from tera.umi.com (tera.umi.com [192.195.245.144]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 91606634B4F for ; Tue, 30 Mar 2004 13:39:31 -0500 (EST) Received: from bosmail02.bos.il.pqe (bosmail02.bos.il.pqe [172.24.3.66]) by tera.umi.com (8.11.6/8.11.6) with ESMTP id i2UIlc908789 for ; Tue, 30 Mar 2004 13:47:38 -0500 Received: from bosmail00.bos.il.pqe ([172.24.3.64]) by bosmail02.bos.il.pqe with Microsoft SMTPSVC(5.0.2195.6713); Tue, 30 Mar 2004 13:47:39 -0500 X-MimeOLE: Produced By Microsoft Exchange V6.0.6249.0 content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by Ecartis Subject: RE: [Q] does outer join cause full table scan? Date: Tue, 30 Mar 2004 13:47:39 -0500 Message-ID: <4C9B6FDA0B06FE4DAF5918BBF0AD82CFECFC6A@bosmail00.bos.il.pqe> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [Q] does outer join cause full table scan? Thread-Index: AcQWhQNlngUNssmESf6ZsAjMCFv3/gAAaHtQ From: "Bobak, Mark" To: X-OriginalArrivalTime: 30 Mar 2004 18:47:39.0882 (UTC) FILETIME=[7A246CA0:01C41687] X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 2093 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Mark.Bobak@il.proquest.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org 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@yahoo.com] Sent: Tuesday, March 30, 2004 1:29 PM To: oracle-l@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@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@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 -----------------------------------------------------------------