{"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>\nSolution #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>\nSQL 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>\nSolution #2:
    \nAdd hint in the SELECT clause<\/p>\nSQL 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>\nor <\/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>\nDownload 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\n\n\n\n\n\n\n\n\n\n\n\n\t\n