以下是两种方法,您可以在所有其他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