Записки жертвы высоких технологий

    Здравствуйте! Мой блог посвящен в большей своей части моей профессиональной деятельности на поприще использования технологий ORACLE для разработки баз данных OLTP и OLAP хранилищ данных. В заметках я периодически размещаю разнообразные SQL, PL/SQL и Java скрипты написанные мной и не только мной, ссылки на интересные источники в сети либо другую полезную информацию, которая каким либо образом касается моей работы с замечательными продуктами компании ORACLE.
    Вы можете связаться со мной по контактному емейлу, если у вас есть какие-либо вопросы, связанные с разработкой баз данных на основе продуктов ORACLE, буду рад помочь вам, если это будет в моих силах.

7 окт. 2025 г.

Генерация последовательностей чисел без использования CONNECT BY

Написал, казалось бы, простой запрос, получающий последовательности свободных и занятых
ключей в очень большой таблице для ее уплотнения. В процессе запуска наткнулся на ошибку
нехватки памяти для работы цикла CONNECT BY. Победить ее не смог и сначала позорно написал конвейерную функцию

create or replace function number_sequence(p_from number, p_to number) return
dbkey_tab pipelined is
begin
if p_from > p_to then
for i in REVERSE p_to .. p_from loop
pipe row(dbkey_row(i));
end loop;
elsif p_from < p_to then
for i in p_from..p_to loop
pipe row(dbkey_row(i));
end loop;
end if;
return ;
end;

но потом вспомнил про альтернативное написание иерархических запросов и быстренько
соорудил вот такие шаблончики генерации последовательностей: первый - для генерации 
обратной последовательности N...1, (-1), 

WITH rseq (value) AS (
SELECT 10 value FROM dual e
UNION ALL
SELECT ep.value - 1 FROM rseq ep JOIN dual e on ep.value > 1
)
SELECT * FROM rseq

эквивалент запроса 

select 10 - (level - 1) from dual connect by level <= 10

а второй, соответственно, - для генерации 
прямой последовательности 1...N, (+1)

WITH rseq (value) AS (
SELECT 1 value FROM dual e
UNION ALL
SELECT ep.value + 1 FROM rseq ep JOIN dual e on ep.value < 10
)
SELECT * FROM rseq

эквивалент запроса

select level from dual connect by level <= 10


Заменил CONNECT BY запросы на вот такие последовательности - ошибка исчерпания памяти 
исчезла, запросы прекрасно работают. В бложике размещаю как памятку для повторных своих 
использований. Вы, если найдете, тоже, конечно, пользуйтесь.

Комментариев нет: