Использование полиморфных табличных функций в Oracle

Привет! Меня зовут Зураб Диаконашвили, я разработчик в компании SM Lab. Сегодня поговорим об использовании полиморфных табличных функций в Oracle и рассмотрим их работу на примерах.

При работе с Oracle используются SQL-запросы – они помогают управлять базами данных, представленными в виде таблиц. Мы получили задачу на динамическое добавление полей в стационарный набор полей сущности. Для этого мы решили попробовать PTF-функцию (пользовательскую табличную функцию, которая вызывается в предложении FROM) и сравнить результат её работы с обычным SQL PIVOT и JavaScript.

Полиморфные табличные функции (PTF) являются частью стандарта SQL: 2016. В Oracle они представлены в версии 18c.

Что необходимо знать при работе с PTF:

• PTF – это определяемые пользователем табличные функции, которые могут быть вызваны в предложении FROM;

• тип строки результата (набор колонок) не объявляется при создании функции;

• тип строки результата может зависеть от аргументов функции и, следовательно, от точного синтаксиса вызова;

• PTF может использовать произвольное количество параметров, Oracle же требует один обязательный параметр – исходная таблица. Это может быть как таблица в БД, так и CTE (with-выражение).

С помощью PTF мы можем манипулировать как набором колонок итоговой таблицы, так и набором строк, т.е. можем добавлять/удалять колонки и добавлять/удалять строки.

Также следует обратить внимание, что в PTF-пакете может быть объявлено три процедуры и одна функция:

• DESCRIBE function (обязательно);

• FETCH_ROWS procedure (опционально);

• OPEN procedure (опционально);

• CLOSE procedure (опционально).

В обязательной функции DESCRIBE (описательная часть) указываем, какие колонки мы добавляем и исключаем из исходного набора данных, а также какие колонки должны быть доступны для чтения в процедуре FETCH_ROWS.

Из процедур основной является FETCH_ROWS, в которой мы получаем вычисленные отображаемые данные при выполнении SQL-запроса. OPEN и CLOSE – это необязательные процедуры. Их смысл в инициализации данных, которые хранятся в store для дальнейшего использования, и финализации расчетов (при необходимости) соответственно.

Время выполнения процедур/функций PTF-пакета:

• функция DESCRIBE выполняется при компиляции SQL-запроса, т.е. для каждого SQL_ID функция DESCRIBE выполняется один единственный раз;

• остальные процедуры вызываются в процессе выполнения SQL-запроса.

Рассмотрим на примерах.

Пример 1. Самый простой пример

/* Скрипт генерации тестовых данных */
create table t (
    a number,
    b number,
    c number
);
insert into t values (1,2,3);
insert into t values (4,5,6);
insert into t values (7,8,9);
commit;

/********************************************************************/
/* Пакет для работы с PTF*/
create or replace package ptf_package_1 as

function my_ptf
    (
        -- исходная таблица - это может быть как таблица БД, так и CTE (with t as () ...)
        p_tab in out table
    ) return
        table pipelined
        row   polymorphic
        using ptf_package_1;

function describe
    (
        -- исходная таблица
        p_tab in out dbms_tf.table_t
    ) return dbms_tf.describe_t;

end ptf_package_1;
/
create or replace package body ptf_package_1 as

function describe
    (
        -- исходная таблица
        p_tab in out dbms_tf.table_t
    ) return dbms_tf.describe_t
is
begin
    return null;
end;

end ptf_package_1;
/

Это примитивный, вырожденный пример, в нем PTF ничего не изменяет, а возвращает исходные данные как есть.

В этом пакете объявлена функция my_ptf, которую мы будем вызывать в SQL-запросе. У этой функции есть только заголовок и нет тела.

Мы указываем в объявлении функции:

• table pipelined;

• row|table polymorphic;

• using <имя пакета>  (пакет, в котором описаны процедуры, необходимые для работы этой функции).

Тут можно указать одну из двух возможных семантик – строковая семантика (row polymorphic) или табличная семантика (table polymorphic).

В данном случае функция объявлена внутри пакета, но совершенно необязательно объявлять ее именно так – это может быть самостоятельная функция, может быть функция в составе другого пакета. 

Здесь функция DESCRIBE возвращает null и итогом работы PTF будут данные, которые ничем не отличаются от данных в исходной таблице, в чем мы и можем убедиться, выполнив SQL-запрос:

select * from ptf_package_1.my_ptf(t);

Пример 2. Добавление и удаление строк и колонок.

create or replace package ptf_package_2 as

