In article <3A59BB5B.AEE533DC_at_siemens.at>,
Harald Mitterhofer <Harald.Mitterhofer_at_siemens.at> wrote:
> 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?
>
> /harald mitterhofer
>
How the optimizer solves a query involving views depends on the query
and view code (group by, having, distinct etc...). You best bet may be
to take a second look at the explain plans for the original code and
you version and see if you can hint the code to run like your code, or
an altername approach would be to store your code as a view and have
the application use it.
--
Mark D. Powell -- The only advice that counts is the advice that
you follow so follow your own advice --
Sent via Deja.com
http://www.deja.com/
Received on Mon Jan 08 2001 - 10:40:58 CST
