oracle : how to ensure that a function in the where clause will be called only after all the remaining where clauses have filtered the result?

Question

I am writing a query to this effect:

select * 
from players 
where player_name like '%K% 
  and player_rank<10 
  and check_if_player_is_eligible(player_name) > 1;

Now, the function check_if_player_is_eligible() is heavy and, therefore, I want the query to filter the search results sufficiently and then only run this function on the filtered results.

How can I ensure that the all filtering happens before the function is executed, so that it runs the minimum number of times ?

Answer 1

Here's two methods where you can trick Oracle into not evaluating your function before all the other WHERE clauses have been evaluated:

1. Using rownum

   Using the pseudo-column rownum in a subquery will force Oracle to "materialize" the subquery. See for example this askTom thread for examples.

   SELECT *
     FROM (SELECT *
              FROM players
             WHERE player_name LIKE '%K%'
               AND player_rank < 10
               AND ROWNUM >= 1)
    WHERE check_if_player_is_eligible(player_name) > 1
   

   Here's the documentation reference "Unnesting of Nested Subqueries":

   The optimizer can unnest most subqueries, with some exceptions. Those exceptions include hierarchical subqueries and subqueries that contain a ROWNUM pseudocolumn, one of the set operators, a nested aggregate function, or a correlated reference to a query block that is not the immediate outer query block of the subquery.

2. Using CASE

   Using CASE you can force Oracle to only evaluate your function when the other conditions are evaluated to TRUE. Unfortunately it involves duplicating code if you want to make use of the other clauses to use indexes as in:

   SELECT *
     FROM players
    WHERE player_name LIKE '%K%'
      AND player_rank < 10
      AND CASE 
            WHEN player_name LIKE '%K%'
             AND player_rank < 10 
               THEN check_if_player_is_eligible(player_name) 
          END > 1
   

Answer 2

There is the NO_PUSH_PRED hint to do it without involving rownum evaluation (that is a good trick anyway) in the process!

SELECT /*+NO_PUSH_PRED(v)*/*
FROM (
        SELECT *
        FROM players
        WHERE player_name LIKE '%K%'
            AND player_rank < 10
    ) v
 WHERE check_if_player_is_eligible(player_name) > 1