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

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

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>


hi

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




| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|

| 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

Original text of this message

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