--------------------------------------------------------------------------------
function my_ptf
    (
        p_tab          in out table,                 -- исходная таблица
        p_hide_cols    in     columns default null,  -- колонки к удалению
        p_new_col_name in     varchar2 default null, -- новая колонка
        p_new_col_val  in     varchar2 default null  -- значение для новой колонки
    ) return
        table pipelined
        row   polymorphic
        using ptf_package_2;

--------------------------------------------------------------------------------
function describe
    (
        p_tab          in out dbms_tf.table_t,          -- исходная таблица
        p_hide_cols    in     dbms_tf.columns_t default null, -- колонки к удалению
        p_new_col_name in     varchar2 default null,    -- новая колонка
        p_new_col_val  in     varchar2 default null     -- значение для новой колонки
    ) return DBMS_TF.DESCRIBE_T;

--------------------------------------------------------------------------------
procedure fetch_rows
    (
        p_new_col_name  in varchar2 default null,   -- новая колонка
        p_new_col_val   in varchar2 default null    -- значение для новой колонки
    );

end ptf_package_2;
/
create or replace package body ptf_package_2 as

--------------------------------------------------------------------------------
function describe
    (
        p_tab          in out dbms_tf.table_t,        -- исходная таблица
        p_hide_cols    in     dbms_tf.columns_t default null,  -- колонки к удалению
        p_new_col_name in     varchar2 default null,  -- новая колонка
        p_new_col_val  in     varchar2 default null   -- значение для новой колонки
    ) return dbms_tf.describe_t
is
    v_new_columns dbms_tf.columns_new_t;
begin
    -- пометим колонки которые нужно убрать: PASS_THROUGH := false
    for i in 1 .. p_tab.column.count
    loop
        if p_tab.column(i).description.name member of p_hide_cols then
            p_tab.column(i).pass_through := false;
            p_tab.column(i).for_read := false; -- значение по умолчанию, здесь для наглядности
        end if;
    end loop;

    -- добавим новую колонку
    v_new_columns(1) :=
        dbms_tf.column_metadata_t
        (
            name => p_new_col_name,
            type => dbms_tf.type_varchar2
        );

    -- вернем список добавляемых колонок
    return dbms_tf.describe_t( new_columns => v_new_columns );
end;

--------------------------------------------------------------------------------
procedure fetch_rows
    (
        p_new_col_name  in varchar2 default null,   -- новая колонка
        p_new_col_val   in varchar2 default null    -- значение для новой колонки
    )
as
    v_column_values dbms_tf.tab_varchar2_t;
    v_env           dbms_tf.env_t := dbms_tf.get_env();
begin
    DBMS_TF.Trace(v_env);
    -- Для всех строк задаем одно и тоже значение для новой колонки
    for i in 1..v_env.row_count loop
        v_column_values(nvl(v_column_values.last + 1, 1)) := p_new_col_val;
    end loop;

    -- вставим коллекцию значений новой колонки в итоговую таблицу
    dbms_tf.put_col(1, v_column_values);
end;

end ptf_package_2;
/


/* SQL-запрос для вызова PTF */
select * from ptf_package_2.my_ptf(t, columns(c), 'D', 'Hello world!');

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

В функции DESCRIBE задается:

• какие колонки следует скрыть из результата;

• какие новые колонки следует добавить;

• какие колонки следует прокидывать в результат;

• значения каких колонок должны быть доступны в процедуре FETCH_ROWS.

Функция DESCRIBE возвращает тип DBMS_TF.DESCRIBE_T – список новых полей, которые должна вернуть PTF.

TYPE column_t IS RECORD (	
    description  COLUMN_METADATA_T,
    pass_through BOOLEAN,	-- прокидывать ли колонку в результат
    for_read     BOOLEAN	-- доступна ли колонка в FETCH_ROWS
);

При работе с PTF важно помнить о единственном ограничении: параметры и их наименования, указанные в этой функции, должны совпадать с параметрами, которые мы указываем в функции DESCRIBE; но при этом есть разница. В DESCRIBE мы указываем специальные типы, которые описаны в пакете DBMS_TF – это специальный пакет под полиморфные табличные функции. В PTF-функции первый параметр указан как table – это так называемый псевдооператор, который был создан специально под технологию PTF. Второй псевдооператор – это columns. Эти псевдооператоры мы указываем только в описании FTF-функции, которая будет вызываться в SQL-запросе. 

Можно было бы решить, что с этими параметрами следует работать как с обычными коллекциями, но на самом деле это не совсем так – они частично ведут себя как коллекции, но это не коллекции. Позже вернемся к этому нюансу.

