Re: Problems with Views?

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 - 02:58:27 CDT