RE: left outer join

Mladen,

I think you have left and right outer join confused. The left or right = indicates the side of the join that is not deficient.

So, in the example below, tab1 left join tab2 means that tab1 has all = rows and tab2 may be missing rows.

So, I think his query is valid. He's counting how many rows in tab1 are = not in tab2 (where tab2.yyid is null).

-Mark

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Gogala, Mladen Sent: Friday, August 20, 2004 3:09 PM
To: 'oracle-l_at_freelists.org'
Subject: RE: left outer join

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 !=3D NULL.

--
Mladen Gogala
Oracle DBA
email:mladeng_at_aetvn.com
Ext: 9787




> -----Original Message-----

> From: Sai Selvaganesan [mailto:ssaisundar_at_sbcglobal.net]=20

> Sent: Friday, August 20, 2004 2:56 PM

> To: oracle-l_at_freelists.org

> Subject: left outer join

>=20
>=20


> hi=20

>=20


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

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

>=20


> the explain plan is=20

>=20


>  =20

>=20


> PLAN_TABLE_OUTPUT=20

> --------------------------------------------------------------

> --------------------------------------------------------------------=20

>=20


> --------------------------------------------------------------

> -----------=20

> | Id  | Operation            |  Name       | Rows  | Bytes |=20

> Cost (%CPU)|=20

> --------------------------------------------------------------

> -----------=20

> |   0 | SELECT STATEMENT     |             |     1 |    12 | =20

>  149K  (8)|=20

> |   1 |  SORT AGGREGATE      |             |     1 |    12 | =20

>           |=20

> |*  2 |   FILTER             |             |       |       | =20

>           |=20

> |   3 |    NESTED LOOPS OUTER|             |       |       | =20

>           |=20

> |   4 |     TABLE ACCESS FULL| tab1      |    13M|    77M|  =20

> 143K  (4)|=20

> |*  5 |     INDEX UNIQUE SCAN| PK_tab2  |     1 |     6 |    =20

>        |=20

> --------------------------------------------------------------

> -----------=20

>=20


> PLAN_TABLE_OUTPUT=20

> --------------------------------------------------------------

> --------------------------------------------------------------------=20

>=20


> Predicate Information (identified by operation id):=20

> ---------------------------------------------------=20

>=20


>    2 - filter("I"."YYPID" IS NULL)=20

>    5 - access("G1"."IUID"=3D"I"."YYPID"(+))=20

>=20


> 17 rows selected.=20

>=20


> the full table scan on tab1 is a botheration. can some=20

> explain why that is the case. i have an index on the join=20

> column and all stats,histograms etc are upto date.

> =20

> i tried doing a 10053 trace on this query and i find=20

> optimizer never even checks the path that traverses through=20

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

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

> outer joins always behave this way?

> =20

> thanks

> sai

>=20
>=20


> ----------------------------------------------------------------

> Please see the official ORACLE-L FAQ: http://www.orafaq.com

> ----------------------------------------------------------------

> To unsubscribe send email to:  oracle-l-request_at_freelists.org=20

> 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

> -----------------------------------------------------------------

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