Re: Problems with Views?

Herman,
Thank you and others for your responses.

Could you further explain what you meant by: Before 9.2, one can see this (cumbersome!) with event 10060 set.

PS we are working with 8i and more importantly, the select list in the view has about 10 columns (most requiring outer joins) that get grouped by, only to have the final output summarize those down to a single column that the user selects as the "grouping"

Herman de Boer <h.de.boer_at_itcg.nl> wrote in message news:<ajq8hg$km6$1_at_news1.xs4all.nl>...
> Vince wrote:
>
> >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
> >
>
> Vince,
>
> Oracle determinces first (independent of the optimizer) whether or not
> the view text (for both real views and inline) views can be merged
> with the main query. If not (which behaviour might also be forced with
> the NO_MERGE hint), the predicates will be pushed inside the view. In
> the example you gave, the predicate "country = ATLANTIS" will be
> pushed inside the view, equal with:
>
> 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
> AND country = 'ATLANTIS' --<<predicated pushed>>
> GROUP BY country, state, city, to_char(some_date, 'YYYYMM')
> -- end view
> )
> GROUP BY state
>
> Note that the 9.2 version plan_table contains 'new' columns
> (access_pred and filter_pred), which show this very clear. Before 9.2,
> one can see this (cumbersome!) with event 10060 set.
>
> Kind Regards,
>
> Herman de Boer
> sr consultant
> IT Consultancy Group bv.
Received on Mon Aug 19 2002 - 13:07:27 CDT