Задача: см. пост вторника

Решение:
В задаче два момента.
1. Установка nls параметра для возможности использовать дату в Russian.
2. Как задать значение по умолчанию, да еще и без PL/SQL.

Насобирали аж три варианта решения. Спасибо всем кто участвовал 🤝

1️⃣ TO_DATE - в 12.2 привычная нам функция to_date была расширена конструкцией default on conversion error. С помощью неё можно задать значение по умолчанию, которое будет возвращаться при ошибке конвертации.
2️⃣ VALIDATE_CONVERSION - функция определяет возможно ли преобразование значения в заданный тип. Возвращает 1 - успех, 0 - не успех. Проверяем результат, если "не успех" подсовываем значение по умолчанию.
3️⃣ CAST - функция преобразования в указанный тип, так же была расширена конструкцией default on conversion error.

Во всех трех вариантах, можно задать 'NLS_DATE_LANGUAGE = Russian’.
Примеры реализации смотрите в комментариях поста вторника.

✅ Если что-то можно сделать на чистом SQL, то не стоит использовать PL/SQL.

#решениезадачи

Друзья, всем привет!

Как вы уже, наверное, в курсе, в следующие выходные я запускаю интенсив по секционированию/партиционированию.

Это будет два дня насыщенных теорией и практикой. Разберем реальные кейсы и много чего еще.
Как артефакты от интенсива у вас останутся: знания, памятка, созданные вами скрипты и проектная работа аля pet-project.

У вас могут быть некоторые сомнения по качеству и содержанию курса 🤷🏻‍♂️
Поэтому я записал легкую демку на 4 минуты. Возможно, это поможет сделать правильный выбор.

Будет ли еще интенсив на эту тему? Возможно будет, где-нибудь в ноябре/декабре.

На текущий момент, осталось только 3 места. Записывайтесь.

И не забывайте, что каждый Database Developer должен уметь использовать секционирование☝🏻

Всем хороших выходных 👍

#секционирование

Коллеги, всем привет!

Хотел показать вам на прикладной задачке, где можно применить секционирование 😉

Cайт МВД предоставляет возможность скачать реестр просроченных, украденных и т.п. паспортов (2Gb, gzip, не инкремент).
Для многих систем, например, платежных, это необходимая информация для принятия решения о том, можно ли пользователю работать в системе без доп ограничений.

Для хранения этого реестра, в БД создана табличка. Отсутствие записи в ней считается как “можно совершать операции”. Присутствие - как “нельзя совершать операции”. Поэтому она не может быть пуста. Иначе пользователи, которым нельзя выполнять операции, смогут это сделать.

Как же обновлять табличку? При этом создавая минимум нагрузки на БД?
Например, использовать секционирование и команду exchange partition 👍

Про эту команду, конкретную реализацию прикладной задачи с паспортами и о многом другом, мы будем говорить на 2-х дневном интенсиве по секционированию 26, 27 июня.

⚠️ У вас есть еще время до 23:59. Всего - 1 место. Спешите!

#секционирование

Друзья, всем привет!
Соскучились? )

Был занят проведением интенсива по секционированию.
Первый поток был успешно завершен 3 июля.

Коллеги послушали теорию, получили ответы на свои вопросы, успели применить на практике свои знания.
Фидбек отличный, ребятам зашло. Обязательно опубликую отзывы. Спасибо за участие 👍

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

В целом, планов громадьё! На подходе курс по PL/SQL, а там глядишь, и оптимизация подоспеет. Посмотрим, как будет хватать времени ⏳

Кстати, давно не было новых видосов. Теперь свободного времени больше, буду исправляться 😉

Всем хорошей трудовой недели 🛠

#секционирование

Задача: Есть две таблички. Связь Master(PK) - Detail(FK).

create table sale(
  sale_id number(30) primary key,
  sale_date date default sysdate
);

create table sale_detail(
  sale_id number(30) not null,  
  item_id number(20) not null,
  quantity number(10) not null,
  constraint sale_detail_fk
  foreign key(sale_id) references sale(sale_id)
);

Требуется произвести вставку именно в такой последовательности:

insert into sale_detail(sale_id, item_id, quantity) values (1, 1, 100);
insert into sale(sale_id, sale_date) values (1, sysdate);

При этом возникает такая ошибка:

ORA-02291: integrity constraint (xxx.SALE_DETAIL_FK) violated - parent key not found

