How to bind variables in Oracle

In Oracle, for a committed SQL statement, there are two alternative parsing processes, one called hard parsing and one called soft parsing.

A hard parsing requires many steps, such as parsing, developing execution paths, and optimizing access plans. Hard interpretation not only consumes a lot of CPU, but more importantly, it will occupy the important latches (latch) resources, seriously affect the scale of the system (that is, limit the system's concurrent lines), and cause problems can not be solved by increasing the number of memory chips and CPUs.

This is because the latch is set to sequentially access and modify some memory areas that cannot be modified at the same time. When an SQL statement is submitted, Oracle first checks to see if there are any identical statements in the shared pool, and if so, only perform a soft analysis, otherwise hard analysis is required.

The only way that Oracle can reuse the execution plan is to take the binding variable. The essence of a bound variable is the substitution variable that replaces the constant in the SQL statement. A binding variable can make every SQL statement that is committed exactly the same.

Binding variables are only placeholders, and binding variables with the same name do not mean that they are the same, and when passing, consider the bitwise of the passed value and the order in which the bound variable appears, rather than the name of the binding variable.

Binding variables are generally more efficient and are not normal:

In the case where the field (including the field set) is indexed, and the set of fields (the set) is very large (that is, if a value is particularly large in the field), using a bound variable can result in a query plan error, which makes the query very inefficient. It is best not to use binding variables.

However, it is not always necessary to use binding variables, and here are two exceptions:

(1) For an interval of time to execute the SQL statement, which is the advantage of binding variables can not be effectively used by the optimizer and offset

(2) In the case of a data warehouse.

A bound variable cannot be used as an embedded string and can only be used as a variable in a statement. You cannot substitute a binding variable for a table name, procedure name, field name, and so on.

From the efficiency point of view, because oracle10g abandoned Rbo, the full introduction of the CBO, therefore, the use of binding variables in the 10G increased efficiency than 9i more obvious.

Example:

Normal SQL statement:

SELECT fname, lname, pcode from cust WHERE id = 674;

SELECT fname, lname, pcode from cust WHERE id = 234;

SELECT fname, lname, pcode from cust WHERE id = 332;

SQL statement with binding variables:

SELECT fname, lname, pcode from cust WHERE id =: cust_no;

Using binding variables in Sql*plus:

Sql> Set Timing on

sql> variable x number;

Sql> exec:x: =8

The PL/SQL process has completed successfully.

Time used: 00:00:00.03

Sql> select * from A;

Id

----------

3

5

Time used: 00:00:00.06

sql> INSERT INTO A values (: x);

1 lines have been created.

Time used: 00:00:00.01

Sql> select * from A;

Id

----------

3

8

5

Time used: 00:00:00.01

More Wonderful content: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

Pl/sql often automatically bind variables without the programmer's concern that many of the SQL statements you write automatically take advantage of the binding variables, as shown in the following example:

Sql> Set Timing on

Sql> Declare

2 I number;

3 BEGIN

4 for I in 11000 LOOP

5 INSERT into A VALUES (I);

6 end Loop;

7 End;

8/

The PL/SQL process has completed successfully.

Time used: 00:00:00.12

This code does not need to use the method of binding variables to improve efficiency, Oracle will automatically bind the variables therein.

Sql> CREATE TABLE D (ID varchar (10));

Table has been created.

Time used: 00:00:00.50

Sql> Declare

2 I number;

3 sqlstr varchar (2000);

4 begin

5 for I in 11000 loop

6 Sqlstr: = ' INSERT into D values (' | | To_char (i) | | ' ）';

7 Execute immediate sqlstr;

8 End Loop;

9 End;

10/

The PL/SQL process has completed successfully.

Time used: 00:00:00.68

This code also executes 1000 INSERT statements, but each statement is different, so Oracle will parse each statement hard once, which is much less efficient than the previous paragraph. If you want to be more efficient, you might use a binding variable to change the statement in the loop to

Sql> Declare

2 I number;

3 sqlstr varchar (2000);

4 begin

5 for I in 11000 loop

6 Sqlstr: = ' INSERT INTO d values (: i) ';

7 Execute immediate sqlstr using I;

8 End Loop;

9 End;

10/

The PL/SQL process has completed successfully.

Time used: 00:00:00.18

The efficiency of this implementation is much higher.

In Pl/sql, a reference variable is a reference to a binding variable. However, dynamic SQL is not the case in Pl/sql.

You have to explicitly take advantage of binding variables in Vb,java and other applications.

Support for binding variables is not limited to Oracle, and other RDBMS also supports this feature to SQL Server.