Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

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 ?

like image 848
Daud Avatar asked Dec 08 '11 08:12

Daud


People also ask

Can we use analytical function in WHERE clause?

Analytical functions can be used to calculate percentages or top-N results in a group. Analytical functions can appear only in the select list of a query or in the ORDER BY clause. They cannot appear in WHERE, ON, HAVING, or GROUP BY clauses.

Does order of WHERE clause matter in Oracle?

The order of execution is important because it has a direct impact on the performance of your queries. In general, the more records that can be eliminated via the WHERE clause, the faster the query will execute.

Can CASE statement be used in WHERE clause Oracle?

You can use a CASE expression in any statement or clause that accepts a valid expression. For example, you can use the CASE expression in statements such as SELECT , UPDATE , or DELETE , and in clauses like SELECT , WHERE , HAVING , and ORDDER BY .

Can we use having and WHERE clause together in Oracle?

Introduction to the Oracle HAVING clause In this statement, the HAVING clause appears immediately after the GROUP BY clause. If you use the HAVING clause without the GROUP BY clause, the HAVING clause works like the WHERE clause. Note that the HAVING clause filters groups of rows while the WHERE clause filters rows.


2 Answers

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
    
like image 200
Vincent Malgrat Avatar answered Oct 16 '22 08:10

Vincent Malgrat


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
like image 21
Alessandro Rossi Avatar answered Oct 16 '22 09:10

Alessandro Rossi