Можно ли так делать? Если да, то, как исправить?
Порядок insert менять нельзя.

Детали в посте четверга 🎓

#задача

Отложенность проверки ограничений

У ограничения (key, check) можно задать свойство, когда это ограничение будет проверяется. Существует две разновидности:
1. IMMEDIATE - проверка осуществляется непосредственно при выполнении операции (по умолчанию).
2. DEFERRED - проверка осуществляется перед commit. При этом ограничение должно быть создано с возможностью включить этот тип (deferrable).

В большинстве кейсов, хватает первого вида. Но иногда, второй тип очень выручает.

Синтаксис:

alter table имя_табл add constraint имя_ограничения check (выражение) deferrable initially immediate;

alter table имя_табл add constraint имя_ограничения check (выражение) deferrable initially deferred;

Подробней в документации.

Пример ниже ⬇️

Простой пример. Две таблицы. Продажа и товары/позиции в продаже.
В позициях - задано количество каждого товара и цена одной единицы. В продаже - указывается сумма по всем позициям total_price - SUM(quantity*item_price).

create table sale(
  sale_id     number(30) primary key,
  sale_date   date default sysdate,
  total_price number(20,2)
);

create table sale_detail(
  sale_id    number(30) not null,  
  item_id    number(20) not null,
  quantity   number(10) not null,
  item_price number(10,2) not null
  constraint sale_detail_fk foreign key(sale_id) references sale(sale_id)
);

При создании продажи и детализации, можно поступить так:
1. Создать продажу с нулевой общей суммой. Получить ID-продажи.
2. Используя ID продажи, вставить все позиции и посчитать сумму.
3. Выполнить обновление поля “общая сумма” в продаже.
⚠️ Не очень оптимально, появляется лишний update.

Можно пойти другим путем.
1. Сгенерировать ID-продажи (скорее всего, sequence).
2. Вставить позиции, посчитав при этом “общую сумму”.
3. Вставить саму продажу с ID и рассчитанной “общей суммой”.

❗️ Конечно, FK должен быть отложенным, иначе при попытке вставки в sale_detail возникнет ошибка (нет записи в sale).

Вполне вероятно, подобная техника может пригодиться при репликациях, загрузке данных и т.п. Когда непонятно, что идет вперед “курица или яйцо”.

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

#constraint

Задача: Есть две таблички. Связь Master(PK) - Detail(FK).
В Detail таблицу производится вставка. Такого ключа в Master-таблице нет.
Возникает ошибка.

Решение:
Изменить определение foreign key. C незамедлительной проверки (immediate, по умолчанию) на отложенную проверку (deferred).

alter table sale_detail drop constraint sale_detail_fk;
alter table sale_detail add constraint sale_detail_fk foreign key (sale_id)
references sale(sale_id)
deferrable initially deferred;

В таком случае, целостность данных будет проверяться только в момент commit.
На табличке, которая уже во всю используется такой фокус по удалению/созданию может не прокатить, зависит от вашей системы - 24/7.
Вероятно, придется использовать свойство ограничения Novalidate. Как-нибудь, расскажу о нем.

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

Если задачка понравилась - ставьте 👍

#решениезадачи #constraint

Задача:
В таблице event есть колонка event_unix_time. Это дата/время в Unix time. Число.
Необходимо организовать быстрый поиск по этой колонке.

create table event(
  event_id        number(30) primary key,
  event_unix_time number(21) not null,
  message         varchar2(200 char) not null
);

insert into event values (1, 1113135689, 'm1');
insert into event values (2, 1626075857, 'm2');
insert into event values (3, 1626000057, 'm3');
commit;

Например, такой запрос:

select * from event t where что-то <= date '2020-07-01';

результат: строка с event_id = 1.

Усложнение задачи для пытливых умов: считать время по Московской таймзоне.

#задача

Deterministic-функции

Друзья, всем привет!

У меня для вас свежее видео 🎥
В этот раз копнул в сторону детерминированных/чистых PL/SQL-функций.
Что это такое? Когда использовать? Как можно ускорить выполнение SQL-запросов, используя их?

Ответы на эти вопросы смотрите в уроке. Надеюсь, понравится 😉

