Are Bind Variables Relevant in Vertica?

Moderator: NorbertKrupa

Post Reply
User avatar
Julie
Master
Master
Posts: 221
Joined: Thu Apr 19, 2012 9:29 pm

Are Bind Variables Relevant in Vertica?

Post by Julie » 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?
Thanks,
Juliette

zvika
Beginner
Beginner
Posts: 25
Joined: Thu Apr 19, 2012 7:55 am

Re: Are Bind Variables Relevant in Vertica?

Post by zvika » 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

User avatar
Julie
Master
Master
Posts: 221
Joined: Thu Apr 19, 2012 9:29 pm

Re: Are Bind Variables Relevant in Vertica?

Post by Julie » 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. 8-)
Thanks,
Juliette

Post Reply

Return to “Vertica Performance Tuning”