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
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
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1813927128
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 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:
- 11.2.0.2 with the same patches applied on both OHs.
- Linux x86_64
- Basically same parameters on both dbs (which is probably where I am going wrong).
