Index On A View Created With A Union

Hi, my informix version is 11.10 running on solaris. i have created a view with unions on a particular tables : sample, samplea, sampleb the columns are (id, username, password). index is defined on username. my view is defined as sampleview: create view on select * from sample union select * from samplea union select * from sapmle b my question is, for example i have select * from sampleview where username = 'abc' will it be using the index on username for each table? somehow, using union is quite faster as compared to querying it against the view. thanks horacio

Yes querying base tables are normally faster than querying a view. You better use "union all " in your view definition , see if any difference. Thanks, Frank On Tue, Mar 6, 2012 at 8:32 AM, NATYURAL HORACIO <horacio.natyural@gmail.com > wrote: > Hi, > > my informix version is 11.10 running on solaris. > > i have created a view with unions on a particular tables : > > sample, samplea, sampleb > the columns are (id, username, password). > index is defined on username. > > my view is defined as sampleview: > > create view on > select * from sample > union select * from samplea > union select * from sapmle b> > my question is, for example i have > > select * from sampleview where username = 'abc'> > will it be using the index on username for each table? > somehow, using union is quite faster as compared to querying it against the > view. > > thanks > horacio > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --f46d043086fcd2425b04ba966371

Because of the UNION when you qpply a filter to the view the optimizer is creating a temp table with the entire contents of the view in it and filtering the rows using a sequential scan. That is why it is slower. There is an ONCONFIG parameter IFX_FOLDVIEW that when set will tell the optimizer to use the definition of the view to replace the view name in queries joining to or filtering rows from the view, but I don't remember if that first appears in 11.10 or in later releases. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Mar 6, 2012 at 8:32 AM, NATYURAL HORACIO <horacio.natyural@gmail.com > wrote: > Hi, > > my informix version is 11.10 running on solaris. > > i have created a view with unions on a particular tables : > > sample, samplea, sampleb > the columns are (id, username, password). > index is defined on username. > > my view is defined as sampleview: > > create view on > select * from sample > union select * from samplea > union select * from sapmle b> > my question is, for example i have > > select * from sampleview where username = 'abc'> > will it be using the index on username for each table? > somehow, using union is quite faster as compared to querying it against the > view. > > thanks > horacio > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e89a8f3b9d97a0c51204ba967eea

hi, thanks for the answer. in effect, are you saying that it would be better to do a union select * from sample union select * from samplea rather than create a view? as the union would be using index but the view would not since it is on a temp table? unless of course I set the paramter that you mentioned. i think it's included in 11.10 from what i've been reding. thanks

also, is it better for me to drop my view and just do the union directly in my sql statement? will this avoid for the need for the temp table creation? btw, when is the temp table created? is it created only when i access the view? or when the view is created Thanks

No, It's better to use 'union all' instead of 'union', when you use simple union the optimizer will use a distinct that will slow down the results. Best regards, Celso Cabral Coimbra Administrador de Banco de Dados ClearTech Ltda "Trust at the heart of Communications" Tel. (11) 3576-4509 -----Mensagem original----- De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Em nome de NATYURAL HORACIO Enviada em: terça-feira, 6 de março de 2012 16:36 Para: ids@iiug.org Assunto: Re: Index On A View Created With A Union [26459] hi, thanks for the answer. in effect, are you saying that it would be better to do a union select * from sample union select * from samplea rather than create a view? as the union would be using index but the view would not since it is on a temp table? unless of course I set the paramter that you mentioned. i think it's included in 11.10 from what i've been reding. thanks ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

View is fine. Have you tried, "Union All"? instaed of just "Union" operator ? Temp table is created when you access it. Thanks, Frank On Tue, Mar 6, 2012 at 2:45 PM, NATYURAL HORACIO <horacio.natyural@gmail.com > wrote: > also, > > is it better for me to drop my view and just do the union directly in my > sql > statement? > > will this avoid for the need for the temp table creation? > btw, when is the temp table created? > is it created only when i access the view? or when the view is created > > Thanks > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e89a8f22c5355bb10b04ba987941

On Tue, Mar 6, 2012 at 12:04, Celso Cabral Coimbra < ccoimbra@cleartech.com.br> wrote: > No, > It's better to use 'union all' instead of 'union', when you use simple > union > the optimizer will use a distinct that will slow down the results. > This is the crucial point. When the view consists of 3 tables in UNION [DISTINCT], the optimizer has no choice but to eliminate the duplicates. If you can stomach working with UNION ALL instead, then you should get better performance - as long as the contributions from the three tables are disjoint. If there could be overlap from the three tables (so some rows could appear in 2 or all three of the unioned tables), you will have to deal with duplication at some point in the processing. > > -----Mensagem original----- > De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Em nome de NATYURAL > HORACIO > > > thanks for the answer. > > in effect, > are you saying that it would be better to do a union > > select * from sample union select * from samplea> > rather than create a view? as the union would be using index but the view > would not since it is on a temp table? > -- Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." --f46d040716c7b8f30604ba991471

The temp table will be created dynamically at query time. Run the queries both ways under set explain to see what is actually happening. Art On Mar 6, 2012 1:45 PM, "NATYURAL HORACIO" <horacio.natyural@gmail.com> wrote: > also, > > is it better for me to drop my view and just do the union directly in my > sql > statement? > > will this avoid for the need for the temp table creation? > btw, when is the temp table created? > is it created only when i access the view? or when the view is created > > Thanks > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9340cbd8e93d804ba99de33