So What Co-operative: May 2012

I have two simple tables that I want to join in an outer join:

SQL> @bug32052.sql
SQL> set feedback on
SQL>
SQL> drop table jh1;

Table dropped.

SQL> create table jh1 (x number(10));

Table created.

SQL>
SQL> drop table jh2;

Table dropped.

SQL> create table jh2 (x number(10));

Table created.

SQL>
SQL> insert into jh1 values (1);

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> select count(*) from jh1;

COUNT(*)
----------
1

1 row selected.

SQL>
SQL> select count(*) from jh2;

COUNT(*)
----------
0

1 row selected.

SQL>

I write a simple OUTER JOIN query...

SQL> select jh1.x, jh2.x from jh1

2 full outer join jh2 on jh2.x = jh1.x;

...and I expect to get a "1" from jh1 and a null value from jh2:

X X

---------- ----------

1 row selected.

SQL>

Perfect, that's what I am expecting. Except in one of my dbs, I get this:

SQL> select jh1.x, jh2.x from jh1

2 full outer join jh2 on jh2.x = jh1.x;

no rows selected

SQL>

Hmm, interesting. After a little detective work, I find on one database my execution plan is:

SQL> select jh1.x, jh2.x from jh1

2 full outer join jh2 on jh2.x = jh1.x;

X X

---------- ----------

1 row selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 1813927128

----------------------------------------------------------------------------------

----------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 26 | 5 (20)| 00:00:01 |

| 1 | VIEW | VW_FOJ_0 | 1 | 26 | 5 (20)| 00:00:01 |

|* 2 | HASH JOIN FULL OUTER| | 1 | 26 | 5 (20)| 00:00:01 |

| 3 | TABLE ACCESS FULL | JH2 | | | 2 (0)| 00:00:01 |

| 4 | TABLE ACCESS FULL | JH1 | 1 | 13 | 2 (0)| 00:00:01 |

----------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("JH2"."X"="JH1"."X")

Note

-----

- dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

10 recursive calls

0 db block gets

17 consistent gets

0 physical reads

0 redo size

391 bytes sent via SQL*Net to client

359 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

and on the db where the query comes back with no rows, it's:

SQL> select jh1.x, jh2.x from jh1

2 full outer join jh2 on jh2.x = jh1.x;

no rows selected

Execution Plan

----------------------------------------------------------

Plan hash value: 2793306587

-----------------------------------------

| Id | Operation | Name |

-----------------------------------------

| 0 | SELECT STATEMENT | |

| 1 | VIEW | VW_FOJ_0 |

| 2 | MERGE JOIN | |

| 3 | SORT JOIN | |

| 4 | TABLE ACCESS FULL| JH2 |

|* 5 | SORT JOIN | |

| 6 | TABLE ACCESS FULL| JH1 |

-----------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

5 - access("JH2"."X"="JH1"."X")

filter("JH2"."X"="JH1"."X")

Note

-----

- rule based optimizer used (consider using cbo)

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

3 consistent gets

0 physical reads

0 redo size

298 bytes sent via SQL*Net to client

353 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

0 rows processed

So it looks like we have an issue with the Rule Based Optimizer. I confirm the issue by passing the /*+ rule */ hint on both databases and get the wrong results. I then use the /*+ dynamic_sampling=7 */ hint on both and get the correct results.

While I know I can get around the issue by setting optimizer_dynamic_sampling=7 in my init.ora file, I am reluctant because I don't know what other queries it will mess up. I have an SR with Oracle to try and explain why it works one way on one db and one way on the other.

Environment: