The Vertica Database Forums

Posted: **Wed Mar 20, 2013 6:49 pm**

Hello,

Bind variables are "substitution" variables that are used as place of literals (such as 111, 234, 567, etc.) that have the effect of sending exactly the same SQL to Oracle every time the query is executed to avoid hard parses. Basically its a way to use the same execution plan regardless of a literal value.

Example:

Instead of doing this, we would do this:

Code: Select all

SQL> select * from emp where empno = 25;
SQL> select * from emp where empno = 26;
SQL> select * from emp where empno = 27;
[code]
We would do this:

[code]
SQL> variable empno number
SQL> exec :empno := 25
SQL> select * from emp where empno = :empno;
SQL> exec :empno := 26
SQL> select * from emp where empno = :empno;
SQL> exec :empno := 27
SQL> select * from emp where empno = :empno;

Dose this concept apply in Vertica?

Posted: **Wed Mar 20, 2013 11:53 pm**

Hi ,

I don't think so.

Vertica is for BI and DWH and not for OLTP
Bind variable are not suited for Vertica.

Oracle use binds in order to keep the shared pool from exploding.
Even in Oracle when you are using it for BI and DWH it's better NOT to use binds ( as long as the number of queries will be relativ small )
when not using binds in Oracle you are giving the optimzer more information about the predicate so it will get better estimates.

So in short ... No

Zvika

Posted: **Thu Mar 21, 2013 12:22 pm**

Zvika, thanks for the response! What you said is kind of what I thought, but now I know for sure.

The Vertica Database Forums

Are Bind Variables Relevant in Vertica?

Are Bind Variables Relevant in Vertica?

Re: Are Bind Variables Relevant in Vertica?

Re: Are Bind Variables Relevant in Vertica?