Re: Data binding

"Art S. Kagel" <kagel@bloomberg.com> offerred: +Yes. Versions before 7.3 resolved the query path at PREPARE time so, +except for an EXECUTE IMMEDIATE (which prepares and executes at the +same time), these two queries MUST be treated differently. Since the +actual value of the bind variable is unknown at PREPARE time, when it +is represented in the PREPAREd statement by a question mark ('?'), the +optimizer CANNOT use data distributions to decide on which indexes to +use or to make any other query path decisions like fragment +elimination or table ordering in joins. It decides as best it can +based on the available distributions for other tables and columns. + +Version 7.3's optimizer does try to defer some of the decision process +until the data is bound and the CURSOR OPENed. I have no details +about what parts that includes and how successful it is. Prepare has always NOT done optimization if the query included placeholders for host variables, defering the optimization until the cursor was opened and values provided with the USING clause. Now it has also been the case that when a cursor was opened as above and the query optimized at that point, all subsequent use of that cursor (even if it has been closed and reopened) will use the query plan generated at the time of that first open (unless an index that had been chosen is dropped, or the table altered). AT some point, the WITH REOPTIMIZATION clause was added to allow you to specify that the query be checked for reoptimization on subsequent opens of the cursor (allowing different values in host variables to cause different query plans to be chosen). -- Dave Kosenko davek@summitdata.com Director of Training Services (732) 469-4070 Summit Data Group (an Informix Authorized Education Center)