Advantages of using placeholders in SQL statements

1. Increase the readability of SQL code
2. Placeholder can be pre-compiled to improve execution efficiency
3. Preventing SQL injection
4 The purpose of the placeholder is to bind the variable, which can reduce the hard parsing of data sql, so the execution efficiency will improve a lot

The binding variable is the primary tool for Oracle to resolve hard parsing, which solves the excessive consumption of the library cache in an OLTP system to improve performance

The binding variable is the primary tool for Oracle to resolve hard parsing, which solves the excessive consumption of the library cache in an OLTP system to improve performance. Then the knife grinds too fast, makes it sharp, but easy to break. Everything has advantages and disadvantages of the two, local conditions, timely, all in how to weigh only. This article describes the use of bound variables, as well as the advantages and disadvantages of binding variables, use occasions.

When it comes to binding variables, you have to understand hard parsing and soft parsing. Hard parsing in short, a SQL statement is not run and is running for the first time, it needs to be parsed, semantically identified, followed by statistical information to generate the best execution plan, and then executed. and soft parsing, it is due to the SQL statement in the library cache has a consistent SQL statement text, the operating environment, that is, the same parent and child cursors, using take doctrine, directly execute. Soft parsing also goes through syntactic analysis, semantic recognition, and the generation of hash value, followed by the library cache to search for the same hash value, such as existing in the implementation of soft parsing. For more hard parsing and soft parsing as well as parent cursors, child cursors

The binding variable is to reduce the parsing, for example you have a statement like this:

Select AAA,BBB from the CCC where ddd=eee;
If you often query by changing the value of the EEE predicate, as follows
Select AAA,BBB from the CCC where DDD=FFF;
Select AAA,BBB from the CCC where DDD=GGG;
Select AAA,BBB from the CCC where ddd=hhh;
Each statement to be parsed by the database once, so that a waste of resources, if the Eee replaced with ": 1" as a binding variable form, regardless of the value of DDD behind, no need to repeat the resolution

Suppose you want to update the payroll for employees with IDs from 1 to 10000 to $150.00,
Do not use bound variables:
Sql.executequery ("UPDATE employees SET Salay = 150.00 WHERE id = 1");
Sql.executequery ("UPDATE employees SET Salay = 150.00 WHERE id = 2");
Sql.executequery ("UPDATE employees SET Salay = 150.00 WHERE id = 3");
Sql.executequery ("UPDATE employees SET Salay = 150.00 WHERE id = 4");
....
Sql.executequery ("UPDATE employees SET Salay = 150.00 WHERE id = 10000");

To use a binding variable:
UPDATE employees SET Salay =? WHERE id =? "

The difference is that instead of binding a variable, it is equivalent to parsing and executing a 1w SQL statement repeatedly. With a binding variable, the parse SQL statement is used only once, followed by a 9,999-second reuse of the execution plan that was generated for the first time. Obviously, the latter will be more efficient.

When should not/do not have to use bound variables
A. If you use a data warehouse, a large query runs for several hours, there is no need to do binding variables, because the cost of parsing is negligible.
・the. Variables have a significant impact on the optimizer's execution plan: When a bound variable is used, the query optimizer ignores its specific value, so its estimated accuracy is far less accurate than using literal values, especially if the table has data skew (the data is unevenly distributed on the table), which provides the wrong execution plan. This allows for a non-efficient execution plan to be used.

Advantages of using placeholders in SQL statements