This is yet another post inspired by a problem on the ODC https://community.oracle.com/message/14839371#14839371. The OP hasn’t mentioned any performance problem, but this is a classic recipe for issues. We want to left join to another table but only when a condition against our driving table has been met.
Let’s copy the table from the post and demo the conditional outer join scenario (I’ve changed the data types to remove a few other quirks):
drop table oe_order_headers_a;
create table oe_order_headers_a
(header_id number not null
,order_number number not null
,order_source_id number
,attribute20 number
);
insert into oe_order_headers_a(header_id, order_number, order_source_id, attribute20) values (1411757, 10000103, null, null );
insert into oe_order_headers_a(header_id, order_number, order_source_id, attribute20) values (1411758, 50000408, 1021, 1411757);
insert into oe_order_headers_a(header_id, order_number, order_source_id, attribute20) values (1411759, 50000409, 2, 1411757);
insert into oe_order_headers_a(header_id, order_number, order_source_id, attribute20) values (1411760, 50000410, null, null);
insert into oe_order_headers_a(header_id, order_number, order_source_id, attribute20) values (1411761, 10000105, 2, 1411761);
insert into oe_order_headers_a(header_id, order_number, order_source_id, attribute20) values (1411762, 50000411, 1021, 1411762);
insert into oe_order_headers_a(header_id, order_number, order_source_id, attribute20) values (1411763, 50000412, 12, 1411763);
select oh.order_number
,oh.order_source_id
,oh2.order_number parent_order_number
from oe_order_headers_a oh
left join oe_order_headers_a oh2
on (oh.order_source_id = 1021 and oh.attribute20 = oh2.header_id)
where oh.order_number between 50000000 and 60000000;
ORDER_NUMBER ORDER_SOURCE_ID PARENT_ORDER_NUMBER
------------ --------------- -------------------
50000408 1021 10000103
50000411 1021 50000411
50000412 12
50000409 2
50000410
And if we look at the explain plan and some stats (of a second execution) via SQL*Plus autotrace, we get:
ANDY@pdb1>set autotrace on
ANDY@pdb1>/
Execution Plan
----------------------------------------------------------
Plan hash value: 2432841757
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 325 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 5 | 325 | 7 (15)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| OE_ORDER_HEADERS_A | 5 | 195 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| OE_ORDER_HEADERS_A | 7 | 182 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OH"."ATTRIBUTE20"="OH2"."HEADER_ID"(+) AND
"OH"."ORDER_SOURCE_ID"=CASE WHEN ("OH2"."HEADER_ID"(+) IS NOT NULL) THEN 1021
ELSE 1021 END )
2 - filter("OH"."ORDER_NUMBER">=50000000 AND "OH"."ORDER_NUMBER"<=60000000)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
825 bytes sent via SQL*Net to client
572 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5 rows processed
The plan looks ok, we’re doing a full scan of the same table twice but in reality we would probably have two different tables here. If you take a look at the predicates section, you’ll notice that Uncle Oracle has done something very clever, it’s transformed our conditional outer join condition (the oh.order_source_id = 1021 bit) into another equality join condition:
"OH"."ORDER_SOURCE_ID"=CASE WHEN ("OH2"."HEADER_ID"(+) IS NOT NULL) THEN 1021
ELSE 1021 END )
This is what allows us to do the hash join, you’re not allowed to do a hash join without equality conditions – after all, you have to hash some expression and compare that to a hashed expression from the other side.
Let’s take it a step further, what about if we wanted to also outer join to our other table when our order_source_id is 1022. We would instinctively just change our equality condition into an IN condition, right? Well, here’s what happens:
ANDY@pdb1>select oh.order_number
2 ,oh.order_source_id
3 ,oh2.order_number parent_order_number
4 from oe_order_headers_a oh
5 left join oe_order_headers_a oh2
6 on (oh.order_source_id in (1021,1022) and oh.attribute20 = oh2.header_id)
7 where oh.order_number between 50000000 and 60000000
8 /
ORDER_NUMBER ORDER_SOURCE_ID PARENT_ORDER_NUMBER
------------ --------------- -------------------
50000408 1021 10000103
50000409 2
50000410
50000411 1021 50000411
50000412 12
Execution Plan
----------------------------------------------------------
Plan hash value: 1726314521
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 260 | 18 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 5 | 260 | 18 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | OE_ORDER_HEADERS_A | 5 | 195 | 3 (0)| 00:00:01 |
| 3 | VIEW | | 1 | 13 | 3 (0)| 00:00:01 |
|* 4 | FILTER | | | | | |
|* 5 | TABLE ACCESS FULL| OE_ORDER_HEADERS_A | 1 | 26 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OH"."ORDER_NUMBER">=50000000 AND "OH"."ORDER_NUMBER"<=60000000)
4 - filter("OH"."ORDER_SOURCE_ID"=1021 OR "OH"."ORDER_SOURCE_ID"=1022)
5 - filter("OH"."ATTRIBUTE20"="OH2"."HEADER_ID")
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
23 consistent gets
0 physical reads
0 redo size
826 bytes sent via SQL*Net to client
572 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5 rows processed
We are now doing a nested loop to that full tablescan of our outer table! Taking a look at the predicates we can see that Uncle Oracle is no longer working his magic for us: we are doing a filter predicate on line 4 followed by the join predicate on line 5. On the bright side, we are only doing that full table scan once per row in our driving table that matches our conditional join predicate but this is 2 times as many as we really need to.
I’ll note that if our main condition was on something that could use an index and was fine running via nested loop then we wouldn’t have a problem.
How do we address this? We do the work for the optimizer, we transform that conditional join predicate into an equality join predicate.
If we tried to write this how Oracle rewrote the first query internally, it’s easy to see why it wasn’t able this time: it’s difficult! The best I could come up with following the pattern was:
select oh.order_number
,oh.order_source_id
,oh2.order_number parent_order_number
from oe_order_headers_a oh
,oe_order_headers_a oh2
where oh.attribute20 = oh2.header_id (+)
and oh.order_source_id in (case when (oh2.header_id(+) is not null) then 1021 else 1021 end , case when (oh2.header_id(+) is not null) then 1022 else 1022 end)
and oh.order_number between 50000000 and 60000000;
ERROR at line 7:
ORA-01719: outer join operator (+) not allowed in operand of OR or IN
This doesn’t even solve our equality requirement, we would have to expand the IN into two union all branches… it’s just not worth it.
Luckily, there is a more obvious transformation which is to do the transformation on the left side of the join. I would naturally write it like this:
select oh.order_number
,oh.order_source_id
,oh2.order_number parent_order_number
from oe_order_headers_a oh
left join oe_order_headers_a oh2
on (case when oh.order_source_id in (1021,1022) then oh.attribute20 else null end = oh2.header_id)
where oh.order_number between 50000000 and 60000000;
Execution Plan
----------------------------------------------------------
Plan hash value: 2432841757
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 325 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 5 | 325 | 7 (15)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| OE_ORDER_HEADERS_A | 5 | 195 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| OE_ORDER_HEADERS_A | 7 | 182 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OH2"."HEADER_ID"(+)=CASE "OH"."ORDER_SOURCE_ID" WHEN 1021 THEN
"OH"."ATTRIBUTE20" WHEN 1022 THEN "OH"."ATTRIBUTE20" ELSE NULL END )
2 - filter("OH"."ORDER_NUMBER">=50000000 AND "OH"."ORDER_NUMBER"<=60000000)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
825 bytes sent via SQL*Net to client
572 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5 rows processed
Back to the standard performance of our hash join from before, bingo!
CTAndrewSayer 