Full Outer Join With Poor Performance (Doc ID 269809.1)

Last updated on DECEMBER 02, 2010

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.4 and later   [Release: 9.2 and later ]
Information in this document applies to any platform.

Symptoms

FULL OUT JOIN WITH POOR PERFORMANCE

Query works fine with oracle 8i. user tried using full outer join feature of oracle 9i.
Query runs very slow with change in execution plan.

Oracle 8i execution plan
------------------------------------

create table t1 as
select
decode(nvl(m.ANZ_M,0), 0, an.VORNAME, m.VORNAME) vorname, nvl(m.ANREDE_SL,0) anr_m,
nvl(m.ANZ_M,0) anz_m, nvl(an.ANREDE_SL,0) anr_w, nvl(an.ANZ_W,0) anz_w
from tuerk.ttp_anrede_m m
left outer join tuerk.ttp_anrede_w an on (m.VORNAME = an.VORNAME)
union
select
decode(nvl(m.ANZ_M,0), 0, an.VORNAME, m.VORNAME) vorname, nvl(m.ANREDE_SL,0) anr_m,
nvl(m.ANZ_M,0) anz_m, nvl(an.ANREDE_SL,0) anr_w, nvl(an.ANZ_W,0) anz_w
from tuerk.ttp_anrede_m m
right outer join tuerk.ttp_anrede_w an on (m.VORNAME = an.VORNAME)
;

CREATE TABLE STATEMENT Hint=CHOOSE 14 K 71
LOAD AS SELECT
SORT UNIQUE 14 K 872 K 71 :Q74457005
UNION-ALL :Q74457004
HASH JOIN OUTER 5 K 320 K 17 :Q74457004
TABLE ACCESS FULL TTP_ANREDE_M 5 K 248 K 2 :Q74457002
TABLE ACCESS FULL TTP_ANREDE_W 9 K 124 K 14 :Q74457000
HASH JOIN OUTER 9 K 552 K 17 :Q74457004 TABLE ACCESS FULL TTP_ANREDE_W 9 K 124 K 14 :Q74457001
TABLE ACCESS FULL TTP_ANREDE_M 5 K 248 K 2 :Q74457003


Oracle 9i execution plan
-----------------------------------

create table t1 as
select
decode(nvl(m.ANZ_M,0), 0, an.VORNAME, m.VORNAME) vorname, nvl(m.ANREDE_SL,0) anr_m,
nvl(m.ANZ_M,0) anz_m, nvl(an.ANREDE_SL,0) anr_w, nvl(an.ANZ_W,0) anz_w
from tuerk.ttp_anrede_m m
full outer join tuerk.ttp_anrede_w an on (m.VORNAME = an.VORNAME)
;

CREATE TABLE STATEMENT Hint=CHOOSE 58 K 38069
LOAD AS SELECT
VIEW 58 K 6 M 38069
UNION-ALL
HASH JOIN OUTER 54 K 1 M 39
TABLE ACCESS FULL TTP_ANREDE_M 54 K 748 K 11
TABLE ACCESS FULL TTP_ANREDE_W 69 K 944 K 14
FILTER
TABLE ACCESS FULL TTP_ANREDE_W 3 K 47 K 14
TABLE ACCESS FULL TTP_ANREDE_M 1 9 11

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms