Oracle: как обеспечить, чтобы функция в предложении 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() тяжелая, и поэтому я хочу, чтобы запрос достаточно тщательно отфильтровывал результаты поиска, а затем запускал эту функцию только при отфильтрованных результатах.

Как я могу гарантировать, что вся фильтрация произойдет до того, как функция будет выполнена, чтобы она выполняла минимальное количество раз?

Ответ 1

Здесь два метода, где вы можете обмануть Oracle, чтобы не оценивать свою функцию до того, как все другие предложения WHERE были оценены:

Использование rownum

Использование псевдо-столбца rownum в подзапросе заставит Oracle "материализовать" подзапрос. См., Например, эту цепочку askTom для примеров.
```
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" :

Оптимизатор может игнорировать большинство подзапросов, за некоторыми исключениями. Эти исключения включают иерархические подзапросы и подзапросы, содержащие псевдокоманду ROWNUM, один из операторов набора, вложенную агрегированную функцию или коррелированную ссылку на блок запроса, который не является непосредственным внешним блоком запроса подзапроса.
Использование CASE

Используя CASE, вы можете заставить Oracle оценивать вашу функцию только тогда, когда другие условия оцениваются в TRUE. К сожалению, это связано с дублированием кода, если вы хотите использовать другие предложения для использования индексов, как в:
```
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
```

Ответ 2

Существует NO_PUSH_PRED, чтобы сделать это без привлечения оценки rownum (это хороший трюк) в этом процессе!

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

Ответ 3

Обычно вы хотите избежать форсирования определенного порядка выполнения. Если данные или запрос изменяются, ваши подсказки и трюки могут иметь неприятные последствия. Обычно лучше предоставлять полезные метаданные для Oracle, чтобы они могли принимать правильные решения для вас.

В этом случае вы можете обеспечить лучшую статистику оптимизатора о функции с помощью ASSOCIATE STATISTICS.

Например, если ваша функция очень медленная, потому что она должна читать 50 блоков каждый раз, когда она вызывается:

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

По умолчанию Oracle предполагает, что функция выберет строку 1/20 раз. Oracle хочет удалить столько строк, сколько скоро насколько возможно, изменение избирательности должно сделать функцию менее вероятной для выполнения первой:

associate statistics with functions
check_if_player_is_eligible default selectivity 90;

Но это вызывает некоторые другие проблемы. Вы должны выбрать избирательность для ВСЕХ возможных условий, 90%, конечно, не всегда будут точными. Стоимость ввода-вывода - это количество выбранных блоков, но стоимость процессора - это "машинные инструкции", что именно это означает?

Существуют более сложные способы настройки статистики, например, с помощью Oracle Data Cartridge Extensible Optimizer. Но картридж данных, вероятно, является одним из самых сложных функций Oracle.

Ответ 4

Вы не указали, является ли player.player_name уникальным или нет. Можно предположить, что это так, а затем база данных должна вызывать функцию по крайней мере один раз для каждой записи результата.

Но, если player.player_name не уникально, вы бы хотели минимизировать вызовы до count (different player.player_name). Поскольку (Ask) Tom показывает в Oracle Magazine, эффективный способ скалярного кэша подзапроса. p >

Вам понадобится перевести вызов функции в подзапрос, чтобы использовать скалярный кэш подзапроса:

SELECT players.*
FROM   players,
      (select check_if_player_is_eligible(player.player_name) eligible) subq
WHERE  player_name LIKE '%K%'
  AND  player_rank < 10
  AND  ROWNUM >= 1
  AND  subq.eligible = 1

Ответ 5

Поместите исходный запрос в производную таблицу, затем поместите дополнительный предикат в предложение where производной таблицы.

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