left outer join

From: Sai Selvaganesan <ssaisundar_at_sbcglobal.net>
Date: Fri, 20 Aug 2004 11:56:27 -0700 (PDT)
Message-ID: <20040820185627.23611.qmail@web81607.mail.yahoo.com>

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
-----------------------------------------------------------------

Received on Fri Aug 20 2004 - 13:52:37 CDT