Oracle deterministic что это

Обновлено: 25.06.2024

Тем на данный вопрос много. но повторюсь.

в доке очень сухо сказано:

Specify DETERMINISTIC to indicate that the function returns the same result value whenever it is called with the same values for its arguments.

You must specify this keyword if you intend to call the function in the expression of a function-based index or from the query of a materialized view that is marked REFRESH FAST or ENABLE QUERY REWRITE. When Oracle Database encounters a deterministic function in one of these contexts, it attempts to use previously calculated results when possible rather than reexecuting the function. If you subsequently change the semantics of the function, then you must manually rebuild all dependent function-based indexes and materialized views.

Do not specify this clause to define a function that uses package variables or that accesses the database in any way that might affect the return result of the function. The results of doing so will not be captured if Oracle Database chooses not to reexecute the function.

The following semantic rules govern the use of the DETERMINISTIC clause:

You can declare a top-level subprogram DETERMINISTIC.
*

You can declare a package-level subprogram DETERMINISTIC in the package specification but not in the package body.
*

You cannot declare DETERMINISTIC a private subprogram (declared inside another subprogram or inside a package body).
*

A DETERMINISTIC subprogram can call another subprogram whether the called program is declared DETERMINISTIC or not.

ну не совсем сухо. Первая часть вроде отвечает.. но простите мне не очень понятно.

Мне не понятно одно.

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

т.е. Можно было бы данные подход использовать для получения стабильного результата. следующего примера:
Допустим есть объекты и эти объекты в разной степени доступны пользователям.
если проверку правила организовать типа:

Предлагаю Вашему вниманию перевод интересного на мой взгляд поста про неочевидную особенность Oracle.

Создаем таблицу FRUITS.

Заполняем таблицу данными: 5 бананов, 7 яблок, 3 черники.
INSERT INTO fruits VALUES ( 'banana' );
INSERT INTO fruits VALUES ( 'banana' );
INSERT INTO fruits VALUES ( 'banana' );
INSERT INTO fruits VALUES ( 'banana' );
INSERT INTO fruits VALUES ( 'banana' );
INSERT INTO fruits VALUES ( 'apple' );
INSERT INTO fruits VALUES ( 'apple' );
INSERT INTO fruits VALUES ( 'apple' );
INSERT INTO fruits VALUES ( 'apple' );
INSERT INTO fruits VALUES ( 'apple' );
INSERT INTO fruits VALUES ( 'apple' );
INSERT INTO fruits VALUES ( 'apple' );
INSERT INTO fruits VALUES ( 'blueberry' );
INSERT INTO fruits VALUES ( 'blueberry' );
INSERT INTO fruits VALUES ( 'blueberry' );
Чтобы знать сколько раз запускалась наша функция, создаем сиквенс. Напишем функцию, которая возвращает цвет фрукта (входной параметр) и инкрементирует сиквенс, как индикатор своей работы.

CREATE OR REPLACE FUNCTION get_colour (p_fruit_name IN varchar2)
RETURN varchar2
IS
l_num number;
BEGIN
SELECT seq.nextval INTO l_num FROM dual;

CASE p_fruit_name
WHEN 'banana' THEN RETURN 'yellow' ;
WHEN 'apple' THEN RETURN 'green' ;
WHEN 'blueberry' THEN RETURN 'blue' ;
END CASE ;
END get_colour;
/

Узнаем цвет каждого фрукта в нашей таблице

Так, в таблице 15 записей, значит функция будет вызвана 15 раз. И поскольку мы выполняем seq.nextval, то можем ожидать, что результат будет 16. Давайте сбросим сиквенс для проведения еще одного эксперимента
DROP SEQUENCE seq;
CREATE SEQUENCE seq START WITH 1;
И опять используем нашу функцию, чтобы получить цвет фруктов в таблице, но на этот раз обернем ее выражением SELECT FROM dual.
SELECT ( SELECT get_colour(fruit_name) FROM dual)
FROM fruits;
Можно предположить, что как и в предыдущий раз функция будет выполнена 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 раза.


DETERMINISTIC в PL/SQL

Функция называется детерминированной, если при одном наборе параметров IN и IN OUT она всегда возвращает одно и то же значение. Отличительной чертой детерминированных функций является отсутствие побочных эффектов: все изменения, вносимые программой, отражаются в списке параметров.

