Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Index use on views

Re: Index use on views

From: Walt <walt_askier_at_SHOESyahoo.com>
Date: Thu, 25 Jan 2007 13:07:23 -0500
Message-ID: <12rhsescj2guk31@corp.supernews.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US