Native Full Outer Join in Oracle 10g

Native Full Outer Join is known to be a feature of Oracle 11g. What is not well known though is that it can be used in 10.2.0.3 and 10.2.0.4 as well – by applying a hint with the self descriptive name NATIVE_FULL_OUTER_JOIN. So I decided it might be worth to open my blog with it.

When I say “not well known” I mean: the only place on the web where I found it clearly stated was this excellent post on Oracle Optimizer Team Blog (it is about the outer joins generally; the “FOJ 10g” question was initiated by Christian Antognini in the comment part). To see the statement of the optimizer team, you need to scorll to the end of the post, just before the comments start, and find Question Q3.

Let’s do a quick test:

SQL> select * from v$version;

BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bi
PL/SQL Release 10.2.0.4.0 – Production
CORE    10.2.0.4.0      Production
TNS for 64-bit Windows: Version 10.2.0.4.0 – Production
NLSRTL Version 10.2.0.4.0 – Production

5 rows selected

SQL> drop table t1;

Table dropped.

SQL> drop table t2;

Table dropped.

SQL> create table t1
2  as select rownum as n1, lpad(’x',20) as v1, lpad(’x',100) as v11
3  from dual
4  connect by level <= 4800000;

Table created.

SQL> create table t2
2  as
3  select n2, v2 from (
4  select rownum as n2, lpad(’x',20) as v2, lpad(’x',100) as v22
5  from dual
6  connect by level <= 4800000;

Table created.

SQL> exec dbms_stats.gather_table_stats(user,’T1′);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(user,’T2′); 

PL/SQL procedure successfully completed.

SQL> set autotrace traceonly

SQL> set timing on

So I created 2 tables, each containing 5 Mln. rows. The expected result of the full outer join with condition “n1=n2″ is:
9.8 Mln rows = 4.8 (T1) + 4.8 (T2) + 0.2 (rows matching t1.n1=t2.n2).

–> 1. FOJ without the hint

SQL> select v1,v2
2  from
3  t1
4  full outer join
5  t2
6  on t1.n1=t2.n2;

9800000 rows selected.

Elapsed: 00:02:45.37

Execution Plan
———————————————————-
Plan hash value: 2733138327
—————————————————————————————-
| Id  | Operation               | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
—————————————————————————————-
|   0 | SELECT STATEMENT        |      |  9811K|   224M|       | 82506   (1)| 00:16:31 |
|   1 |  VIEW                   |      |  9811K|   224M|       | 82506   (1)| 00:16:31 |
|   2 |   UNION-ALL             |      |       |       |       |            |          |
|*  3 |    HASH JOIN RIGHT OUTER|      |  5003K|   252M|   181M| 43740   (1)| 00:08:45 |
|   4 |     TABLE ACCESS FULL   | T2   |  5008K|   124M|       |  4947   (2)| 00:01:00 |
|   5 |     TABLE ACCESS FULL   | T1   |  5003K|   128M|       | 20454   (1)| 00:04:06 |
|*  6 |    HASH JOIN RIGHT ANTI |      |  4808K|   146M|    85M| 38765   (1)| 00:07:46 |
|   7 |     TABLE ACCESS FULL   | T1   |  5003K|    28M|       | 20448   (1)| 00:04:06 |
|   8 |     TABLE ACCESS FULL   | T2   |  5008K|   124M|       |  4947   (2)| 00:01:00 |
—————————————————————————————-

Predicate Information (identified by operation id):
—————————————————
3 – access(”T1″.”N1″=”T2″.”N2″(+))
6 – access(”T1″.”N1″=”T2″.”N2″)

Statistics
———————————————————-
362  recursive calls
0  db block gets
645349  consistent gets
178226  physical reads
0  redo size
168561097  bytes sent via SQL*Net to client
7187155  bytes received via SQL*Net from client
653335  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)

9800000  rows processed

–> 2. FOJ with the hint

 SQL> select /*+ NATIVE_FULL_OUTER_JOIN */ v1,v2
2  from
3  t1
4  full outer join
5  t2
6  on t1.n1=t2.n2; 

9800000 rows selected.

Elapsed: 00:01:40.48

Execution Plan
———————————————————-
Plan hash value: 3807180574
——————————————————————————————
| Id  | Operation             | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
——————————————————————————————
|   0 | SELECT STATEMENT      |          |  5008K|   114M|       | 43740   (1)| 00:08:45 |
|   1 |  VIEW                 | VW_FOJ_0 |  5008K|   114M|       | 43740   (1)| 00:08:45 |
|*  2 |   HASH JOIN FULL OUTER|          |  5008K|   253M|   181M| 43740   (1)| 00:08:45 |
|   3 |    TABLE ACCESS FULL  | T2       |  5008K|   124M|       |  4947   (2)| 00:01:00 |
|   4 |    TABLE ACCESS FULL  | T1       |  5003K|   128M|       | 20454   (1)| 00:04:06 |
——————————————————————————————

Predicate Information (identified by operation id):
—————————————————
2 – access(”T1″.”N1″=”T2″.”N2″)

Statistics
———————————————————-
197  recursive calls
0  db block gets
401210  consistent gets
86361  physical reads
0  redo size
168561603  bytes sent via SQL*Net to client
7187155  bytes received via SQL*Net from client
653335  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)

9800000  rows processed

A quick comparrison shows:

  • Elapsed time: 00:01:40.48 (FOJ) vs. 00:02:45.37 (without FOJ)
  • consistent gets: 401210 vs. 645349
  • physical reads: 86361 vs. 178226
  • Advertisements

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s