Re: Optimizer & Views

Achim Reiners (ar@mai.de) wrote: [ . . . ] > I have a problem with the performance when selecting through a view. > The output of the SET EXPLAIN option shows that there is a temp table > generated for the View I use. I assume that my performance problems come > from this temp table. > My question: > 1.) When (under what circumstances) does Informix use a temp table for a view? > 2.) If it does so, are the selection criteria in the query (the WHERE clause) > already applied to the temp table? I tripped over performance problems with views also, even when the underlying tables were all indexed appropriately for the queries we were doing. The problem is that Informix will completely ignore indices when queried through views if the view definition contains any of the following: group by distinct aggregates (sum, ave, . . .) outer joins I suspect you are using one of these features. Because Informix doesn't use the indices in such cases it thinks it needs to do a sort/merge join to resolve the query. This causes it to build a temp table first (BTW: beware it always builds internal temp tables with logging ON). If you can engineer around needing any of those four features then performance should be comparable to non-view queries. If you are not using any of these then you are probably just missing an index on a table. Ken Miles Vancouver Division Hewlett-Packard