Oracle Doc Question

From: Daniel Roy <danielroy10junk_at_hotmail.com>
Date: 26 Dec 2003 08:08:10 -0800
Message-ID: <3722db.0312260808.42429fcd@posting.google.com>

Guys,

    I was going through the performance tuning manual for 9.2, for the chapter about the hints. Here's what they say about the MERGE hint:

<Quote>

MERGE
The MERGE hint lets you merge a view for each query.

If a view's query contains a GROUP BY clause or DISTINCT operator in the SELECT list, then the optimizer can merge the view's query into the accessing statement only if complex view merging is enabled. Complex merging can also be used to merge an IN subquery into the accessing statement if the subquery is uncorrelated.

Complex merging is not cost-based; that is, the accessing query block must include the MERGE hint. Without this hint, the optimizer uses another approach.

For example:
SELECT /*+MERGE(v)*/ e1.last_name, e1.salary, v.avg_salary FROM employees e1,
  (SELECT department_id, avg(salary) avg_salary    FROM employees e2
   GROUP BY department_id) v
WHERE e1.department_id = v.department_id AND e1.salary > v.avg_salary;

---

Note:
This example requires that complex view merging be enabled.  

<End of quote>

No matter what I try, I can't merge the inline view into the outer view. That seems to make sense, since I can't think of a way that a view with a GROUP BY could possible be "mergeable" into another outer query. I tried with complex view merging both enabled and disabled (by playing with "optimizer_features_enable"), and tried also with hints. Can someone have this working the way Oracle says it should, or confirm my suspicions that it's simply impossible to merge that inner view into the outer.

Thanx

Daniel Received on Fri Dec 26 2003 - 10:08:10 CST