Снова обратимся к примеру. Мы исключаем колонки, которые перечислили в параметре hide_cols, и указываем, что параметр pass_through = false.

За что отвечает параметр PASS_THROUGH? Его смысл прост: если PASS_THROUGH имеет значение true, то это означает, что колонка прокидывается из первоначальной таблицы в выходную таблицу, выходные данные. Второй параметр FOR_READ отвечает за то, данные каких колонок будут доступны внутри FETCH_ROWS.

По умолчанию PASS_THROUGH = true , а FOR_READ = false;

Теперь добавление новой колонки. Новые колонки мы добавляем следующим образом: есть объект, который возвращает функция DESCRIBE, и одним из его атрибутов является коллекция, содержащая список новых колонок. 

В этом примере в процедуре FETCH_ROWS мы задаем для всех строк одно и то же значение, которое заполняется в коллекцию, и эта колонка вставляется в итоговые, результирующие данные. Здесь с помощью процедуры DBMS_TF.put_col вставляется новая колонка с указанными данными. Единичка тут означает первую колонку из вставляемых.

Важный момент: параметры в FETCH_ROWS должны быть правильными. Что значит правильными: по документации мы обязаны в FETCH_ROWS передать все параметры, которые указаны в PTF-функции, кроме тех параметров, которые указаны с помощью псевдооператоров, т.е. с помощью table и columns. 

Давайте посмотрим на результат работы.

Исходные данные:

Результат работы PTF:

Пример 3. Добавление реальных данных в новую колонку

create or replace package ptf_package_3 as

--------------------------------------------------------------------------------
function my_ptf
    (
        p_tab          in out table,                 -- исходная таблица
        p_new_col_name in     varchar2 default null  -- новая колонка
    ) return
        table pipelined
        row   polymorphic
        using ptf_package_3;

--------------------------------------------------------------------------------
function describe
    (
        p_tab          in out dbms_tf.table_t,       -- исходная таблица
        p_new_col_name in     varchar2 default null  -- новая колонка
    ) return DBMS_TF.DESCRIBE_T;

--------------------------------------------------------------------------------
procedure fetch_rows
    (
        p_new_col_name in     varchar2 default null  -- новая колонка
    );

end ptf_package_3;
/
create or replace package body ptf_package_3 as

--------------------------------------------------------------------------------
function describe
    (
        p_tab          in out dbms_tf.table_t,       -- исходная таблица
        p_new_col_name in     varchar2 default null  -- новая колонка
    ) return dbms_tf.describe_t
is
    v_new_columns dbms_tf.columns_new_t;
begin
    -- пометим все колонки доступными для чтения в процедуре FETCH_ROWS
    for i in 1 .. p_tab.column.count
    loop
        p_tab.column(i).for_read := true;
    end loop;

    -- добавим новую колонку
    v_new_columns(1) :=
        dbms_tf.column_metadata_t
        (
            name => p_new_col_name,
            type => dbms_tf.type_varchar2
        );

    -- вернем список НОВЫХ, добавляемых колонок
    return dbms_tf.describe_t( new_columns => v_new_columns );
end;

--------------------------------------------------------------------------------
procedure fetch_rows
    (
        p_new_col_name in     varchar2 default null  -- новая колонка
    )
as
    v_row_set    dbms_tf.row_set_t;
    v_new_column dbms_tf.tab_varchar2_t;
    v_row_count  pls_integer;
    v_env        dbms_tf.env_t := dbms_tf.get_env();
begin
    -- получаем данные из исходной таблицы
    DBMS_TF.Get_Row_Set
        (
            rowset    => v_row_set,
            row_count => v_row_count
        );

    -- выводим трассировку
    dbms_output.put_line('v_row_count = ' || v_row_count);
    dbms_tf.trace(v_row_set);
    dbms_tf.Trace(v_env);

    -- вычисляем значение для новой колонки - входные данные в JSON формате
    for i in 1..v_env.row_count loop
        v_new_column(i) := dbms_tf.row_to_char(v_row_set, i);
    end loop;

    -- вставим коллекцию значений новой колонки в строку итоговой таблицы
    DBMS_TF.Put_Col(1, v_new_column);
end;

end ptf_package_3;
/

Давайте сразу посмотрим на результат её работы:

Исходная таблица та же самая. Мы добавляем новую колонку, которую мы заполняем данными в формате JSON, т.е. здесь мы уже работаем с реальными данными. 

