Новые возможности SQL в Oracle Database 21c

Функция ANY_VALUE

Функция ANY_VALUE позволяет не включать поле в список GROUP BY, по которым проводится группировку. Значение  в колонке ANY_VALUE выбирается первым со значением NOT NULL. Ключевое слово DISTINCT допускается использовать в ANY_VALUE, но оно не функционирует. 

select d.deptno, any_value(DISTINCT d.dname) AS deptname, any_value(e.ename) AS ename, count(e.empno) AS employee_count from dept d left outer join emp e on d.deptno = e.deptno group by d.deptno order by1;

Улучшенный синтаксис аналитических функций

WINDOW

В предыдущих версиях оконная рамка определялась непосредственно в вызове аналитической функции. В Oracle 21c оконная рамка может быть определена отдельным предложением, что позволяет использовать её одновременно в несколькох аналитических функциях. Также можно определить несколько оконных рамок. Оконную рамку можно расширять по разному в кажом месте использования.

select empno, deptno, sal, first_value(sal) over (w1 rows between unbounded preceding and current row) as lowest_in_dept, first_value(sal) over w2 as highest_in_dept, rank() over w1 as sal_rank_in_dept from emp

window w1 as (partition by deptno order by sal), w2 as (partition by deptno order by sal desc);

GROUPS

Дополнительно к ROWS и RANGE было добавлено ещё одно - GROUPS. При использовании GROUPS данные делятся на группы на основе упорядоченных значений. Текущая строка является частью текущей группы, поэтому ссылки на предыдущую или следующую относятся к предыдущей или следующей группе, а не к конкретным строкам. Подобна RANGE количество строк в окне не известно до тех пор, пока данные не будут упорядочены.

select row_number () over ( order by sal) as row_order, sal, avg(sal) over w1 as avg_rows, avg(sal) over w2 as avg_groups, avg(sal) over w3 as avg_range from emp window w1 as (order by sal rows between 1 preceding and current row), w2 as (order by sal groups between 1 preceding and current row), w3 as (order by sal range between 1 preceding and current row);

EXCLUDE

Ключевое слово EXCLUDE имеет несколько опций, позволяющих исключить различные строки из оконных рамок.

EXCLUDE NO OTHERS: ничего не исключает. Равносильно отсутствию слова EXCLUDE.

EXCLUDE CURRENT ROW: исключает только текущую строку.

EXCLUDE GROUP: исключает текущую группу, текущую строку и все остальные строки с таким же значением.

EXCLUDE TIES: удаляет все строки с таким же значением, как у текущей строки.

EXCLUDE может использоваться с ROWS, RANGE и GROUPS.

select row_number () over w1 as row_order, value, avg(value) over (w1 rows between 1 preceding and 1 following exclude current row) as ex_current_row, avg(value) over (w1 rows between 1 preceding and 1 following exclude group) as ex_group, avg(value) over (w1 range between unbounded preceding and current row exclude ties) as ex_ties from t1 window w1 as (order by value);

Таблица Blockchain

В Oracle Database 21c появился новый тип таблицы - блокчейн таблица - это защищенная от несанкционированного доступа таблица, предназначенная только для вставки, с соответствующим периодом хранения строк и самой таблицы. Строки организованы в цепочки, где каждая строка содержит хэш-сумму данных, содержащихся в строке, и хэш-сумму данных предыдущих строк.

Пример создания таблицы:

create blockchain table bct_t1 (

id number,

fruit varchar2(20),

quantity number,

created_date date,

constraint bct_t1_pk primary key (id) )

no drop until 0 days idle

no delete until 16 days after insert

hashing using "SHA2_512" version "v1";

NO DROP : таблица не может быть удалена (только если в ней уже есть строки). Таблица удалится при удалении схемы. NO DROP UNTIL number DAYS IDLE : таблица не может быть удалена пока не пройдет указанное количество дней, в течении который не будет вставлена ни одна строка.

NO DELETE определяеть срок хранения строк - как долго каждая строка будет защищена от удаления.

Тип данных JSON

В таблице можно создавать колонки с типом данных JSON. 

create table t1 (

id number generated always as identity,

json_data json,

constraint ta_pk primary key (id)

);

SQL Macros

Использование вызова функци в SQL-запросах замедляет выполнение запроса из-за переключения контекстов SQL и PL/SQL. Для решения этой проблемы было придумано ключевое слово sql_macro, которое заставляет опитизатор выполнять подстановку во время оптимизации запроса. 

create or replace function calculate_tax(p_value number)

return varchar2 sql_macro(scalar) is begin return q'{ p_value * 0.2 }'; end; /

... Продолжение следует ... Статья не завершена ...