Use Hints for execution of all the conditions in where clause

Question

Consider a query like :

select * from <table_name>
where <condition1> and <condition2> and <condition3>;

Suppose Oracle executes the condition (any condition) and if not true, it does not execute the other conditions. So if I have other conditions that have logical error then it is not thrown. For example:

select count(*) from dual 
where 1=0 and 
'stack' = SUBSTR('stackoverflow','k', 3);

Oracle returns 0. Now remove the 1=0 condition, we get ORA-01722: invalid number.

I know that Oracle does the cost optimization and decides the order of condition execution. So how do I override this and make it execute all the conditions so that error is thrown instead of the misleading output? Can we use hints? I am new to the hints concept, so some example would be great.

Answer 1

You can check condition one by one or simply using ORkeyword to check only errors.

    select count(*) from dual 
    where 'stack' = SUBSTR('stackoverflow',1,5) and  1=0
   --  True and False return 0


    select count(*) from dual 
    where 'stack' = SUBSTR('stackoverflow','a',5) and  1=0
    --  True and False  return 0


   select count(*) from dual 
   where 'stack' = SUBSTR('stackoverflow',1,5) and  1=1
   --  True and True Return 1

here oracle first checks wrong condition 1=0 then its not checking another condition which also have logical error like invalid number 'a' in substr syntax.

Answer 2

The XY problem is asking about your attempted solution rather than your actual problem. This leads to enormous amounts of wasted time and energy, both on the part of people asking for help, and on the part of those providing help.

xyproblem.info

---

FYI

There used to be an ORDERED_PREDICATES hint
Optimizer Hints

That was deprecated in 10g
What's New in Oracle Performance?

Answer 3

I don't know if that solves your "problem" whatever it is, but here's one approach. This query is more or less equivalent to yours, and raises the error.

SQL> with t as (
  2  select /*+ materialize */ * from   dual
  3   where  'stack' = substr('stackoverflow', 'k', 3)
  4  )
  5  select * from t where 1 = 0;
 where  'stack' = substr('stackoverflow', 'k', 3)
                                          *
ERROR at line 3:
ORA-01722: invalid number

However no guarantee. materialize is just a hint and as such it may not be obeyed.

Answer 4

select  *

from    <table_name>

where   case 
            when    <condition1> 
            then    case
                        when    <condition2> 
                        then    case
                                    when    <condition3>
                                    then    1
                                end
                    end
        end = 1