How long execution plan for a Prepared statement remains valid at database. let say i create a prepared statement for an update statment and my code excute that statement 500 times so i understand that during first call the excution plan or some other setup done for executing prep statement will be created at database side and will be valid for subsequent calls. My doubt is how long that execution plan live on Db
is it bound to PreparedStatement or Connection Object, in other words once done with my update if i close prep statement and commit transaction what haapens to that plan which was created on DB for my prep statement, if i create a new prepared statement object with same SQL string in the same connection what will happen now.
If its green its biology if its stinkks its chemistry if it has numbers it is Maths and if it doesn't work its TECHNOLOGY
Yogesh, There are two execution plans. There is the one that lives in Java in the database driver. This one is linked to the connection object. In practice people use a connection pool and the prepared statement cache is linked to the connection that lives on in the pool. Therefore it has a longer life.
There is also the execution plan that lives on the database. It can potentially live longer than the connection.