oracle：如何确保where子句中的函数只有在所有剩余的where子句过

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

现在,函数check_if_player_is_eligible()很重,因此,我希望查询足够过滤搜索结果,然后只对过滤的结果运行此功能.

如何确保所有过滤在函数执行之前发生,以便运行最少次数？

>使用rownum

在子查询中使用伪列rownum将强制Oracle“实现”子查询.参见例如这个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

以下是文档编号“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.

>使用CASE

使用CASE,当其他条件被评估为TRUE时,您可以强制Oracle仅评估您的功能.不幸的是,如果要使用其他子句来使用索引,那么它涉及复制代码：

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!