Oracle SQL Query rewrite: Separate outer joined inline view using UNION ALL or add hint for the inline view

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 = b.dept_id
       AND    b.location = 'US'
       GROUP  BY emp_id
       ) b
WHERE  emp.date_entered >= to_date('20080101','yyyymmdd')
AND    a.emp_id          = b.emp_id (+);

Solution #1:
Outer join means that ‘EXISTS’ or ‘NOT EXISTS’. ‘EXISTS’ 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.

SQL will be translated into:

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
        WHERE  a.dept_id = b.dept_id
               AND b.location = 'US'
        GROUP  BY emp_id) b
WHERE  emp.date_entered >= To_date('20080101', 'yyyymmdd')
       AND a.emp_id = b.emp_id
UNION ALL
SELECT a.emp_id,
       a.emp_name,
       NULL salary_amt
FROM   emp a
WHERE  emp.date_entered >= To_date('20080101', 'yyyymmdd')
       AND NOT EXISTS (SELECT 1
                       FROM   salary epi001,
                              dept b
                       WHERE  a.dept_id = b.dept_id
                              AND b.location = 'US'
                              AND a.emp_id = epi001.emp_id) 

Solution #2:
Add hint in the SELECT clause

SQL will be translated into

SELECT /*+ NO_MERGE(b)  USE_HASH(b) */ a.emp_id,
                                       a.emp_name,
                                       b.salary_amt
FROM   emp a,
       (SELECT emp_id,
               SUM(salary) salary_amt
        FROM   salary a,
               dept b
        WHERE  a.dept_id = b.dept_id
               AND b.location = 'US'
        GROUP  BY emp_id) b
WHERE  emp.date_entered >= To_date('20080101', 'yyyymmdd')
       AND a.emp_id = b.emp_id (+) 

or

SELECT /*+ PUSH_PRED(b) */ a.emp_id,
                           a.emp_name,
                           b.salary_amt
FROM   emp a,
       (SELECT emp_id,
               SUM(salary) salary_amt
        FROM   salary a,
               dept b
        WHERE  a.dept_id = b.dept_id
               AND b.location = 'US'
        GROUP  BY emp_id) b
WHERE  emp.date_entered >= To_date('20080101', 'yyyymmdd')
       AND a.emp_id = b.emp_id (+) 

Download this demo: C# version