This is a short note about a limitation in complex view merging for outer joins.
We start with two simple tables, t1 and t2. To show the effect we don’t even need to load any data.
1 2 3 4 5 6 7 8 9 10 | create table t1 ( id1 number not null , vc1 varchar2(200));create table t2 ( id1 number not null , id2 number not null , num1 number); |
I know, I said it’s about outer joins but let’s first check the execution plan for the INNER JOIN.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | explain plan forselect *from t1 inner join ( select /*+ merge */ id2 , 0 x , sum(num1) sum_num1 from t2 group by id2 ) s1 on (s1.id2 = t1.id1);select * from dbms_xplan.display();----------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 153 | 5 (20)| 00:00:01 || 1 | HASH GROUP BY | | 1 | 153 | 5 (20)| 00:00:01 ||* 2 | HASH JOIN | | 1 | 153 | 4 (0)| 00:00:01 || 3 | TABLE ACCESS FULL| T1 | 1 | 127 | 2 (0)| 00:00:01 || 4 | TABLE ACCESS FULL| T2 | 1 | 26 | 2 (0)| 00:00:01 |---------------------------------------------------------------------------- |
Oracle merges the inline view as instructed by the MERGE hint. Btw., I’m only using the hint for demonstration purposes.
What happens if we change the join to a LEFT OUTER JOIN?
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | explain plan forselect *from t1 left outer join ( select /*+ merge */ id2 , 0 x , sum(num1) sum_num1 from t2 group by id2 ) s1 on (s1.id2 = t1.id1);select * from dbms_xplan.display();-----------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 143 | 5 (20)| 00:00:01 ||* 1 | HASH JOIN OUTER | | 1 | 143 | 5 (20)| 00:00:01 || 2 | TABLE ACCESS FULL | T1 | 1 | 115 | 2 (0)| 00:00:01 || 3 | VIEW | | 1 | 28 | 3 (34)| 00:00:01 || 4 | HASH GROUP BY | | 1 | 26 | 3 (34)| 00:00:01 || 5 | TABLE ACCESS FULL| T2 | 1 | 26 | 2 (0)| 00:00:01 |----------------------------------------------------------------------------- |
The inline view is not merged anymore. The optimizer trace reveals why it cannot merge the view anymore:
1 2 3 4 5 | CVM: Checking validity of merging in query block SEL$2 (#2)CVM: CVM bypassed: View on right side of outer join contains view with illegal column.CVM: CVM bypassed: Externally referenced expressions are not merge-safe.CVM: CVM bypassed: view on right side of Outer Join + MuLTiple TABle.CVM: CVM bypassed: view on right side of Outer Join + MuLTiple TABle. |
In case you haven’t noticed, there’s this little expression in the projection of the inner SELECT on line 7 (“0 x”). As soon as we remove it, the view will be merged by the optimizer also for LEFT OUTER JOIN.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | explain plan forselect *from t1 left outer join ( select /*+ merge */ id2-- , 0 x , sum(num1) sum_num1 from t2 group by id2 ) s1 on (s1.id2 = t1.id1);select * from dbms_xplan.display();----------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 153 | 5 (20)| 00:00:01 || 1 | HASH GROUP BY | | 1 | 153 | 5 (20)| 00:00:01 ||* 2 | HASH JOIN OUTER | | 1 | 153 | 4 (0)| 00:00:01 || 3 | TABLE ACCESS FULL| T1 | 1 | 127 | 2 (0)| 00:00:01 || 4 | TABLE ACCESS FULL| T2 | 1 | 26 | 2 (0)| 00:00:01 |---------------------------------------------------------------------------- |
Thanks to point and click tools *cough* Cognos *cough* I’ve seen this a lot lately 
Footnote: tests run on 12.2.0.1