Кстати, если помните, весной в голосовании вы выбрали урок про виды индексного доступа. Концепция урока пока бродит в голове. Хочется разжевать тему доступно. Есть желание, попробовать разнообразить его анимацией, чтобы он был наглядней.
К сожалению, Гугл презентации не так богаты в части анимации. Если кто-то встречал софт по созданию визуализаций для алгоритмов - напишите, буду благодарен.

А пока, приятного просмотра (6 минут).

#видео #plsql #функции #deterministic

Задача о поиске по колонке Unix time
постановку см. в посте вторника.

Решение:
Как минимум, два способа:
1️⃣ Создать функциональный индекс по функции преобразования столбца event_unix_time.
2️⃣ Создать виртуальную колонку с использованием функции преобразования + индекс по ней.

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

Например, такую:

create or replace function unixtime_to_date(p_unix_time number)return date
deterministic
is
begin
  return(timestamp '1970-01-01 00:00:00 GMT' +
         numtodsinterval(p_unix_time, 'second')) at time zone 'Europe/Moscow';
end;

Просто функцию использовать не получится, нужно объявить её deterministic.
Что это такое и с чем это едят - смотрите в видео-уроке (предыдущий пост).

Решение 1

create index event_unix_time_idx on event(unixtime_to_date(event_unix_time));
select * from event t where unixtime_to_date(event_unix_time) <= date '2020-07-01';

Решение 2

create table event(
  event_id         number(30) primary key,
  event_unix_time  number(21) not null,
  event_dtime      date as (unixtime_to_date(event_unix_time)) not null,
  message          varchar2(200 char) not null
);

create index event_unix_time_idx on event(event_dtime);
select * from event t where event_dtime <= date '2020-07-01';

Если задачка понравилась - ставьте 👍

#решениезадачи #deterministic

Задача

У нас в канале специалисты различного уровня, поэтому сегодняшняя задачка будет не сильно сложной 😉

Необходимо найти количество восклицательных знаков в строке:
'Hello World! It is a good day!!!'

В данном случае, запрос должен вернуть “4”.

⚠️ Использовать можно только SQL.

🍅 Для сеньоров-помидоров можно чуть усложнить: найдите не менее трех способов решить задачу.

Рекомендую не смотреть комментарии, пока не решите сами 😉

С вашими решениями из комментариев, попробуем сделать интересную вещь, посмотрим как зайдет, но об этом в четверг.

#задача

Задача: поиск количества вхождений определенного символа в строку.

Решение:
Было очень интересно посмотреть разнообразие вариантов.
Спасибо всем, кто потратил своё время. Респект 🔥

В целом, можно выделить три группы решений:

1️⃣ Использование стандартных функций работы со строками - length, replace, translate и др.

2️⃣ Различные комбинации использования функция работы с регулярными выражениями - regexp_count, regexp_replace и др.

3️⃣ Иерархические запросы - connect by.

Всем рекомендую залезть в комментарии под постом вторника и заценить замысловатые решения коллег 💡

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

#решениезадачи

Задача

Представьте, что каждый день к вам на сервер СУБД записывают в виде файлов - данные о клиентах.
Эти данные нужно прочитать, обогатить из других источников и, например, отправить email каждому клиенту.
🔸 строк/клиентов - 10М
🔸 начать отправку нужно как можно быстрей, желательно сразу после чтения первой строки из файла.
🔸 нельзя бахнуть сразу 10М писем одномоментно.

Как бы вы решили такую задачу? Что бы вы использовали?
Код писать не надо.
Если есть предположения как можно решить такую задачу пишите в комментариях.

P.S. В современном мире, это задача среднего слоя.

#задача

Pipelined(конвейерные) - функции

Друзья, всем привет!
У меня для вас свежее видео 🎥

В этот раз копнул в сторону pipelined или конвейерных PL/SQL-функций.
Что это такое? Когда использовать? Как можно ускорить выполнение ваших программ и сделать их более эффективными?
Ответы на эти вопросы смотрите в уроке.

Тема может показаться не простой. Поэтому я постарался сопроводить урок обилием примеров. Надеюсь, понравится.

Приятного просмотра - 15 минут.

#видео #plsql #функции #pipelined

Задача: чтение данных с диска и отправка email с соблюдением некоторых условий.

Пожалуй, не один из постов не вызывал столь бурную реакцию нашего мини-сообщества 🔥 Досталось и мне, за отсутствие точного ТЗ, и порочной концепции реализации функционала в БД.
"Чувствую, что автор просто совета просит)) " - это запомнилось особенно.
Ну и хорошо, юмор помогает жить 😉

