When an SQL statement or some part of it is not known until the execution time, then these statements are executed dynamically. In the case of dynamic SQL, the statement is compiled at runtime. It also means that the access plan is created at runtime, which can benefit from the latest statistics. When the database manager runs a dynamic SQL or XQuery statement, it creates an access plan based on the current system statistics and current configuration parameters. This access plan might change from one execution of the statements, application program to the other.
Dynamic SQL can be used when:
A complete SQL statement or some part of it is not known at compile time
Objects referenced in the SQL statement are not available at compile time
We want to use the latest statistics available to optimize the access plan
We might change the environment such as the changing database or database manager configuration parameters, special registers, and so on, which might improve...