А как же мы это делаем?

В функции DESCRIBE указываем, что данные всех колонок нам нужны для чтения в процедуре FETCH_ROWS, т.е. в той процедуре, которая выполняется при SQL-запросе. Соответственно, мы указываем FOR_READ = true и добавляем новую колонку, указываем первым элементом коллекции эту колонку и передаем ее в результат. 

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

Далее в этой процедуре мы получили данные и в специальную коллекцию вносим значения для всех новых колонок. В данном случае у нас одна единственная новая колонка, но я для наглядности здесь организовал цикл.

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

Давайте рассмотрим результат трассировки. В разделе Get Columns перечисляется информация по тем колонкам, которые мы указали FOR_READ = true (в данном случае мы везде указали, что FOR_READ = true, поэтому здесь указаны все колонки).

И в разделе трассировки Put Columns выводится информация по новой колонке.

Пример 4. Разворачивание полей из структуры EAV (Entity Attribute Value) в обычный плоский табличный вид – главная цель тестирования PTF.

Скрипт генерации данных для примера:

-- create mock data

--
drop table ptf_100;
create table ptf_100 ( code number, field_name varchar2(100 char), value varchar2(100 char));
create unique index ptf_100_ui on ptf_100 (code, field_name);
declare
    c_rows constant number := 100;
    v_cur_count number(30) := 1;
    v_check number(30);
begin
    for i in
        (
            select rownum as code from dual t connect by level <= c_rows
        )
    loop
        -- dbms_output.put_line(i.code);
        insert into ptf_100 (code, field_name, value) values (i.code,  'FIRST_NAME', to_char(dbms_random.string('U', trunc(dbms_random.value(5, 10)))));
        insert into ptf_100 (code, field_name, value) values (i.code,  'LAST_NAME',  to_char(dbms_random.string('U', trunc(dbms_random.value(5, 20)))));
        insert into ptf_100 (code, field_name, value) values (i.code,  'GENDER',     to_char(decode(round(dbms_random.value(1, 2)), 1, 'MALE', 2, 'FEMALE')));
        insert into ptf_100 (code, field_name, value) values (i.code,  'PET',        to_char(decode(round(dbms_random.value(1, 4)), 1, 'Dog', 2, 'Cat', 3, 'Bird', 4, 'Reptile')));
        insert into ptf_100 (code, field_name, value) values (i.code,  'CITY',       to_char(decode(round(dbms_random.value(1, 10)), 1, 'Moscow', 2, 'Tokyo', 3, 'Osaka', 4, 'Cairo', 5, 'New York', 6, 'Mexico City', 7, 'Shanghai', 8, 'Dhaka', 9, 'Delhi', 10, 'Sao Paulo')));
        insert into ptf_100 (code, field_name, value) values (i.code,  'POSTCODE',   round(dbms_random.value(100000, 999999)));
        v_cur_count := v_cur_count + 1;
    end loop;
    -- dbms_output.put_line(v_cur_count);
    commit;
end;
/

Данные подготовлены, смотрим, как выглядит исходная таблица:

Это обычная EAV-структура, всем знакомая: у нас есть код сущности и атрибуты этой сущности, в данном случае имя, фамилия, пол, питомец, город, почтовый индекс. Они у нас даны в вертикальной структуре, а цель нашей задачи развернуть их, сделать аналог стандартной операции PIVOT.

И результат работы функции:

По-прежнему самое интересное здесь в DESCRIBE и FETCH_ROWS. Важно помнить: DESCRIBE у нас выполняется один раз при компиляции SQL-запроса, т.е. для каждого SQL_ID функция DESCRIBE выполняется один единственный раз, в то время как FETCH_ROWS выполняется при каждом SQL-запросе с участием данной PTF-функции. 

Небольшая ремарка: до сих пор мы рассматривали только строковые семантики, а в данном случае, обратите внимание, параметр table не row_polymorphic как в предыдущих случаях, а table_polymorphic – это как раз означает, что это табличная семантика и если посмотреть на запрос, то там указано PARTITION BY CODE.

Для PTF с табличной семантикой нам обязательно надо это учитывать, т.к. если мы указываем, что это табличная семантика, но при этом не указываем PARTITION BY CODE или ORDER BY, она будет работать неправильно и выдавать некорректную информацию.

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

Рассмотрим детально как это сделано и как это работает.

Мы должны указать системе, что в процессе выполнения процедуры FETCH_ROWS из той пачки строк, которая нам пришла (данные нам приходят пачками, т.к. мы здесь указали PARTITION BY CODE), все строки, кроме первой, удаляем и все колонки удаляем и добавляем новые колонки со всеми нужными атрибутами. 

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

