Re: tuning of views

In the past when i have done reports based on multiple views, i always seems to have problem getting the swine to run faster. It is best to first tune all 3 views to their optimal using Explain Plan and TKPROF.
When using all 3 in the report, use explain plan to check the execution path. You'll probably find a few full table scans on some of your views. A trick i used to use is to select 'something' from an indexed field even if i am not directly using it in my main query, just to force the query to use the index. i have seen improvements in speed using this. It can be a bit hit and miss, you'll just have to try it and study your execution plans.

The following is just an example of what i am talking about.

select empno, ename, manager
from emp_view e, manager_view mgr
where e.man_no = m.man_no
-- The next line is used just to force the query to use an index and e.id > 0;

If you can write the query without the views, try that first.

HTH Good luck!

Mark

In article <3_V44.415$nu6.1819756_at_nnrp3.proxad.net>, "PERPEN Manuel" <manuel.perpen_at_free.fr> wrote:
> Hello
> I'm working with Oracle and i have a big problem with a request
> with wiews.
> I have to use a request composed of 3 views. I have to wait 8
> hours to have
> the result but i need less than 6 minutes to have the result of
> every of the
> view with a separate treatment. When i use the result of every
> view in
> access i need 3 seconds to have the final result.
> View1 : 2500 lines in 2 minutes
> View2 : 4000 lines in 4 minutes
> View3: 2000 lines in 2 minutes
> The final request composed of view1 ,view2 and view3 : 8 hours
> The results of view1, view2 and View3 in ACCESS : 3 seconds.
> I think that it's not possible for access to be faster than
> oracle!! If you
> have an idea i thank you very much to send me an email at
> manuel.perpen_at_free.fr

• Sent from RemarQ http://www.remarq.com The Internet's Discussion Network * The fastest and easiest way to search and participate in Usenet - Free!