Skip to content
No results
We use cookies to ensure that we give you the best experience on our website.

# 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