Use "case when end" construct, which does the short-circuit. Test on a fairly big table such as dba_source to see the timing:

SQL> var a number
SQL> exec :a := 9

PL/SQL procedure successfully completed.

SQL> set timing on
SQL> select case when (:a>10) then (select count(*) from dba_source) end from dual;

CASEWHEN(:A>10)THEN(SELECTCOUNT(*)FROMDBA_SOURCE)END
----------------------------------------------------


Elapsed: 00:00:00.00
SQL> exec :a := 11

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL> select case when (:a>10) then (select count(*) from dba_source) end from dual;

CASEWHEN(:A>10)THEN(SELECTCOUNT(*)FROMDBA_SOURCE)END
----------------------------------------------------
                                               77483

Elapsed: 00:00:11.01

SQL>

The alternative others suggested works too, i.e. use PL/SQL to test your variable first. But if you can't use PL/SQL for any reason, then the above shown may be the only solution.

Yong Huang

> Yong,
>   Thanks. Is there a way to know the order of execution for something like :
> SQL> var a number
> SQL> explain plan for
>  select a
>  from mytable
>  where a = :a;
> and :a > 10
> 
> basically i want to execute the query only if the :a is greater than 10. When
> does :a > 10 get executed. Before or after a = :a?  I know one way is to put
> a if at the beginning. That was my original question. Thanks



SEE ALSO
* NVL doesn't short-circuit 
http://groups.google.com/groups?threadm=MjApb.24%240X5.348%40news.oracle.com&rnum=1
* COALESCE short-circuits (See message 8 and function definitions in message 4)
http://groups.google.com/group/comp.databases.oracle.server/browse_frm/thread/9b7eb6fe16906d6b/5c39f087f90894cd