Use of Bind variables in Oracle when there are no variables

Question

Use of Bind variables in Oracle when there are no variables

Navigation

#1 by (1 votes)

0

Is it useful to use bind variables when there are no variables?

For example

SELECT count(1) as cantidad
FROM MYTABLA
WHERE CAMPO1=5

This query will always be executed the same, 5 will always be 5 and will not change.

As far as Oracle keeps the execution plan of this setentencia and for the next time an execution is requested to Oracle, Oracle instead of analyzing Query will look in its area of SGA and will proceed.

Does it have any advantage to use a Bind variable that will never change its value?

This I ask why an oracle expert said:

"Al usar constantes en cada ejecución, causa que se genere un sql id diferente debido al hard parse en la base de datos, haciendo imposible adjuntar un profile a la query."

oracle oracle-11g

asked by Kaltresian 27.07.2016 в 14:35

source

1 answer

How to Show an Image in Modal Window Bootstrap iTextSharp extract pdf text by coordinates

score 1 · Answer 1

What you say is correct. If the value is not variable, there is no advantage in using a parameter bind.

What the expert meant is that if you run the query several times with constants but different values , then this would cause problems. For example, if you run the following queries:

SELECT count(1) as cantidad
FROM MYTABLA
WHERE CAMPO1=5

SELECT count(1) as cantidad
FROM MYTABLA
WHERE CAMPO1=50

SELECT count(1) as cantidad
FROM MYTABLA
WHERE CAMPO1=500

Because you use 3 different constants , Oracle considers that each statement is different, and this forces you to do a hard parse for each one, which has a negative effect on concurrency and memory.

In that case, it would be better to use a bind variable like this:

SELECT count(1) as cantidad
FROM MYTABLA
WHERE CAMPO1=:campoParam

But this does not apply to your case. As the value never changes, Oracle would only make the hard parse the first time you execute the query and no more.