Re: Views - performance

Jeff
according to me using inline view is more faster in terms of performance than using stored object views when u use stored object views oracle has to read the data dictionary to find out the existence as well as the validity of the view then it will search the dictionary for the syntax(query) of the view .it will then execute the query
this includes lots of steps for execution whereas in inline views oracle just has to check to syntax and semantics of the query and fires it
moreover for debugging the query you will have to query user_views for all the views in the query and then combine them with the original query
so it also becomes a headache to debug
hence i would suggest you to go for inline views

laxman

fitzjarrell_at_cox.net wrote:
> Comments embedded.
> Jeff User wrote:
> > Thanks Dave
> >
>
> I prefer David.
>
> > I thought maybe there was a general do or don't do rule of thumb.
>
> No. Unless, of course, you like hitting your thumb.
>
> > It is V 9i on a Unix system. I don't even know any more than that
> > about the system.
>
> 'V 9i' says about as much as calling ground meat 'hamburger', as 'V 9i'
> spans 9.0.1.x through 9.2.0.8, and the differences are vast between
> 9.0.1.x and 9.2.0.x. Discovering the full release number is fairly
> simple:
>
> $ sqlplus /nolog
>
> SQL*Plus: Release 9.2.0.6.0 - Production on Wed Sep 6 14:32:08 2006
>
> Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
>
> SQL>
>
> Surely you have access to SQL*Plus.
>
> >
> > There are many views that I am encountering and was considering
> > re-writing some of them.
>
> You *have* tested these 'rewrites' on another system? And you do
> realise that making a view more complex in hopes of simplifying the
> final query may backfire on you? Before you do any modification to the
> existing views you need to know or ask several things:
>
> Are these views part of a licensed software package? If so you likely
> cannot modify them without violating the EULA. Occasionally the vendor
> will allow such changes and will provide authorisation in writing; you
> need to contact the vendor to field a response.
>
> Will these changes actually improve your situation? The only way
> you'll possibly have any idea is to use explain plan to provide a base
> execution path. Better still, have the DBA set up a 10046 trace on
> your session and start executing queries using the existing views. The
> tkprof output can be quite eye-opening.
>
> Did you investigate subquery factoring in place of 'hacking' away at
> existing views? For example:
>
> with myquery as ( .....),
> mynextquery as (....),
> myotherquery as (....)
> select ..., ..., ...
> from myquery, mynextquery, myotherquery
> where ...;
>
> Jonathan Lewis explains how this works very nicely in his text "Cost
> Based Oracle Fundamentals"; I strongly suggest you obtain a copy and
> start reading.
>
> > I have managed to combine a few, but not sure
> > if it is worth it.
>
> You won't know until you generate execution plans and statspack reports
> to see what the database is doing. Up until then all you have is
> guesswork.
>
> > We are however, dealing with several hundred
> > thousand records to around 1 to 1.3 million at a clip sometimes, so it
> > is going to get more attention.
>
> Those are not large volumes of data; you need to work with your DBA to
> tune what you already have in place before you try to 'fix' things by
> changing the source tables/views. Knowing what Oracle is doing with
> your query is half of the battle, as you have a starting point on
> changing your code to enable Oracle to perform better with the existing
> objects. If you can't get acceptable performance after tuning, then
> investigate how to modify the views to provide better
> performance/throughput, presuming, of course, you have permission to
> enact such changes.
>
> >
> > Thanks for the reply.
> >
> > Jeff
> >
>
> Please do not top-post.
>
> >
> >
> > On 6 Sep 2006 09:00:15 -0700, "fitzjarrell_at_cox.net"
> > <fitzjarrell_at_cox.net> wrote:
> >
> > >
> > >Jeff User wrote:
> > >> Hi everyone
> > >> Sorry if this is cross posted, I just found this group.
> > >>
> > >> I would like to know if using several views for a complicated query is
> > >> slower than placing all the code in one large query.
> > >>
> > >> I had heard that views were expensive in terms of performance, but I
> > >> find them used quite a bit.
> > >>
> > >>
> > >> Jeff
> > >
> > >The answer depends upon the code you're writing and how you choose to
> > >write it. Subquery factoring could help with this, creating views
> > >might be faster, using 'in-line' views might provide better
> > >performance, but you, and we, won't know until you actually profile
> > >your query and post the query plan:
> > >
> > >explain plan
> > >set statement_id = '...' for
> > ><your in-doubt query here>;
> > >
> > >select *
> > >from table(dbms_xplan.display);
> > >
> > >We can, and will, make suggestions to help you but asking for 'blanket
> > >statements' isn't the best way to answer your question. There are too
> > >many variables at work, such as which release of Oracle (all four
> > >numbers), which operating system, how much RAM, the suspect query and
> > >the query plan. Given that information we can provide some insight;
> > >absent that your guess is as good as ours, and it would be merely
> > >guesswork at this point. You certainly wouldn't ask a mechanic to fix
> > >your automobile without giving him/her access to it.
> > >
> > >Provide more information on what you're doing and how you're trying to
> > >do it, along with the requested Oracle and operating system details.
> > >Then we can possibly provide a better answer to your question.
> > >
> > >
> > >David Fitzjarrell
>
>
> David Fitzjarrell
Received on Thu Sep 07 2006 - 01:02:35 CDT