Re: Performance of Views

akreienbuhl_at_yahoo.com (alainkr) wrote in message news:<9822b77f.0210020057.655a83fe_at_posting.google.com>...
> > In a case like this the use of a view to define the join will probably
> > help reduce the chance of bad SQL being developed to perform the same
> > query since the view as coded should help ensure the best join
> > order/method is in use.
>
> Thanks, this is exactly my point. I've heard about compiling view etc.
> could you point me to some documentation.
>
> Thanks.
>
> Alain.
>
>
> Mark.Powell_at_eds.com (Mark D Powell) wrote in message news:<2687bb95.0210011036.1f897543_at_posting.google.com>...
> > akreienbuhl_at_yahoo.com (alainkr) wrote in message news:<9822b77f.0210010654.13456cb8_at_posting.google.com>...
> > > Hello all,
> > >
> > > I need your advice for the following problem :
> > >
> > > I'd like to use a view for a query that is repeated througout our
> > > code. That query has 7 joins. From a developer I'd really like that.
> > > It would prevent each developer from aliasing with different name etc.
> > > I'm affraid of performance problem since that view would be against a
> > > quite large table 3,000,000 records / year
> > >
> > > A ran an execution plan for both (using the view and using tables)
> > > there're obliously identical. Now does that mean that I won't have any
> > > more performance problem with the view that without.
> > >
> > > I apreciate your help.
> > >
> > > Alain.
> >
> > Alain, no this does not guarentee that you will never have a
> > performance problem related to the view, but it does mean you are
> > probably OK.
> >
> > If you are using the CBO then outdated statistics or a permanent
> > change over time in the relationship (relative size, data value range
> > changes etc...) between some of the tables could affect the optimal
> > plan and require revisiting the tuning process. This is also true
> > with a straight query against the tables.
> >
> > In a case like this the use of a view to define the join will probably
> > help reduce the chance of bad SQL being developed to perform the same
> > query since the view as coded should help ensure the best join
> > order/method is in use.
> >
> > IMHO -- Mark D Powell --

The SQL Manual has the syntax for creating a view, and the only time you 'compile' a view at least through 8.1 is when you want to manually revalidate an invalidated view "alter view viewname compile".

The Concepts manual should introduce the concept of a view while the DBA Admin manual should have a section on managing views.

• Mark D Powell --