| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: left outer join
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 -------------------------------------------------------------------------=(%CPU)|=20
=20
| Id | Operation | Name | Rows | Bytes | Cost =
-------------------------------------------------------------------------=
=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 -------------------------------------------------------------------------=
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
-- 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
 |
 |