Does subqueries "short-circuit" based on other conditions? I have a slightly costly case when exists (...) then 1 else 0 end sub-query in the select clause. The sub-query is only really needed if another column in the main query has a specific value (can think of it as a has_child flag).

As the main query returns tens of thousands of rows and runs frequently, it's a prime target for optimisation.

I have tried adding has_child = 1 into the case when clause, but that did not change the explain plan at all (understandably). But real query test feels quicker. Unfortunately, I cannot gather statistics on the production database, so I have no idea if the improvement is real or not.

So is Oracle actually able to skip/stop the join/sub-query if the condition guarding it is already false?

The explain plan looks like:
Yes, they do.

select
  case 
    when (1 = 0 and (select 1/0 from dual) = 1) 
    then 1 
    else 0
  end as c 
from dual;

         C
----------
         0


Here 1 = 0 is false, so the second argument of the AND is not executed, luckily for us, because it would be a division by zero. With 1 = 1 the second part is executed and we receive the expected error:

select
  case 
    when (1 = 1 and (select 1/0 from dual) = 1) 
    then 1 
    else 0
  end as c 
from dual;

ERROR at line 3:
ORA-01476: divisor is equal to zero