Explain Plan for Open cursor

Team, I am using ESQL/C to interact with Informix 10. I need help in following issues: 1). will explain plan be prepared again when opening the cursor when passing different param in SQL? eg:- sprintf (select_1, "%s %s %s %s %s", "SELECT o.order_num, sum(total price)", "FROM orders o, items i", "WHERE o.order_date > ? AND o.customer_num = ?", "AND o.order_num = i.order_num", "GROUP BY o.order_num"); EXEC SQL prepare statement_1 from :select_1; EXEC SQL declare q_curs cursor for statement_1; EXEC SQL open q_curs using :o_date, :o.customer_num;(Will the explain plan prepared again?) Best Regards, Subbu

The big answer is "Generally, no.". However, there are additional parts to that answer and sometimes the answer is "Sort of.". Any query that is prepared with replaceable parameters in the WHERE clause is only partially optimized. There is a final optimization step that takes the actual parameter values supplied in the OPEN statement into account, that step is repeated each time the cursor is OPENed. The other answer has to do with something called Open-Fetch-Close Optimization. If the corresponding ONCONFIG parameter or environment variable is set, then optimization is deferred until the time that the OPEN is executed. In that case, the first optimization will take place at the time of the first open, not when you execute the PREPARE. Subsequent OPENs will operate as above. Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Apr 11, 2011 at 4:32 PM, M SUBBU <daredevil_subbu@yahoo.co.in>wrote: > Team, > > I am using ESQL/C to interact with Informix 10. I need help in following > issues: > > 1). will explain plan be prepared again when opening the cursor when > passing > different param in SQL? > > eg:- > sprintf (select_1, "%s %s %s %s %s", > > "SELECT o.order_num, sum(total price)", > > "FROM orders o, items i", > > "WHERE o.order_date > ? AND o.customer_num = ?", > > "AND o.order_num = i.order_num", > > "GROUP BY o.order_num"); > > EXEC SQL prepare statement_1 from :select_1; > EXEC SQL declare q_curs cursor for statement_1; > EXEC SQL open q_curs using :o_date, :o.customer_num;(Will the explain plan > prepared again?) > > Best Regards, > Subbu > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --bcaec54859be6eb76604a0aaa146

Thanks Kagel.