Difference Between "&" and "&&" in oracle

Question

I'm studying the differences between bind variables(:) and ampersand operator(& / substitution variables). I realized that "double ampersand is stored variable values and reused these values where it is the same session.".

According to this fact, if I use the double ampersand, then I can avoid hard parsing or not?

Answer 1

Title asks for difference between & and &&.

Both are used with substitution variables (as you already know). Here's a simple and rather dummy example, but will illustrate the difference.

First option: only one & used with the same substitution variable name; you're required to enter the same value twice (or, as many times as needed):

SQL> set ver off
SQL>
SQL> select e.ename, e.job, e.sal
  2  from emp e join dept d on e.deptno = d.deptno
  3  where e.deptno = &par_deptno
  4    and d.deptno = &par_deptno;
Enter value for par_deptno: 10   --> this is from line #3
Enter value for par_deptno: 10   --> this is from line #4

ENAME      JOB              SAL
---------- --------- ----------
CLARK      MANAGER         2450
KING       PRESIDENT       5000
MILLER     CLERK           1300

Re-run the same query - you'll again be prompted:

SQL> /
Enter value for par_deptno: 30
Enter value for par_deptno: 30

ENAME      JOB              SAL
---------- --------- ----------
ALLEN      SALESMAN        1600
WARD       SALESMAN        1250
MARTIN     SALESMAN        1250
BLAKE      MANAGER         2850
TURNER     SALESMAN        1500
JAMES      CLERK            950

6 rows selected.

SQL>

Second option: &&, which results in only one prompt to enter the value:

SQL> select e.ename, e.job, e.sal
  2  from emp e join dept d on e.deptno = d.deptno
  3  where e.deptno = &&par_deptno
  4    and d.deptno = &&par_deptno;
Enter value for par_deptno: 10

ENAME      JOB              SAL
---------- --------- ----------
CLARK      MANAGER         2450
KING       PRESIDENT       5000
MILLER     CLERK           1300

What happens if I re-run it? The same (old!) value will be used, without any prompt:

SQL> /

ENAME      JOB              SAL
---------- --------- ----------
CLARK      MANAGER         2450
KING       PRESIDENT       5000
MILLER     CLERK           1300

SQL>

If you want to use another substitution variable value, undefine it and then run the query:

SQL> undefine par_deptno
SQL> /
Enter value for par_deptno: 30

ENAME      JOB              SAL
---------- --------- ----------
ALLEN      SALESMAN        1600
WARD       SALESMAN        1250
MARTIN     SALESMAN        1250
BLAKE      MANAGER         2850
TURNER     SALESMAN        1500
JAMES      CLERK            950

6 rows selected.

SQL>

---

As of question you posted in the body (about hard parsing): substitution variables, as far as I can tell, won't help in that. Bind variables would.

Have a look at what BluShadow wrote on OTN Forums (PL/SQL 101 : Substitution vs. Bind Variables):

So, in summary, Substitution variables are variables that the user interface detects and prompts for text to substitute into the code before submitting it to the database, and Bind variables are placeholders in queries that allow SQL queries to be soft parsed rather than hard parsed when the query is re-used, help prevent SQL injection, and allow for the values to be supplied easily and seamlessly within the code issuing it.

---

Also, note that substitution variables can't be used everywhere; it depends on a tool you use. For example, SQL*Plus works with them, TOAD doesn't recognize them, etc.

Answer 2

A bind variable is prefixed by : and is evaluated by the SQL engine in the database.

A substitution variable is prefixed by & (or &&) and is evaluated by the client application you are using to connect to the database (i.e. SQL*Plus, SQL Developer or other clients that support the same syntax and not all clients support the substitution variable syntax) and is NOT understood by the SQL engine in the database (and would result in a syntax error if the client sent it to the database without pre-processing it).

if I use the double ampersand, then I can avoid hard parsing or not?

A substitution variable will be treated like you have done a find-replace on the source-code of the script you are using and is done by the client application before any code is sent to the database.

The database then has to parse the statements you send it and will need to do a hard-parse of any statement that it has not seen before (including changes in white-space). If you are always using a different statement then the database will always have to do a hard-parse the statement each time; it can do a soft-parse of statements that it has cached.

Bind variables may prevent this re-parsing; substitution variables will not prevent it (as they are handled on the client-side).

Answer 3

Others have sufficiently answered that using substitution variables (& and && won't help with your parsing concern. But there's a problem with bind variables (:), too.

If you are using SQLPlus, you should know that there is no way to avoid a hard parse if your variable is a new value. SQLPlus isn't a programming environment. It's more like a thin client, just an input/output screen to the database. This impacts its use of bind variables (:) in a significant way. To use a bind variable in SQLPlus, you must declare it and then assign its value:

SQL> var junk number
SQL> exec :junk := 5;

PL/SQL procedure successfully completed.

Behind the scenes, it uses PL/SQL to do the assignment. That line exec :junk := 5; gets sent to the database as:

BEGIN :junk := 5; END;

Which is an anonymous PL/SQL block requiring a cursor in the shared pool just like a normal SQL does, and because the 5 will be a literal, as the value changes, this assignment block will create new cursors, hard parsing every time.

So, a SQLPlus bind variable providing input (rather than receiving output) can't help you avoid hard parsing. That being said, if the SQL that will use the bind variable is complex with lots of joins, etc... then you will avoid hard parsing that SQL by using binds, and presumably it's much more expensive and requires a lot more parsing time and memory in the shared pool than the assignment block does. So there's benefit to using bind variables even for input... but you can't avoid the hard parse of the assignment itself.

For this reason, if the intent is to insert data into Oracle and you want to use bind variables for the insert columns, it will wreak havoc on the shared pool. This makes SQLPlus a bad tool for loading data into the database which normally requires a lot of successive inputs. If however you simply want to do a query and you need to provide some inputs that will be used in the query predicates, then presumably you won't be running this too frequently that the hard parsing becomes an issue. A few dozen iterations is one thing, thousands is something else entirely.

SQLPlus is a handy tool for doing ad-hoc work or occasional scripting, and it can function fairly decently to extract data at modest volumes. We all appreciate its ubiquity. But it is not a good tool for programmatic use at any appreciable volume of executions (data crunching). For that, you'd want a real, fully fledged programming environment with a data connector that uses OCI. Such an environment will be fully-featured and do a lot of things SQLPlus cannot do, including assigning client-side bind variables without using PL/SQL to do so. Then you can work with bulk assignments without any hard parsing.

If you use SQLPlus the way it was intended, for ad-hoc or occasional scheduled script use, then the volume is so low nobody concerns themselves with the issue of parsing. Just use it and enjoy.