RE: left outer join
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 20 Aug 2004 15:09:41 -0400
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Sai Selvaganesan
Sent: Friday, August 20, 2004 2:56 PM
To: oracle-l@xxxxxxxxxxxxx
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@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:
