When do we use Statement vs. Prepared Statement? In my opinion unless you are executing DDL statements (which don’t allow variable binding) you are better of always using Prepared Statement.
Why? To understand this we need to know how oracle queries are executed. Without getting into too much detail the steps (that we would be most concerned about) in executing a query in oracle are:-
1. Parsing
2. Shared pool check (part of parsing step).
3. Development of execution plan. (Most Expensive Step).
4. Execution
Development of execution plan is the most expensive step and using Prepared Statement helps us avoid calling that step often.
Issue with using static queries:-
When you send a static query using Statement (select * from table where name = ‘whatever’), 1.) the query is parsed, 2.) oracle checks for the execution plan in its shared pool (just think of it as some accessible location in memory). It will contain the execution plan if the query was executed before. If yes it jumps to execution (soft parse) else it would develop an execution plan and then execute the query (hard parse). It’s ok as long as you send select * from table where name = ‘whatever’ again however as soon as you change the where clause oracle would need to create another execution plan.
Advantage using dynamic queries:-
Using Prepared statement creates a query using bind variables (select * from table where name = ‘?’). The execution steps are the same, only difference is when an execution plan is created its created using bind variables. Bind variables are substituted with values before the execution phase. So, when you send another query to the database in the form of select * from table where name = ‘?’ with another value, the 1.) query is parsed, 2). oracle checks for the execution plan in its shared pool (and finds it, making it a soft parse), replaces the bind variable with an actual value and executes the query avoiding the 3rd step altogether.
Hard Parse Vs Soft Parse? When queries avoid the development of execution plan stage, we call it a soft parse. When they have to go through the development of execution plan stage we call that a hard parse.
Check this document OracleSQLParsingEssentials explaining this concept with related performance data. Specially read about No Parse (avoid even soft parse!) and Statement Caching (have JDBC cache your prepared statements, avoiding soft parse).