Пример детерминированной функции, которая представляет собой простую инкапсу­ляцию substr :

Если при вызове функции передаются, например, строка «abcdef» (string_in), число 3 (start_in) и 5 (end_in), то сколько бы раз вы ни вызывали функцию betwnStr с этим на­бором параметров, она всегда будет возвращать строку « cde ». Тогда почему бы Oracle не сохранить результат, связанный с конкретным набором аргументов? Ведь при следующем вызове функции с теми же параметрами можно получить результат, не выполняя код функции! Чтобы добиться подобного эффекта, включите предложение DETERMINISTIC в заголовок функции:

Решение об использовании сохраненной копии возвращаемого результата (если такая копия доступна) принимается оптимизатором запросов Oracle. Сохраненные копии могут браться из материализованного представления, функционального индекса или повторного вызова одной функции в команде SQL.

Функция должна быть объявлена с ключевым словом DETERMINISTIC , чтобы она могла вызываться в выражении функционального индекса или из запроса материализованного представления с пометкой REFRESH FAST или ENABLE QUERY REWRITE . Кроме того, кэширование входных данных и результатов детерминированной функции выполняется только тогда, когда функция вызыва­ется в команде SQL .

Детерминированная функция может улучшить производительность команд SQL , вы­зывающих такие функции. В Oracle нет надежного способа проверки отсутствия побочных эффектов у функций, объявленных детерминированными. Поэтому при использовании таких функций вся ответственность за последствия возлагается на программиста. Детерминированная функция не должна основываться на переменных пакета и иметь право изменять ре­зультирующие значения в базе данных.

Предлагаю Вашему вниманию перевод интересного на мой взгляд поста про неочевидную особенность Oracle.

Создаем таблицу FRUITS.

CREATE TABLE fruits (fruit_name varchar2(30));

Заполняем таблицу данными: 5 бананов, 7 яблок, 3 черники.


INSERT INTO fruits VALUES ('banana');
INSERT INTO fruits VALUES ('banana');
INSERT INTO fruits VALUES ('banana');
INSERT INTO fruits VALUES ('banana');
INSERT INTO fruits VALUES ('banana');
INSERT INTO fruits VALUES ('apple');
INSERT INTO fruits VALUES ('apple');
INSERT INTO fruits VALUES ('apple');
INSERT INTO fruits VALUES ('apple');
INSERT INTO fruits VALUES ('apple');
INSERT INTO fruits VALUES ('apple');
INSERT INTO fruits VALUES ('apple');
INSERT INTO fruits VALUES ('blueberry');
INSERT INTO fruits VALUES ('blueberry');
INSERT INTO fruits VALUES ('blueberry');


Чтобы знать сколько раз запускалась наша функция, создаем сиквенс.


CREATE SEQUENCE seq START WITH 1;


Напишем функцию, которая возвращает цвет фрукта (входной параметр) и инкрементирует сиквенс, как индикатор своей работы.


CREATE OR REPLACE FUNCTION get_colour (p_fruit_name IN varchar2)
RETURN varchar2
IS
l_num number;
BEGIN
SELECT seq.nextval INTO l_num FROM dual;

CASE p_fruit_name
WHEN 'banana' THEN RETURN 'yellow';
WHEN 'apple' THEN RETURN 'green';
WHEN 'blueberry' THEN RETURN 'blue';
END CASE;
END get_colour;
/

Узнаем цвет каждого фрукта в нашей таблице

SELECT get_colour(fruit_name) FROM fruits;

Вопрос: Что вернет этот запрос?

SELECT seq.nextval FROM dual;


Так, в таблице 15 записей, значит функция будет вызвана 15 раз. И поскольку мы выполняем seq.nextval, то можем ожидать, что результат будет 16. Давайте сбросим сиквенс для проведения еще одного эксперимента


DROP SEQUENCE seq;
CREATE SEQUENCE seq START WITH 1;

И опять используем нашу функцию, чтобы получить цвет фруктов в таблице, но на этот раз обернем ее выражением SELECT FROM dual.


SELECT (SELECT get_colour(fruit_name) FROM dual)
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 раза.

Читайте также: