left outer join
- From: Sai Selvaganesan <ssaisundar@xxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 20 Aug 2004 11:56:27 -0700 (PDT)
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
-----------------------------------------------------------------
Other related posts:
