Интересная особенность Oracle SQL

http://atulley.wordpress.com/2011/02/07/using-dual-to-reduce-function-calls-and-getting-your-5-a-day/
  • Перевод
Предлагаю Вашему вниманию перевод интересного на мой взгляд поста про неочевидную особенность Oracle.


Создаем таблицу FRUITS.
CREATE TABLE fruits (fruit_name varchar2(30));

Заполняем таблицу данными: 5 бананов, 7 яблок, 3 черники.
INSERT INTO fruits VALUES ('banana');<br>INSERT INTO fruits VALUES ('banana');<br>INSERT INTO fruits VALUES ('banana');<br>INSERT INTO fruits VALUES ('banana');<br>INSERT INTO fruits VALUES ('banana');<br>INSERT INTO fruits VALUES ('apple');<br>INSERT INTO fruits VALUES ('apple');<br>INSERT INTO fruits VALUES ('apple');<br>INSERT INTO fruits VALUES ('apple');<br>INSERT INTO fruits VALUES ('apple');<br>INSERT INTO fruits VALUES ('apple');<br>INSERT INTO fruits VALUES ('apple');<br>INSERT INTO fruits VALUES ('blueberry');<br>INSERT INTO fruits VALUES ('blueberry');<br>INSERT INTO fruits VALUES ('blueberry');

Чтобы знать сколько раз запускалась наша функция, создаем сиквенс.
CREATE SEQUENCE seq START WITH 1;

Напишем функцию, которая возвращает цвет фрукта (входной параметр) и инкрементирует сиквенс, как индикатор своей работы.
CREATE OR REPLACE FUNCTION get_colour (p_fruit_name IN varchar2)<br>RETURN varchar2<br>IS<br>l_num number;<br>BEGIN<br>SELECT seq.nextval INTO l_num FROM dual;<br><br>CASE p_fruit_name<br>WHEN 'banana' THEN RETURN 'yellow';<br>WHEN 'apple' THEN RETURN 'green';<br>WHEN 'blueberry' THEN RETURN 'blue';<br>END CASE;<br>END get_colour;<br>/

Узнаем цвет каждого фрукта в нашей таблице
SELECT get_colour(fruit_name) FROM fruits;

Вопрос: Что вернет этот запрос?
SELECT seq.nextval FROM dual;

Так, в таблице 15 записей, значит функция будет вызвана 15 раз. И поскольку мы выполняем seq.nextval, то можем ожидать, что результат будет 16. Давайте сбросим сиквенс для проведения еще одного эксперимента
DROP SEQUENCE seq;<br>
CREATE SEQUENCE seq START WITH 1;<br>

И опять используем нашу функцию, чтобы получить цвет фруктов в таблице, но на этот раз обернем ее выражением SELECT FROM dual.
SELECT (SELECT get_colour(fruit_name) FROM dual)<br>FROM fruits;

Вопрос: что на этот раз вернет запрос?
SELECT seq.nextval FROM dual;

Можно предположить, что как и в предыдущий раз функция будет выполнена 15 раз и запрос опять вернет 16. Однако, это не так.
Мы обнаруживаем, что возвращается число 4, а это означает, что функция была вызвана всего 3 раза.
Что же произошло?
Почему функция выполняется всего 3 раза, хотя мы передаем ей каждую запись таблицы, а это 15 фруктов, и при этом в целом запрос возвращает верные данные?
Ответ заключается в механизме кеширования результатов подзапросов — Scalar Subquery Caching.
Результат запроса SELECT some_function(x) FROM dual будет сохранен для каждого значения параметра x.
Таким образом, фактически функция будет выполняться только для разных входных параметров, а т.к. у нас всего три разных фрукта (банан, яблоко, черника),
то и функция будет выполнена всего три раза.
А здесь здесь Том Кайт рассказывает об этом.

Прим. переводчика.
Для полноты картины следует упомянуть о возможности объявить эту функцию как DETERMINISTIC, тогда и в запросе
SELECT get_colour(fruit_name) FROM fruits; она будет выполнена всего 3 раза.
  • +20
  • 23,3k
  • 9
Поделиться публикацией
Похожие публикации
Ой, у вас баннер убежал!

Ну. И что?
Реклама
Комментарии 9
    0
    А если функция должна возвращать разные результаты, даже при разных параметрах, то все будет печально?
      0
      Пардон, при одинаковых параметрах
        +2
        Тогда ее не следует объявлять как DETERMINISTIC, в случае, описанном в посте, ядро Oracle само разберется, что делать.
      +1
      Подзапросы такого вида (скалярные подзапросы):
      SELECT (SELECT get_colour(fruit_name) FROM dual)
      FROM fruits;
      Кэшируются базой для более быстрого доступа и поэтому не происходит дополнительных вызовов.
      В первом случае seq = 16 во во втором 4.
      Не знаю есть ли это в документации (не обращал внимания) просто помню с тренинга.
        0
        Здесь важен именно подзапрос в SELECT'е
        +3
        Вообще вроде вполне известный факт. Кстати, вместо nextval можно было использовать seq.currval, и еще на эту тему вам возможно было бы интересно:
        1. RESULT_CACHE clause,RELIES_ON в 11g
        2. Inline функций в pl/sql в 11g
        3. Рестарты(но тут это дикое кол-во информации)
          0
          Оракл состоит из «интересных неочевидных особенностей» чуть более, чем наполовину :)

          Для полноты картины надо добавить, что объявление функций детерминированными или недетерминированными (и, соответственно, кеширование результатов первых) есть и в других базах. Оптимизатор MySQL с версии 5.1 или даже раньше уже умеет включать кеширование детерминированных функций.
            0
            В MSSQL функции тоже можно объявить детерминированными.
            0
            Аналогичная особенность есть в PostgreSQL — три типа детерминированности функции: IMMUTABLE, STABLE и VOLATILE.
            Подробнее тут: www.postgresql.org/docs/9.0/static/xfunc-volatility.html
            Кстати, поищите в документации по Ораклу — может там тоже третий тип найдется? :)

            Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

            Самое читаемое