First a simple question - are views capable of using indexes when
selected from with a where clause?

I am not seeing index usage when selecting from views, whereas the
correct index is used when running the query contained in the view
seperately.

Assume this scenario:

create table A (
column1 integer,
column2 integer,
column3 integer,
created_date date);

Table A:

column1 column2 column3 created_date
------- ------- ------- ------------
1 1 1 11/1/2004 10:00:00
1 1 1 11/2/2004 09:00:00


The view would be defined as such:

Create view A_Max (Column1, column2, column3, max_create_date) as
select
column1, column2, column3, max(created_date)
from
a
group by
column1, column2, column3;


The query would look something like this:

select
column1, column2, column3, max_created_date
from
A_Max
where
column1 = 1


Assuming there is an index on column1, the plan should be "A index
(column1_idx)". However, I get a plan of "A natural". If I run this
query:

select
column1, column2, column3, max(created_date)
from
a
where
column1 = 1
group by
column1, column2, column3

Then the correct plan is issued.


I'm pretty sure I'm not understanding something here.

tia,

Bob M..