Reoptimize queries

When you prepare SELECT, EXECUTE FUNCTION, or EXECUTE PROCEDURE statements, the database server uses a query plan to optimize the query. If you later modify the data that is associated with the prepared statement, you can compromise the effectiveness of the query plan for that statement. However, when you change the data, you can reoptimize your query.

You can reoptimize a query by setting the Informix® JDBC Driver extension method to reuse the PreparedStatement object but reoptimize the previously prepared query plan. Alternatively, you can create a new PreparedStatement object. Reoptimizing an existing PreparedStatement object, which rebuilds only the query plan, has the following advantages over creating a new PreparedStatement object, which rebuilds the whole statement:

• Uses fewer resources
• Reduces overhead
• Requires less time

To enable reoptimization, set the withReoptimization argument to the IfmxPreparedStatement.executeQuery() method to true. The executeQuery() method has the following format:

 com.informix.jdbc.IfmxPreparedStatement.executeQuery(boolean withHold, 
                               boolean withReOptimization)

The following query uses the IfmxPreparedStatement.executeQuery() method to enable reoptimization:

Connection conn = DriverManager.getConnection(URL);
com.informix.jdbc.IfmxPreparedStatement pStmt = 
         (com.informix.jdbc.IfmxPreparedStatement) 
         conn.prepareStatement("SELECT * FROM customer");
ResultSet rs =  pStmt.executeQuery(false, true);