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?

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

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

You usually want to avoid forcing a specific order of execution. If the data or the query changes, your hints and tricks may backfire. It's usually better to provide useful metadata to Oracle so it can make the correct decisions for you.

In this case, you can provide better optimizer statistics about the function with ASSOCIATE STATISTICS.

For example, if your function is very slow because it has to read 50 blocks each time it is called:

associate statistics with functionscheck_if_player_is_eligible default cost(1000 /*cpu*/, 50 /*IO*/, 0 /*network*/);

By default Oracle assumes that a function will select a row 1/20th of the time. Oracle wants to eliminate as many rows as soonas possible, changing the selectivity should make the function less likely to be executed first:

associate statistics with functionscheck_if_player_is_eligible default selectivity 90;

But this raises some other issues. You have to pick a selectivity for ALL possible conditions, 90% certainly won't always be accurate. The IO cost is the number of blocks fetched, but CPU cost is "machine instructions used", what exactly does that mean?

There are more advanced ways to customize statistics,for example using the Oracle Data Cartridge Extensible Optimizer. But data cartridge is probably one of the most difficult Oracle features.