Re: Problems with Views?

On 16 Aug 2002, vinnyop_at_yahoo.com wrote:
> We use views to simplify front-end development (using a persistence
> tool - TopLink). As we are getting closer to actual devivery, I am
> getting concerned about the performance of such an approach. The
> following is an example of a query executed using such a view (we
> don't inline the view as shown below):
>
> SELECT state, COUNT(*), sum(dollars)
> FROM (
> -- here is the view
> SELECT country,
> state,
> city,
> to_char(some_date, 'YYYYMM') as service_month,
> sum(big_bucks) as money
> FROM table t1,
> table t2
> WHERE t1.joins = t2.joins
> GROUP BY country, state, city, to_char(some_date, 'YYYYMM')
> -- end view
> )
> WHERE country = 'ATLANTIS'
> GROUP BY state
>
> Does this query first build the results of the view before the outer
> statement? Or will the outer select's where clause be incorproated
> into the inner (view)?

I'm a bit confused. Your actual query is

    SELECT state, COUNT(*), sum(dollars)     FROM view
    WHERE country = 'ATLANTIS'
    GROUP BY state
?

If this is the case, then all you have to do is tune the view. The problems with views arise when you start joining other objects to them. View resolution can modify the plans and all of the sudden, everything slows down.

Another issue with views is when the view performs a inline view. The inline view can never be restricted by restricting on the columns.

> I realize it would be better to construct the query without the view,
> but we are using the underlying view to provide semi-customizable
> reporting where the outer select is dynamic (but limited to the group
> by columns within the view). I am just wondering how bad it is.

In your case, the view is a great reason to use them. Just be careful as you try to pile joins on top of this view.

-- 
Galen deForest Boyer
Sweet dreams and flying machines in pieces on the ground.