Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle Doc Question

Re: Oracle Doc Question

From: Sybrand Bakker <gooiditweg_at_sybrandb.demon.nl>
Date: Sat, 27 Dec 2003 00:43:14 +0100
Message-ID: <5nhpuvk9amkkutbung0gn8svtdoc454265@4ax.com>


On 26 Dec 2003 08:08:10 -0800, danielroy10junk_at_hotmail.com (Daniel Roy) wrote:

>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

I have the distinct feeling the docs are discussing *ordinary* (stored) views, as opposed to inline views. I wouldn't be surprised if the paragraph online applied to ordinary views. Obviously merging an ordinary/stored view must be completely different for the parser compared to merging inline views. As one would probably need to rewrite the parser to merge inline views, I'm not sure whether merging inline views is possible at all.

--
Sybrand Bakker, Senior Oracle DBA
Received on Fri Dec 26 2003 - 17:43:14 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US