Re: Union view limits

Art Kagel wrote: > The only limit that I am aware of is the limits on the length of a > single SQL statement which is 65535 characters, so if each table and > column name is two characters long that works out to about 3449 SELECTS > in an maximum length UNION less the characters needed to define the VIEW. > > Art > > Art S. Kagel > Oninit (www.oninit.com <http://www.oninit.com>) > IIUG Board of Directors (art@iiug.org <mailto:art@iiug.org>) > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on my employer, Oninit, the IIUG, nor any other > organization with which I am associated either explicitly or implicitly. > 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 Wed, Oct 21, 2009 at 2:34 PM, fandelau <fandelau@gmail.com > <mailto:fandelau@gmail.com>> wrote: > > Guys, > Is anyone aware of a limit on the number of select statements that a > union view can handle? I have tested a union view with 100 "select * " > from 100 different tables, with no problem. Just wondering if there is > a ceiling, or if the sky is the limit... ^_^ > I'm working on 11.10.FC3 on Solaris. > Thanks in advance! > > Ramon. > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org <mailto:Informix-list@iiug.org> > http://www.iiug.org/mailman/listinfo/informix-list > > Cool idea, Art. Nothing as clear as 2-character table names! ;-) OK, now that Ramon has opened this can-o-worms, let's play with another idea: Chaining unions. Suppose you approach this 65K limit on the length of SQL statement in your CREATE UNION command. Workaround: Create a broad as union as you can within that 65K limit. Name it UN1. For the remaining SELECTs, create UN2, UN3, as many as you need. When you have all these unions, create the final union as SELECT * FROM U1 union SELECT * FROM U2... Then watch the optimizer bury itself in the sand. ;-) The optimizer aside, I wonder: Would the expansion of unions internally violate the 65K limit? -- Jacob, the speculator