RE: left outer join

From: Powell, Mark D <mark.powell_at_eds.com>
Date: Fri, 20 Aug 2004 15:08:36 -0400
Message-ID: <564DE4477544D411AD2C00508BDF0B6A2133DE89@usahm018.exmi01.exch.eds.com>

I do not use the ANSI syntax very often but a left join makes sense only as in "left outer join" and the explain plan shows "nested loops outer" so when you perform a left outer join you are telling Oracle that you want to return each and every row in tab1 plus the matching row data from tab2 when it exists and null values for the tab2 column data when the matching rows do not exist. Now since you must return each and every row in tab1 a full table scan is the most efficient way to do this.

Perhaps you just want to perform an inner join.

HTH -- Mark D Powell --

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Sai Selvaganesan Sent: Friday, August 20, 2004 2:56 PM
To: oracle-l_at_freelists.org
Subject: left outer join

i have the following query that does a left outer join

explain plan for
select count(*) from tab1 g1 left join tab2 i on g1.iuid=i.yypid where i.yypid IS NULL

the explain plan is

PLAN_TABLE_OUTPUT

| 0 | SELECT STATEMENT | | 1 | 12 | 149K (8)|
| 1 | SORT AGGREGATE | | 1 | 12 | |

|*  2 |   FILTER             |             |       |       |            | 


|   3 |    NESTED LOOPS OUTER|             |       |       |            | 

|   4 |     TABLE ACCESS FULL| tab1      |    13M|    77M|   143K  (4)| 

|*  5 |     INDEX UNIQUE SCAN| PK_tab2  |     1 |     6 |            | 
-------------------------------------------------------------------------

PLAN_TABLE_OUTPUT

Predicate Information (identified by operation id):

2 - filter("I"."YYPID" IS NULL)
5 - access("G1"."IUID"="I"."YYPID"(+))

17 rows selected.

the full table scan on tab1 is a botheration. can some explain why that is the case. i have an index on the join column and all stats,histograms etc are upto date.

i tried doing a 10053 trace on this query and i find optimizer never even checks the path that traverses through the index build on tab1.iuid.

here is the single table access path from 10053: SINGLE TABLE ACCESS PATH
TABLE: TAB1 ORIG CDN: 13522500 ROUNDED CDN: 13522500 CMPTD CDN: 13522500
Access path: tsc Resc: 14484 Resp: 14484 Access path: index (no sta/stp keys)

      Index: IDX_TAB1_MTIME
  TABLE: TAB1      RSC_CPU: 0   RSC_IO: 42405

IX_SEL: 1.0000e+00 TB_SEL: 1.0000e+00 Access path: index (no sta/stp keys)

      Index: IDX_TAB1_PUBTIME
  TABLE: TAB1      RSC_CPU: 0   RSC_IO: 34671

IX_SEL: 1.0000e+00 TB_SEL: 1.0000e+00 Access path: index (no sta/stp keys)

Index: PK_TAB1 TABLE: TAB1 RSC_CPU: 0 RSC_IO: 31008 IX_SEL: 1.0000e+00 TB_SEL: 1.0000e+00 Access path: index (no sta/stp keys)

Index: PK_TAB1 TABLE: TAB1 RSC_CPU: 0 RSC_IO: 31008 IX_SEL: 1.0000e+00 TB_SEL: 1.0000e+00 BEST_CST: 14484.00 PATH: 2 Degree: 1

can someone please explain or tell me what is happening. does outer joins always behave this way?

thanks
sai

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 Fri Aug 20 2004 - 14:05:39 CDT