Re: are views obsolete ???? optimizer error ????

Path: emory!sol.ctr.columbia.edu!zaphod.mps.ohio-state.edu!hobbes.physics.uiowa.edu!news.iastate.edu!news From: GO.MSB@isumvs.iastate.edu Newsgroups: comp.databases.informix Message-ID: <1992Jan29.152022.21806@news.iastate.edu> Date: 29 Jan 92 15:20:22 GMT Sender: news@news.iastate.edu (USENET News System) Organization: Iowa State University, Ames IA In article <15865@sbsvax.cs.uni-sb.de>, becker@ukh840.med-in.uni-sb.de writes: >Sirs > >We have a table with a field izahl - the patients identification - and a >field hktnr - number of an examination. > .... stuff deleted .... > >When I now write: select * from hkt_nr_max where izahl = "GUI290321F", >I must wait long time. It seems, Informix builds the view for all entries >in the table into a temporary table and then selects the izahl. This >could be derived from the sqexplain-file. I think, this is an error >in Informix V4.0 - perhaps the optimizer works wrong. > >Any Ideas? > >Dieter >-- > Dr. med. dipl.-math Dieter Becker Tel.: (0 / +49) 6841 - 16 3046 > Medizinische Universitaets- und Poliklinik Fax.: (0 / +49) 6841 - 16 3369 > Innere Medizin III > D - 6650 Homburg / Saar Email: becker@med-in.uni-sb.de ---------------------------------------------------- Unfortunately, you have found what I also consider to be a weak point with views. I also found this problem when doing some work with ORACLE. Since the WHERE is not within the view, it processes all of the data before applying the WHERE limitation (which is logical from the definition of the view). I was using ORACLE's SQL*Forms and had to put the data from the WHERE into a temporary table, and then join to it in the view to get the subset from the view. Not very practical for command level access. This is not a solution to your problem, tut mir leid. Aber, ich verstehe ihre Vereitelung. Marvin Beck Iowa State University