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.

If you know that "Oracle does the cost optimization and decides the order of condition execution" why do you still think that "Oracle executes the condition1 ..." — David דודו Markovitz, Dec 21 '16 at 06:29
well when I remove the 1=0 condition, I get the error but not otherwise. So that would mean that Oracle skipped execution, right? — HitchHiker, Dec 21 '16 at 06:31
I though my question made it clear: `make it execute all the conditions so that error is thrown` — HitchHiker, Dec 21 '16 at 06:33
But is doesn't mean it executes the predicates by their order. I'm opening an answer in order to give you a clear example. — David דודו Markovitz, Dec 21 '16 at 06:34
Sorry, but this make no sense. If you know that you have data issue you should handled it at the source or in the code and not wait for exceptions to occur. — David דודו Markovitz, Dec 21 '16 at 06:38
@DuduMarkovitz I edited my post, making it clear that regardless of which predicate is executed first, rest are not executed - my real issue. — HitchHiker, Dec 21 '16 at 06:40
what do you think Oracle should execute after this condition? where 1=0 there is no records after this clause, you are doing something wrong if you thinking that way — TheName, Dec 21 '16 at 07:05
Are you trying to solve real problem or is it a theoretical question? What is your goal? — Marcin Wroblewski, Dec 21 '16 at 09:01
@MarcinWroblewski A real problem made me question this working. But either point of view, real life or theoretical is welcome here — HitchHiker, Dec 21 '16 at 09:20
"so that error is thrown instead of the misleading output?" ... the output is not misleading at all. No rows would be output, which is the correct result. — David Aldridge, Dec 21 '16 at 10:17

score 1 · Answer 1 · answered Dec 21 '16 at 10:12

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.

Thank you for the `materialize` hint pointer :) I will do some research around that. — HitchHiker, Dec 21 '16 at 10:36

score 0 · Answer 2 · answered Dec 21 '16 at 07:00

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.

a huge NO! nothing ensures you evaluation of predicates by their order. — David דודו Markovitz, Dec 21 '16 at 07:04

score 0 · Answer 3 · answered Dec 21 '16 at 07:10

0

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?

answered Dec 21 '16 at 07:10

David דודו Markovitz

42,900
6
64
88

Thank you for the response. I am working on 12c, so obviously that hint can not be used. – HitchHiker Dec 21 '16 at 09:25

score 0 · Answer 4 · answered Dec 21 '16 at 09:00

0

select  *

from    <table_name>

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

answered Dec 21 '16 at 09:00

David דודו Markovitz

42,900
6
64
88

1

I think you are doing something terribly wrong here, but here it is. – David דודו Markovitz Dec 21 '16 at 09:01
Yes, the next question would be "my performance is terrible -- how do I optimise this?" – David Aldridge Dec 21 '16 at 10:15
@DavidAldridge :-) – David דודו Markovitz Dec 21 '16 at 10:17

Use Hints for execution of all the conditions in where clause

4 Answers4