How smart is Oracle's view interpreter?

There's an answer to a query about aggregate functions in views which was posted by johnd_at_ducvax.auburn.edu in article <1993Apr9.125220.1_at_ducvax.auburn.edu> at the tail of this message.

I have a related question I would like some information on.

I would like to know how smart the SQL parser is when interpreting queries to views. For instance, if a view has a column that is a result of of summary operation, but that column is not queried, does the system go to the trouble of doing the calculation anyway?

For example, using the view man_region defined below, would the query   SELECT S_DATE, MAN_CODE FROM MAN_REGION cause the summation to be done?

Similarly, if a view is composed of the results of a number of SQL statements UNION'ed together, do view definitions statements which contribute no tuples to the result of a particular query get evaluated during the query evaluation?

Please reply by email, if possible.  

In article <1993Apr9.125220.1_at_ducvax.auburn.edu> johnd_at_ducvax.auburn.edu writes:
>
>Here's an interesting one for you SQL fans:
>
>I'm running into a little (ha!) problem creating a view with a
>aggregate function. Here's the SQL code:
>
> select col1,
> max(col2)
> from
> table1, table2
> where
> table1.col1 = table2.col1
> group by
> col1

This should work without any problem. Here's an example with RDBMS 6.0.33.2.1 running on UNIX on a Sun 4. Note that rfdivisions is a view also.
>

SQL> create view man_region (s_date, location, man_code, man_count)   2 as
  3
  select s_date, region_code, man_code, sum(man_count)   4 from acmman a, rfdivisions b
  5 where a.location = b.division_code
  6 group by s_date, region_code, man_code;

View created.

SQL> select man_count from man_region where s_date like '%AUG-92';

 MAN_COUNT

       238
       572


-- 
Gary Hall                  | Voice (604) 291-3208 | INTERNET: hall_at_cs.sfu.ca
Centre for Systems Science | Fax   (604) 291-4424 | 
Simon Fraser University    | 			  
Burnaby, B.C.  V5A 1S6     |