RE: left outer join
- From: "Gogala, Mladen" <Mladen.Gogala@xxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 20 Aug 2004 15:09:11 -0400
You have full table scan on tab1 because lef outer join
means that the join column(s) of the left table is(are) equal
to the join column(s) of the right table or are NULL.
If you take a look at the performance tuning manual, it will
tell you that B-tree indexes are not used for the resolution
of "IS NULL" condition. You, of course, have "IS NULL" on
both sides of the equation, which means that outer join does
not make sense in your situation. Your query can be re-written
as:
select count(*) from tab1 g1,tab2 g2
where g1.iuid is null
and g2.yypid is null;
Your query is logically incorrect because NULL != NULL.
--
Mladen Gogala
Oracle DBA
email:mladeng@xxxxxxxxx
Ext: 9787
> -----Original Message-----
> From: Sai Selvaganesan [mailto:ssaisundar@xxxxxxxxxxxxx]
> Sent: Friday, August 20, 2004 2:56 PM
> To: oracle-l@xxxxxxxxxxxxx
> Subject: left outer join
>
>
> 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@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
