Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Problems with Views?
Vince,
IMHO:
> Does this query first build the results of the view before the outer
> statement?
Yes, the inner sql is executed, then the outer sql is applied to the results.
> Or will the outer select's where clause be incorproated
> into the inner (view)?
No, it is applied separately.
> I am just wondering how bad it is.
In these cases query performance will depend largely on the efficiency of the view. Run an explain plan on the select statement of the view - that is where you will get the most improvement from tuning. Also keep in mind that the indexes on the tables underlying the views should be designed to support the sql from the views you are using.
Hope these thoughts help.
Mike
"Vince" <vinnyop_at_yahoo.com> wrote in message
news:56e2f55a.0208160706.7f60b43a_at_posting.google.com...
> 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 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.
>
> Thanks in advance, Vince
Received on Fri Aug 16 2002 - 11:56:24 CDT