Далее мы запоминаем порядковые номера полей с названием и значением атрибута, т.е. мы записываем их в отдельные переменные, которые в дальнейшем с помощью механизма работы со store передаем в  процедуру FETCH_ROWS.

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

Итого DESCRIBE у нас выполнил свои задачи: мы указали FOR_READ = true для каждой колонки, т.е. не прокидываем ни одну колонку, зато все колонки открываем для чтения в FETCH_ROWS и указываем, какие новые колонки у нас будут в нашей результирующей таблице.

Идем в процедуру FETCH_ROWS.

Что здесь у нас происходит? Во-первых, мы получаем из store сохраненные значения, индексы полей с названием и значением атрибутов. Хочу заметить, что есть также возможность передачи данных не только между DESCRIBE и FETCH_ROWS, но и между несколькими вызовами этого FETCH_ROWS, т.е. если нам необходимо сделать какие-то накопительные вычисления, агрегации, то с помощью  XStore_Get мы можем передавать значения между разными вызовами FETCH_ROWS.

После прочтения номеров полей из store начинается самое интересное: мы все строки, кроме первой, пометили к удалению, т.е. у нас есть специальная переменная – это массив флагов “удален / не удален” для строк. По сути в этой коллекции будет столько элементов, сколько у нас строк обработки в данном вызове FETCH_ROWS.

Далее мы сначала заполняем значение ключевой колонки, т.е. значение поля CODE, а потом в цикле значения всех атрибутов записываем соответствующие поля строки.

Важный момент. Нам обязательно нужно присвоить новым колонкам либо какие-то вычисленные значения, либо, если мы не смогли вычислить ничего, обязательно указать null. Иначе это приведет к проблемам. Я это сейчас продемонстрирую.

Исключаем код, который заполняет колонку с невычисленными значениями:

И получаем результат:

То есть, если мы исключим из логики код, который заполняет колонку с невычисленными значениями, получаем ерунду. Это связано со следующим: где-то внутри логики, внутри реализации самого PTF выскакивает ошибка NO_DATA_FOUND, но до нас этот NO_DATA_FOUND, этот exception не доходит. Он выскакивает внутри и там же перехватывается, причем перехватывается так, что мы никаких ошибок не видим, видим только печальный результат. 

Вернемся к примеру. Мы заполнили все поля и далее с помощью DBMS_TF.Put_Row_Set вставляем набор строк и вторым параметром указываем нашу коллекцию, в которой перечислены флаги с удаленными/неудаленными строками. В нашем случае, напоминаю еще раз,  у нас пришла пачка строк. Мы фактически всем этим строкам указываем, что они удаленные, оставляем только первую строчку. Её наполняем уже своими новыми колонками и в результате получаем одну строчку вместо всей пачки строк.

Несколько слов про возможные ошибки при выполнении SQL с PTF:

• нужно быть внимательными с параметрами, т.к. при выполнении SQL могут возникать самые неожиданные ошибки: например, я несколько раз получал ошибки типа “ORA-03 113: end‑of‑file on communication channel”;

• если внутри FETCH_ROWS возникнет NO_DATA_FOUND, то есть высокая вероятность, что ошибка будет подавлена и вы получите некорректные данные;

• если параметры процедур не соответствуют правилам, вылезет ошибка "ORA-62573: new column (…) is not allowed with describe only polymorphic table function".

Сравнение производительности по сравнению с обычным PIVOT и JavaScript

Я тестировал PTF на следующих данных: 

И что мы получили? PTF сильно уступает. Значительные потери производительности возникают на уровне большого объема данных.

В целом можно сказать, что у PTF есть какая-то своя ниша, но нужно хорошо думать нужно вам это использовать или нет.

Где ее можно реально использовать? В таких задачах, где мы обязаны на стороне базы выполнить динамический разворот из вертикальной структуры в горизонтальную (именно на стороне базы). Результирующий набор колонок нам при этом заранее неизвестен – это динамический момент. Только в таком случае есть смысл использовать PTF, в других он себя совершенно не оправдывает, учитывая, что есть разные неожиданные ошибки.

На нескольких форумах я также встречал описания бредовых не только ошибок, но и результатов, связанных с какой-то хитрой внутренней логикой, до понимания которой никто не доходил. Из всего этого можно сделать вывод, что технология пока сырая. Следовательно, без острой нужды к PTF можно не обращаться.