Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: left outer join

RE: left outer join

From: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Fri, 20 Aug 2004 15:09:41 -0400
Message-ID: <4C9B6FDA0B06FE4DAF5918BBF0AD82CF09660D48@bosmail00.bos.il.pqe>


Well, there is no filter predicate on tab1, and since tab2 is on the = deficient side of the outer join, it can't be used as a driving table.

I don't think there is any way to avoid a full table scan in this case.

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

hi=20

i have the following query that does a left outer join=20

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

the explain plan is=20

 =20

PLAN_TABLE_OUTPUT=20

-------------------------------------------------------------------------=
---------------------------------------------------------=20

-------------------------------------------------------------------------=

=20
| Id | Operation | Name | Rows | Bytes | Cost =
(%CPU)|=20
-------------------------------------------------------------------------=

=20
| 0 | SELECT STATEMENT | | 1 | 12 | 149K = (8)|=20 | 1 | SORT AGGREGATE | | 1 | 12 | = |=20 |* 2 | FILTER | | | | = |=20 | 3 | NESTED LOOPS OUTER| | | | = |=20 | 4 | TABLE ACCESS FULL| tab1 | 13M| 77M| 143K (4)|=20 |* 5 | INDEX UNIQUE SCAN| PK_tab2 | 1 | 6 | |=20 -------------------------------------------------------------------------=

=20

PLAN_TABLE_OUTPUT=20

-------------------------------------------------------------------------=
---------------------------------------------------------=20

Predicate Information (identified by operation id):=20

---------------------------------------------------=20

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

17 rows selected.=20

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.
=20

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.
=20

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

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

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:06:35 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US