My Oracle Support Banner

Full Outer Join With Poor Performance (Doc ID 269809.1)

Last updated on FEBRUARY 15, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.4 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A 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 <Schema>.<table_name> m
left outer join <Schema>.<table_name> 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 <Schema>.<table_name> m
right outer join <schema>.<table_name> 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 <Table_name> 5 K 248 K 2 :Q74457002
TABLE ACCESS FULL <Table_Name> 9 K 124 K 14 :Q74457000
HASH JOIN OUTER 9 K 552 K 17 :Q74457004 TABLE ACCESS FULL <Table_name> 9 K 124 K 14 :Q74457001
TABLE ACCESS FULL <Table_name> 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 <Schema>.<Table_name> 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 <Table_name> 54 K 748 K 11
TABLE ACCESS FULL <Table_name> 69 K 944 K 14
FILTER
TABLE ACCESS FULL <Table_name> 3 K 47 K 14
TABLE ACCESS FULL <Table_name> 1 9 11

Changes

 

Cause

To view full details, 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 a vibrant support community of peers and Oracle experts.