Решение:
Как я уже много раз писал, любую задачу можно решить разными способами.
Особо прозорливые подписчики уже догадались, что задачка была не просто так задана. Да, одним из способов её решения являются как раз те самые конвейерные или pipelined-функции.
Кратко: по ходу обработки каждой строки по конвейеру мы можем выполнять различные манипуляции и отправлять "условный email", не ожидая загрузки всего объема входных данных из 10М. Подробное объяснение, смотрите в видео-уроке из прошлого поста.

Многообразие систем, решений и Legacy-кода огромно. Возможно, на очередном месте работы вы встретитесь именно с таким методом работы с данными и внешними системами.

Все больше занимаясь разработкой на среднем слое, я лично для себя понял, что такого рода задачи должны решаться именно в приложениях среднего слоя. Подход с жирной серверной логикой в БД, взаимодействие с внешним миром из БД - устаревает. Тема холиварная ))

Кстати, в сентябре будет запуск курса “Oracle PL/SQL.Основы”. Материал готов 🎓 Скоро будет анонс. Кому интересно - ничего не планируйте на сентябрь-ноябрь 😉

Всем хорошего дня!

#решениезадачи #функции #pipelined

Друзья, всем привет!

Вчера занимался некоторыми изысканиями. Это меня привело на страничку с продажей лицензий СУБД Oracle.

Я всегда знал, что стоимость, мягко говоря, заоблачная. Но тут на глаза попались цифры 😳
Для калькуляции возьму стоимость процессорных лицензий. На мой взгляд, наиболее распространенный тип лицензирования.

Oracle 12 EE - 1 CPU лицензия - 4.2М рублей или 57К$ 🔥

В одной из организаций, которую я не буду называть, был сервер с 512 CPU.
Умножим: 512 * 4.2M = 2 150 400 000 руб = 28 000 000 $ в год.

Это один prod сервер. А есть другие БД и среды.

Предупреждая вопросы про ядра: При использовании многоядерных систем (для версии Enterprise Edition) в расчет берется количество ядер, т.е. каждое процессорное ядро выступает как отдельный процессор. Количество процессоров рассчитывается с учетом лицензионного коэффициента, зависящего от типа процессора.

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

Продолжение ⬇️

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

С учетом этого, прикиньте, кто может позволить себе купить лицензию на Oracle, и где вам придется работать 😉
Банки, крупные страховые, платежные системы, ритейловые сети, опсосы и т.п.
Написано ооочень много PL/SQL-кода, который нужно поддерживать и развивать. Без работы не останетесь 💰

Если есть люди в канале близкие к закупке, напишите в комментариях, было бы интересно посмотреть ваши кейсы. Без названий компаний, конечно.

На этой позитивной ноте, желаю всем хорошей трудовой недели! 👍

Долгое выполнение count в пустой таблице

На этот раз обойдемся без email’ов 😉
Эту задачу дают почти на каждом собеседовании на Senior Oracle DBD.
—--
Вы выполняете запрос:

select count(*) from some_table;

Спустя 5 минут вам возвращается результат. Он равен “0”.

В чем причина долгого выполнения?
—-

Комментарии закрываю, в этот раз без спойлеров. Лучше создам опрос 📊

Ответ, как всегда, в четверг 🙋🏻‍♂️

#задача

Задача: долгое выполнение select count из пустой таблицы. см пост вторника.

Судя по опросу, этот вопрос задают не так уж и часто. Может мне везло 🙂

Решение:
Как может на пустой таблице долго выполняться запрос select count(*) ведь строк в ней нет? Собственно, чего считать то?

Такой эффект возможен, когда до этого таблица была большой и затем из нее были удалены все строки. Причем командой delete.

При подсчете count (full table scan) СУБД обязательно должна дойти до HWM (high water mark) сегмента = объекта.

Попытаюсь на примере наводнения объяснить:
1. Представьте дом до наводнения - это аналогия с пустой таблицей.
2. Во время наводнения уровень воды поднимается на сколько то метров от земли - это таблица заполненная данными.
3. После того как наводнение ушло, на доме осталась отметка максимального уровня воды как на картинке (HWM) - аналогия с удалением данных из таблицы командой delete.

Продолжение ⬇️