Re: Index use on views

Ana Ribeiro wrote:
> Hi all,
> I have a view that selects the WHOLE data from several tables but
> grouping the data in a special way. This way, whenever I query this
> view a FULL TABLE SCAN is executed.
>
> One of the underlying tables has an index on the column REGION_ID, and
> whenever I query this table using WHERE REGION_ID = X this index is
> used and I get a very fast response.
>
> My Question is:
> If I query THE VIEW using the same predicate (WHERE REGION_ID = X),
> will my query use an index on this column instead of executing a Full
> Table Scan? Or will ANY QUERIES on this view execute a FULL TABLE SCAN

It *should* use the index. Views are implemented by replacing the view with its underlying definition and then parsing the SQL.

For instance if your view looks something like

   create view v as select a.id , b.foo
   from a,b
   where a.id=b.id;

Then the following query

   select v.foo, c.bar from v, c where c.id = v.id;

gets expanded and parsed as something like

   select v.foo, c.bar from a,b,c where a.id=b.id and a.id=c.id;

That is, the view goes away and the query is run against the underlying tables. Any indices will be used (or not) according to the optimizer.

//Walt Received on Thu Jan 25 2007 - 12:07:23 CST