This is a short note about a limitation in complex view merging for outer joins.<\/p>\n
We start with two simple tables, t1 and t2. To show the effect we don’t even need to load any data.<\/p>\n
\r\ncreate table t1 (\r\n id1 number not null\r\n , vc1 varchar2(200)\r\n);\r\n\r\ncreate table t2 (\r\n id1 number not null\r\n , id2 number not null\r\n , num1 number\r\n);\r\n<\/pre>\nI know, I said it’s about outer joins but let’s first check the execution plan for the INNER JOIN.<\/p>\n
\r\nexplain plan for\r\nselect *\r\nfrom t1\r\n inner join (\r\n select \/*+ merge *\/\r\n id2\r\n , 0 x\r\n , sum(num1) sum_num1\r\n from t2\r\n group by id2\r\n ) s1 on (s1.id2 = t1.id1)\r\n;\r\n\r\nselect * from dbms_xplan.display();\r\n\r\n----------------------------------------------------------------------------\r\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |\r\n----------------------------------------------------------------------------\r\n| 0 | SELECT STATEMENT | | 1 | 153 | 5 (20)| 00:00:01 |\r\n| 1 | HASH GROUP BY | | 1 | 153 | 5 (20)| 00:00:01 |\r\n|* 2 | HASH JOIN | | 1 | 153 | 4 (0)| 00:00:01 |\r\n| 3 | TABLE ACCESS FULL| T1 | 1 | 127 | 2 (0)| 00:00:01 |\r\n| 4 | TABLE ACCESS FULL| T2 | 1 | 26 | 2 (0)| 00:00:01 |\r\n----------------------------------------------------------------------------\r\n<\/pre>\nOracle merges the inline view as instructed by the MERGE hint. Btw., I’m only using the hint for demonstration purposes.<\/p>\n
What happens if we change the join to a LEFT OUTER JOIN?<\/p>\n
\r\nexplain plan for\r\nselect *\r\nfrom t1\r\n left outer join (\r\n select \/*+ merge *\/\r\n id2\r\n , 0 x\r\n , sum(num1) sum_num1\r\n from t2\r\n group by id2\r\n ) s1 on (s1.id2 = t1.id1)\r\n;\r\nselect * from dbms_xplan.display();\r\n\r\n-----------------------------------------------------------------------------\r\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |\r\n-----------------------------------------------------------------------------\r\n| 0 | SELECT STATEMENT | | 1 | 143 | 5 (20)| 00:00:01 |\r\n|* 1 | HASH JOIN OUTER | | 1 | 143 | 5 (20)| 00:00:01 |\r\n| 2 | TABLE ACCESS FULL | T1 | 1 | 115 | 2 (0)| 00:00:01 |\r\n| 3 | VIEW | | 1 | 28 | 3 (34)| 00:00:01 |\r\n| 4 | HASH GROUP BY | | 1 | 26 | 3 (34)| 00:00:01 |\r\n| 5 | TABLE ACCESS FULL| T2 | 1 | 26 | 2 (0)| 00:00:01 |\r\n-----------------------------------------------------------------------------\r\n<\/pre>\nThe inline view is not merged anymore. The optimizer trace reveals why it cannot merge the view anymore:<\/p>\n
\r\nCVM: Checking validity of merging in query block SEL$2 (#2)\r\nCVM: CVM bypassed: View on right side of outer join contains view with illegal column.\r\nCVM: CVM bypassed: Externally referenced expressions are not merge-safe.\r\nCVM: CVM bypassed: view on right side of Outer Join + MuLTiple TABle.\r\nCVM: CVM bypassed: view on right side of Outer Join + MuLTiple TABle.\r\n<\/pre>\nIn 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.<\/p>\n
\r\nexplain plan for\r\nselect *\r\nfrom t1\r\n left outer join (\r\n select \/*+ merge *\/\r\n id2\r\n-- , 0 x\r\n , sum(num1) sum_num1\r\n from t2\r\n group by id2\r\n ) s1 on (s1.id2 = t1.id1)\r\n;\r\nselect * from dbms_xplan.display();\r\n\r\n----------------------------------------------------------------------------\r\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |\r\n----------------------------------------------------------------------------\r\n| 0 | SELECT STATEMENT | | 1 | 153 | 5 (20)| 00:00:01 |\r\n| 1 | HASH GROUP BY | | 1 | 153 | 5 (20)| 00:00:01 |\r\n|* 2 | HASH JOIN OUTER | | 1 | 153 | 4 (0)| 00:00:01 |\r\n| 3 | TABLE ACCESS FULL| T1 | 1 | 127 | 2 (0)| 00:00:01 |\r\n| 4 | TABLE ACCESS FULL| T2 | 1 | 26 | 2 (0)| 00:00:01 |\r\n----------------------------------------------------------------------------\r\n<\/pre>\nThanks to point and click tools *cough* Cognos *cough* I’ve seen this a lot lately \ud83d\ude09<\/p>\n
Footnote: tests run on 12.2.0.1<\/p>\n","protected":false},"excerpt":{"rendered":"
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. create table t1 ( id1 number not null , vc1 varchar2(200) ); create table t2 ( id1 number not […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3,13],"tags":[],"class_list":["post-320","post","type-post","status-publish","format-standard","hentry","category-internals","category-things-that-happen"],"_links":{"self":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts\/320","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=320"}],"version-history":[{"count":3,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts\/320\/revisions"}],"predecessor-version":[{"id":323,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts\/320\/revisions\/323"}],"wp:attachment":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=320"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=320"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=320"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}