oracle中where中使用函数,oracle：如何确保where子句中的函数只有在所有剩余的where子句过滤结果之后才被调用？...

以下是两种方法,您可以在所有其他WHERE子句被评估之前将Oracle强制转换为不评估您的功能：

>使用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

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