Re: Query Performance through Views

In <CG8wLt.Myy@vcd.hp.com> kenm@vcd.hp.com (Ken Miles) writes: >It appears that Informix Online 5.00 makes some rather mindless assumptions >about query optimization under the "create view" statement. It essentially >ignores any indexes it might be able to use based upon what columns are >selected when accessing the view. With "set explain on" the engine spits >out the same grossly inefficient query plan regardless of what columns I >specify in my query. >In the example query attached below two column values are specified which >comprise a composite key (shift_date & shift) which are the best path >to the data but OnLine completely ignores this information, grinds through >the tables in a sequential search, then filters for the values I specified. >It takes eons. (Query plan "estimated cost" = 9578). This appears to be a feature of the "DISTINCT" in your view definition. This seems to force a scan of the underlying tables to remove duplicates before applying your filter criteria. Does it really need to be there? Does the check option need to be there as this is a non-updateable view? >The exact same query posted directly against the tables comes back >instantaneously. (Query plan "estimated cost" = 24). I believe the >query against the View should net the same performance. I believe it would if you remove the "DISTINCT". >Also: I "update statistics" daily. >My questions are: > > 1. How do I get Informix to use the optimal query path under a > view? Does 5.01/.02 have the same behavior? See above. Don't know. > 2. Having just bet the farm on the suitability of building a > Data Warehouse centered around Informix Views, did I just > make a colossal blunder? Don't know. >Ken Miles >Hewlett Packard Vancouver Division >kenm@vcd.hp.com graeme -- Disclaimer: The author's opinions are his own, and not necessarily those of Pyramid Technology Ltd. or Pyramid Technology Inc. --------------------------------------------------------------------------- -m------- Graeme Sargent Voice: +44 (0)252 373035 ---mmm----- Senior Database Consultant Fax : +44 (0)252 373135 -----mmmmm--- Pyramid Technology Ltd. Telex: Tell who??? -------mmmmmmm- Farnborough, Hants GU14 7PL Email: graeme@pyra.co.uk --------------------------------------------------------------------------- We have the technology. The tricky bit is learning how to use it.