Re: Indexes and Views

John Shepherd wrote: > > Greetings ! > If I have a source table (T) and a view (V) of that table. > When my application issues a SELECT against V, how does Informix use indexes > ? > Is it able to use an index against (T) for the underlying creation of (V) > and *then* use another more appropriate index for the SELECT against (V) ? There are no indexes associated with views, as views are logical objects and not physical ones. The indexes created on the table will be used when you select from the view. Cheers, -- Mark. +----------------------------------------------------------+-----------+ | Mark D. Stock mailto:mdstock@mydas.freeserve.co.uk |//////// /| | http://www.informix.com http://www.informixhandbook.com |///// / //| | http://www.iiug.org +-----------------------------------+//// / ///| | |This email will self-destruct in |/// / ////| | |10 sec. If you received this email |// / /////| | |in error, sorry about the mess. |/ ////////| +----------------------+-----------------------------------+-----------+

Hi Mark Thanks for the reply. Appreciate the fact that views are just logical extensions of tables. What I was getting at was .... Does Informix treat my query against (V) as 2 *separate* SELECTs vs (T) ie SELECT #1 creates the View & then SELECT #2 gets the actual data that the application wants ?? If this is the case, then Informix will be able to use 2 different indexes as appropriate Regards, John "Mark D. Stock" <mdstock@mydas.freeserve.co.uk> wrote in message news:8s8c2o$o4b$1@news.xmission.com... > > John Shepherd wrote: > > > > Greetings ! > > If I have a source table (T) and a view (V) of that table. > > When my application issues a SELECT against V, how does Informix use indexes > > ? > > Is it able to use an index against (T) for the underlying creation of (V) > > and *then* use another more appropriate index for the SELECT against (V) ? > > There are no indexes associated with views, as views are logical objects > and not physical ones. The indexes created on the table will be used > when you select from the view. > > Cheers, > -- > Mark. > > +----------------------------------------------------------+-----------+ > | Mark D. Stock mailto:mdstock@mydas.freeserve.co.uk |//////// /| > | http://www.informix.com http://www.informixhandbook.com |///// / //| > | http://www.iiug.org +-----------------------------------+//// / ///| > | |This email will self-destruct in |/// / ////| > | |10 sec. If you received this email |// / /////| > | |in error, sorry about the mess. |/ ////////| > +----------------------+-----------------------------------+-----------+

With informix the view is not materialized prior to selecting the requested data. Instead the users select query is expanded to include the required information of the view's definition. For instance suppose that your view was somthing like create view myview (name, address, grade)as select name, address, grade from student_list where student_list.enrolled = 'Y'. And your query was select count(*) from myview Then the actual query generated and optimized by the system would be select count(*) from student_list where student_list.enrolled = 'Y'. John Shepherd wrote: > Hi Mark > Thanks for the reply. > Appreciate the fact that views are just logical extensions of tables. > What I was getting at was .... > Does Informix treat my query against (V) as 2 *separate* SELECTs vs (T) > ie SELECT #1 creates the View > & then SELECT #2 gets the actual data that the application wants ?? > > If this is the case, then Informix will be able to use 2 different indexes > as appropriate > > Regards, > John > > "Mark D. Stock" <mdstock@mydas.freeserve.co.uk> wrote in message > news:8s8c2o$o4b$1@news.xmission.com... > > > > John Shepherd wrote: > > > > > > Greetings ! > > > If I have a source table (T) and a view (V) of that table. > > > When my application issues a SELECT against V, how does Informix use > indexes > > > ? > > > Is it able to use an index against (T) for the underlying creation of > (V) > > > and *then* use another more appropriate index for the SELECT against (V) > ? > > > > There are no indexes associated with views, as views are logical objects > > and not physical ones. The indexes created on the table will be used > > when you select from the view. > > > > Cheers, > > -- > > Mark. > > > > +----------------------------------------------------------+-----------+ > > | Mark D. Stock mailto:mdstock@mydas.freeserve.co.uk |//////// /| > > | http://www.informix.com http://www.informixhandbook.com |///// / //| > > | http://www.iiug.org +-----------------------------------+//// / ///| > > | |This email will self-destruct in |/// / ////| > > | |10 sec. If you received this email |// / /////| > > | |in error, sorry about the mess. |/ ////////| > > +----------------------+-----------------------------------+-----------+