Re: SET EXPLAIN / SQL

On Mon, 14 Sep 1998, Art S. Kagel wrote: > Stefan Weideneder wrote: > > as Sujit mentioned already, the results of SET EXPLAIN are > > available as soon as you prepared your query. > > This is not always true, but in most cases. > > > > If the query contains no question-mark "?", your query will > > be optimized at the time of the prepare. Otherwise it will > > be optimized at the EXECUTE or OPEN/FOREACH statement. > > Actually, all SQL is optimized at the time it is prepared EVEN if it > has replacable parameters ("?"). This is why the SET EXPLAIN output > for a query with a replacable parameter on a column that is part of the > fragmentation expression of a table will always scan all fragments > unless a non-fragmented (or fragmented on another column), detached, > index is selected by the optimizer. And, in version 7.2 and above, there is an extra option to the ESQL/C OPEN statement: EXEC SQL OPEN c_select USING :var1, :var2, ... WITH REOPTIMIZATION; That usually only makes sense if there are parameters to the statement; if there are no parameters and there have been no changes in the statistics, then the reoptimization simply wastes times and produces the same query plan. Yours, Jonathan Leffler (jleffler@informix.com) #include <witticism.h> Guardian of DBD::Informix v0.60 -- http://www.perl.com/CPAN Informix IDN for D4GL & Linux -- http://www.informix.com/idn