Re: Index use on views

On 25.01.2007 18:47, Ana Ribeiro wrote:
> 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.

No surprise that you get different results when querying that table only.

> 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

That probably depends on the join, the data, the schema, whether statistics are current... Why don't you just try it out or use EXPLAIN?

> FYI, I am using Oracle 9i.

Regards

        robert Received on Thu Jan 25 2007 - 11:55:55 CST