Re: view-optimizing

Harald Mitterhofer wrote in message <3A59BB5B.AEE533DC_at_siemens.at>...
>hallo!
>
>I am confronted with a serious performance-problem:
>there are selects on joined VIEWS which last about 10 minutes;
>when I rewrite the select and "resolve the views by hand" eg. build an
>equivalent select with joins on the view-underlieing tables (no more
>view in the select statement) the query runs 300 milliseconds;
>
>acording to oracle-docs, the optimizer should be able to do exactly the
>same automatically;
>is there a way to force the optimizer to transform the view-query into
>an equivalent select on only tables?
>I took a look at the optimizer-plan and he seems to do a scan on one
>view and then join the other view, which is definitely the most stupid
>thing he could do in this case;
>
>can anybody give me a hint, how to solve this problem?

Mark is correct, the optimiser can usually transform views into the equivalent SQL statement without them, unless there is some good reason why it can't. eg. views with set-operators like GROUP BY. Another example is when you try to perform an outer-join to a view: there are good reasons why Oracle has difficulty achieving this and so performs the way you describe. Are you *sure* your manual transformations provide an equivalent result set to the results of the query with views?

See my web-page: http://home.clara.net/dwotton/dba/ojoin2.htm for more information about why Oracle has trouble integrating views when outer-joins are involved.

Dave.

--
If you reply to this newsgroup posting by email, remove the "nospam"
from my email address first.