{"id":1472,"date":"2012-07-09T21:50:33","date_gmt":"2012-07-10T02:50:33","guid":{"rendered":"http:\/\/www.dpriver.com\/blog\/?page_id=1472"},"modified":"2012-07-10T01:51:12","modified_gmt":"2012-07-10T06:51:12","slug":"oracle-sql-query-rewrite-separate-outer-joined-inline-view-using-union-all-or-add-hint-for-the-inline-view","status":"publish","type":"page","link":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/oracle-sql-query-rewrite\/oracle-sql-query-rewrite-separate-outer-joined-inline-view-using-union-all-or-add-hint-for-the-inline-view\/","title":{"rendered":"Oracle SQL Query rewrite: Separate outer joined inline view using UNION ALL or add hint for the inline view"},"content":{"rendered":"

Separate outer joined inline view using UNION ALL or add hint for the inline view.<\/p>\n

This is an article of Oracle SQL Query rewrite serials<\/a>.<\/p>\n

If outer joined inline view has more than 2 tables in the FROM clause, It has to read all the data in the view, because the view is non-mergeable view due to outer join. So, we have to re-write the statement to remove outer join using UNION ALL.<\/p>\n

Take this SQL for example:<\/p>\n

\r\nSELECT a.emp_id, a.emp_name,\r\n       b.salary_amt\r\nFROM   emp a, \r\n      (SELECT emp_id, SUM(salary) salary_amt\r\n       FROM   salary a, dept b  -- two tables joined\r\n       WHERE  a.dept_id = b.dept_id\r\n       AND    b.location = 'US'\r\n       GROUP  BY emp_id\r\n       ) b\r\nWHERE  emp.date_entered >= to_date('20080101','yyyymmdd')\r\nAND    a.emp_id          = b.emp_id (+);\r\n<\/pre>\n
Solution #1:
\nOuter join means that \u2018EXISTS\u2019 or \u2018NOT EXISTS\u2019. \u2018EXISTS\u2019 means equi-join, so we removed outer symbol (+) to make it equi-join and added NOT EXISTS sub-query to the equi-join using UNION ALL.<\/p>\n
SQL will be translated into:<\/p>\n
\r\nSELECT a.emp_id,\r\n       a.emp_name,\r\n       b.salary_amt\r\nFROM   emp a,\r\n       (SELECT emp_id,\r\n               SUM(salary) salary_amt\r\n        FROM   salary a,\r\n               dept b\r\n        WHERE  a.dept_id = b.dept_id\r\n               AND b.location = 'US'\r\n        GROUP  BY emp_id) b\r\nWHERE  emp.date_entered >= To_date('20080101', 'yyyymmdd')\r\n       AND a.emp_id = b.emp_id\r\nUNION ALL\r\nSELECT a.emp_id,\r\n       a.emp_name,\r\n       NULL salary_amt\r\nFROM   emp a\r\nWHERE  emp.date_entered >= To_date('20080101', 'yyyymmdd')\r\n       AND NOT EXISTS (SELECT 1\r\n                       FROM   salary epi001,\r\n                              dept b\r\n                       WHERE  a.dept_id = b.dept_id\r\n                              AND b.location = 'US'\r\n                              AND a.emp_id = epi001.emp_id) \r\n\r\n<\/pre>\n
Solution #2:
\nAdd hint in the SELECT clause<\/p>\n
SQL will be translated into<\/p>\n
\r\nSELECT \/*+ NO_MERGE(b)  USE_HASH(b) *\/ a.emp_id,\r\n                                       a.emp_name,\r\n                                       b.salary_amt\r\nFROM   emp a,\r\n       (SELECT emp_id,\r\n               SUM(salary) salary_amt\r\n        FROM   salary a,\r\n               dept b\r\n        WHERE  a.dept_id = b.dept_id\r\n               AND b.location = 'US'\r\n        GROUP  BY emp_id) b\r\nWHERE  emp.date_entered >= To_date('20080101', 'yyyymmdd')\r\n       AND a.emp_id = b.emp_id (+) \r\n<\/pre>\n
or <\/p>\n
\r\nSELECT \/*+ PUSH_PRED(b) *\/ a.emp_id,\r\n                           a.emp_name,\r\n                           b.salary_amt\r\nFROM   emp a,\r\n       (SELECT emp_id,\r\n               SUM(salary) salary_amt\r\n        FROM   salary a,\r\n               dept b\r\n        WHERE  a.dept_id = b.dept_id\r\n               AND b.location = 'US'\r\n        GROUP  BY emp_id) b\r\nWHERE  emp.date_entered >= To_date('20080101', 'yyyymmdd')\r\n       AND a.emp_id = b.emp_id (+) \r\n<\/pre>\n
Download this demo: C# version<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
Separate outer joined inline view using UNION ALL or add hint for the inline view. This is an article of Oracle SQL Query rewrite serials. If outer joined inline view has more than 2 tables in the FROM clause, It has to read all the data in the view, because the view is non-mergeable view due to outer join. So, we have to re-write the statement to remove outer join using UNION ALL. Take this SQL for example: SELECT a.emp_id, a.emp_name, b.salary_amt FROM emp a, (SELECT emp_id, SUM(salary) salary_amt FROM salary a, dept b — two tables joined WHERE a.dept_id\u2026<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":1445,"menu_order":0,"comment_status":"closed","ping_status":"open","template":"gsp_feature_page_tt.php","meta":[],"blocksy_meta":{"styles_descriptor":{"styles":{"desktop":"","tablet":"","mobile":""},"google_fonts":[],"version":5}},"yoast_head":"\nHelp you to make better use of General SQL Parser<\/title>\n<meta name=\"description\" content=\"Oracle SQL Query rewrite: Separate outer joined inline view using UNION ALL or add hint for the inline view\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/oracle-sql-query-rewrite\/oracle-sql-query-rewrite-separate-outer-joined-inline-view-using-union-all-or-add-hint-for-the-inline-view\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Help you to make better use of General SQL Parser\" \/>\n<meta property=\"og:description\" content=\"Oracle SQL Query rewrite: Separate outer joined inline view using UNION ALL or add hint for the inline view\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/oracle-sql-query-rewrite\/oracle-sql-query-rewrite-separate-outer-joined-inline-view-using-union-all-or-add-hint-for-the-inline-view\/\" \/>\n<meta property=\"og:site_name\" content=\"SQL and Data Blog\" \/>\n<meta property=\"article:modified_time\" content=\"2012-07-10T06:51:12+00:00\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#organization\",\"name\":\"SQL and Data Blog\",\"url\":\"https:\/\/www.dpriver.com\/blog\/\",\"sameAs\":[],\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png\",\"contentUrl\":\"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png\",\"width\":251,\"height\":72,\"caption\":\"SQL and Data Blog\"},\"image\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/\"}},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#website\",\"url\":\"https:\/\/www.dpriver.com\/blog\/\",\"name\":\"SQL and Data Blog\",\"description\":\"SQL related blog for database professional\",\"publisher\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dpriver.com\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/oracle-sql-query-rewrite\/oracle-sql-query-rewrite-separate-outer-joined-inline-view-using-union-all-or-add-hint-for-the-inline-view\/\",\"url\":\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/oracle-sql-query-rewrite\/oracle-sql-query-rewrite-separate-outer-joined-inline-view-using-union-all-or-add-hint-for-the-inline-view\/\",\"name\":\"Help you to make better use of General SQL Parser\",\"isPartOf\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/#website\"},\"datePublished\":\"2012-07-10T02:50:33+00:00\",\"dateModified\":\"2012-07-10T06:51:12+00:00\",\"description\":\"Oracle SQL Query rewrite: Separate outer joined inline view using UNION ALL or add hint for the inline view\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/oracle-sql-query-rewrite\/oracle-sql-query-rewrite-separate-outer-joined-inline-view-using-union-all-or-add-hint-for-the-inline-view\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/oracle-sql-query-rewrite\/oracle-sql-query-rewrite-separate-outer-joined-inline-view-using-union-all-or-add-hint-for-the-inline-view\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/oracle-sql-query-rewrite\/oracle-sql-query-rewrite-separate-outer-joined-inline-view-using-union-all-or-add-hint-for-the-inline-view\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.dpriver.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"List of demos illustrate how to use general sql parser\",\"item\":\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Oracle SQL Query rewrite\",\"item\":\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/oracle-sql-query-rewrite\/\"},{\"@type\":\"ListItem\",\"position\":4,\"name\":\"Oracle SQL Query rewrite: Separate outer joined inline view using UNION ALL or add hint for the inline view\"}]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Help you to make better use of General SQL Parser","description":"Oracle SQL Query rewrite: Separate outer joined inline view using UNION ALL or add hint for the inline view","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/oracle-sql-query-rewrite\/oracle-sql-query-rewrite-separate-outer-joined-inline-view-using-union-all-or-add-hint-for-the-inline-view\/","og_locale":"en_US","og_type":"article","og_title":"Help you to make better use of General SQL Parser","og_description":"Oracle SQL Query rewrite: Separate outer joined inline view using UNION ALL or add hint for the inline view","og_url":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/oracle-sql-query-rewrite\/oracle-sql-query-rewrite-separate-outer-joined-inline-view-using-union-all-or-add-hint-for-the-inline-view\/","og_site_name":"SQL and Data Blog","article_modified_time":"2012-07-10T06:51:12+00:00","twitter_card":"summary_large_image","twitter_misc":{"Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Organization","@id":"https:\/\/www.dpriver.com\/blog\/#organization","name":"SQL and Data Blog","url":"https:\/\/www.dpriver.com\/blog\/","sameAs":[],"logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png","contentUrl":"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png","width":251,"height":72,"caption":"SQL and Data Blog"},"image":{"@id":"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/"}},{"@type":"WebSite","@id":"https:\/\/www.dpriver.com\/blog\/#website","url":"https:\/\/www.dpriver.com\/blog\/","name":"SQL and Data Blog","description":"SQL related blog for database professional","publisher":{"@id":"https:\/\/www.dpriver.com\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dpriver.com\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/oracle-sql-query-rewrite\/oracle-sql-query-rewrite-separate-outer-joined-inline-view-using-union-all-or-add-hint-for-the-inline-view\/","url":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/oracle-sql-query-rewrite\/oracle-sql-query-rewrite-separate-outer-joined-inline-view-using-union-all-or-add-hint-for-the-inline-view\/","name":"Help you to make better use of General SQL Parser","isPartOf":{"@id":"https:\/\/www.dpriver.com\/blog\/#website"},"datePublished":"2012-07-10T02:50:33+00:00","dateModified":"2012-07-10T06:51:12+00:00","description":"Oracle SQL Query rewrite: Separate outer joined inline view using UNION ALL or add hint for the inline view","breadcrumb":{"@id":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/oracle-sql-query-rewrite\/oracle-sql-query-rewrite-separate-outer-joined-inline-view-using-union-all-or-add-hint-for-the-inline-view\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/oracle-sql-query-rewrite\/oracle-sql-query-rewrite-separate-outer-joined-inline-view-using-union-all-or-add-hint-for-the-inline-view\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/oracle-sql-query-rewrite\/oracle-sql-query-rewrite-separate-outer-joined-inline-view-using-union-all-or-add-hint-for-the-inline-view\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.dpriver.com\/blog\/"},{"@type":"ListItem","position":2,"name":"List of demos illustrate how to use general sql parser","item":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/"},{"@type":"ListItem","position":3,"name":"Oracle SQL Query rewrite","item":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/oracle-sql-query-rewrite\/"},{"@type":"ListItem","position":4,"name":"Oracle SQL Query rewrite: Separate outer joined inline view using UNION ALL or add hint for the inline view"}]}]}},"_links":{"self":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/pages\/1472"}],"collection":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/comments?post=1472"}],"version-history":[{"count":4,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/pages\/1472\/revisions"}],"predecessor-version":[{"id":1502,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/pages\/1472\/revisions\/1502"}],"up":[{"embeddable":true,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/pages\/1445"}],"wp:attachment":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/media?parent=1472"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}