Задача. Повторная вставка без exception

Давно не было авторской задачки 😊

Дана табличка:

create table tab1(
  id number(38),
  val varchar2(20 char),
  constraint tab1_pk primary key (id)  
);

insert into tab1 values (1, 's1');
commit;

Повторное выполнение вставки

insert into tab1 values (1, 's1');

завершается ошибкой:
ORA-00001: unique constraint (PART.TAB1_PK) violated

Хочется написать запрос так, чтобы повторная вставка не вызывала ошибку. Как это сделать?

Я насчитал, как минимум, 3 способа через SQL и 1 читерский через PL/SQL. Интересует, прежде всего, SQL.

Разбор, как всегда, в четверг 🎓

Обсудить в чатике 💬

#задача
Oracle Developer

Задача. Решение. Повторная вставка без exception

Кратко: нужно выполнить повторно insert с таким же PK без возникновения ошибки. Постановка.

1️⃣ Переписать через merge

merge into tab1 d
using (select 1 id, 's1' val from dual) s
   on (d.id = s.id)
when not matched then insert(id, val)
  values(s.id, s.val);  

2️⃣ Использовать хинт (с Oracle 11g)

insert /*+ ignore_row_on_dupkey_index(tab1(id)) */
  into tab1 values (1, 's1');

У хинта есть ограничения, он не всегда подходит. Смотрим документацию.

3️⃣ Использовать опцию log errors в DML
предварительно создаем таблицу для логирования ошибок

call dbms_errlog.create_error_log(
dml_table_name => 'tab1',
err_log_table_name => 'tab1$errlog'
);

insert into tab1 values (1, 's1')
log errors into tab1$errlog reject limit unlimited;

подробности в этом посте

4️⃣ Способ читерский (через PL/SQL)

begin
  insert into tab1 values (1, 's1');
exception 
  when DUP_VAL_ON_INDEX then
    null;
end;
/

С PL/SQL может быть полет фантазии, но интересовал, прежде всего, SQL.

В чатике коллеги накидали еще вариантов.

А что там в PostgreSQL?

Самое простое решение:

insert into tab1 values (1, 's1')
on conflict on constraint tab1_pk
do nothing;

К осени, планирую запустить пилотный поток по основам СУБД PostgreSQL, миграции с Oracle. Кому интересно пишите в личку, ищу 9 человек 😉

————

Понравилась задачка? Ставь 👍

#решениезадачи #exception #dml
Канал Oracle Developer | Чатик 💬

Задача. Реверс инжиниринг текста запроса

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

Как вы знаете, в январе стартовал второй поток по оптимизации. Вчера мы с ребятами разбирали как читать планы запросов, как читать последовательность выполнения операций и т.д. Один из примеров был такой - см. скрин.

Попробуйте по этим данным восстановить план запроса. Скажу сразу этот запрос с особенностью. Если часто с таким не сталкиваетесь будет сложновато.

Ради интереса прогнал через ChatGPT3.5, он сформировал неверный текст запроса 🤖

Разбор задачи в четверг 🎓

Обсудить в чатике 💬

#задача
Oracle Developer

Решение задачи. Реверс инжиниринг текста запроса

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

Итак. Что мы можем сказать по этому плану?

Выполняется 4 операции.
1️⃣ На 3м шаге происходит полное сканирование таблицы employees (table access full). Результат выборки: две колонки email и department_id (см. column projection information для 3). Никаких предикатов нет (нет информации в блоке predicate information для 3), а значит нет условия where.

2️⃣ На 2м шаге происходит доступ к индексу dept_id_pk (index unique scan) по department_id (блок predicate information для 2). Результат выборки: 1 - rowid (из листа уникального индекса при unique scan).

3️⃣ На 1м шаге, по полученному rowid, выбирается одна строка из таблички departments. Результат выборки: department_name.

Ок. У нас есть два множества, полученные на 1м шаге и 3м. Но как они меж собой взаимодействуют?
Если посмотреть в плане нет никаких соединений (join), объединений (union).

Отгадка в последовательности выполнения операций. Человек, который не сталкивается с такими кейсами сказал бы, что последовательность выполнения 2-1-3-0. Но нет 🤷🏻‍♂️
Это исключение. В данном случае, последовательность 3-2-1-0.

Т.е. для всех строк из шага 3, выполняется 2-1.

Итоговый запрос

select e.email
      ,(select d.department_name
          from departments d
         where d.department_id = e.department_id) dep_name
  from employees e;

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

Если хочется комплексно подойти к вопросу - велком на курс по оптимизации Oracle SQL. Старт следующего потока - лето 24.

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

#оптимизация #решениезадачи
Канал Oracle Developer | Чатик 💬

Задача. Хранение истории заключения договоров

Давно не было задачек на архитектуру 😊

Некая система, допустим, электронный кошелек, должна хранить историю договоров пользователей:
🔸 когда пользователь регистрируется в системе, создается запись о том, что договор активен;
🔸 когда он удаляет свой кошелек, необходимо этот факт отразить в данных;
🔸 регистрация в системе происходит по номеру телефона. Все мы помним, что номер телефона может переиспользоваться со временем;
🔸 иногда нужно искать по номеру телефона, когда он использовался с каким договором в какие периоды;
🔸 ожидается, что всего активных договоров будет около 10 миллионов, а неактивных, со временем, ~100 миллионов и будет расти.

Необходимо создать таблицу (или несколько), которая бы отвечала требованиям выше. Как минимум, должны быть поля:
▫️ ID договора (PK)
▫️ Номер телефона (число, не уникальное)

Разбор задачи в четверг 🎓

Обсудить в чатике 💬

#задача
Oracle Developer

Медленно меняющееся измерение или Slowly changing dimension (SCD)

Как-то давно мы уже обсуждали подходы по хранению исторических данных. Я решил, немного раскрыть тему.
Основа - статья в Wiki на ENG (больше инфы) и RUS.

Есть некоторое количество подходов к хранению исторических данных. Кратко перечислю.

Тип 0. Данные не меняются
Изменения не фиксируются, т.к. данные в строках/полях не изменяются.

Тип 1. Полная замена
Историчность не соблюдается, новые данные записываются вместо старых. Нет никакой возможности получить предыдущие значения.

💡Этот тип встречается постоянно. Вы его используете по умолчанию.

Тип 2. Вставка новой строки с новой версией данных
Помимо основных полей, в таблице появляются служебные поля, которые обслуживают историю.

Например, столбец “Actual” = {0 - не актуальная версия, 1 - актуальная}. С “1” может быть только одна строка для какого-то конкретного экземпляра сущности. С “0” может быть несколько.

Или две даты, обозначающие период действия строчки - from/to. Для актуальной, самой последней строки, значение в date_to заполняется либо null, либо какой-то заглушкой типа 01.01.4000.
Еще могут быть различные вариации с монотонно растущими версиями и т.п.

Чтобы получить актуальную запись достаточно отфильтровать по столбцам Actual = 1 или date_to = 01.01.4000 или взять максимальную версию.

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

Тип 3. Добавление доп полей для хранения старых значений
В основной строке мы создаем доп поля, в которых хранятся предыдущие значения.
Как только строка меняется, то в основные поля попадают новые значения, а в доп поля старые значения. Используется такой подход для ограниченного количества полей, только по тем, которые интересует история. Естественно, сохраняется всегда только текущая версия и предыдущая, мы не сможем проследить всю историю изменений.

💡Не прям, частый кейс, но если требуется хранение только текущего и предыдущего значения ограниченного набора полей - почему нет.

Тип 4. Добавление таблицы с историей
В основной таблице хранится самая актуальная информация.
В дополнительной таблице хранятся все изменения. Она повторяет по структуре основную + доп служебные поля (дата изменения, может быть пользователь кто менял и т.п.)
Как только меняется основная таблица, информация тут же помещается в историческую.

💡Довольно часто, этот тип используется в аудитах/логах и т.д.

+ еще три типа, являющиеся разными комбинациями и/или расширениями первых четырех.

У каждого типа есть свои плюсы и минусы и кейсы использования. Рекомендую посмотреть статью на Вики.

К чему это все?
Можно использовать один из подходов для решения вчерашней задачки 😉

#scd #архитектура
Канал Oracle Developer | Чатик 💬

Решение задачи. Хранение истории заключения договоров

При решении задачи воспользуемся вторым подходом/типом SCD (см предыдущий пост).
Добавим два поле - дата ОТ и дата ДО действия строки.

create table contract(
  contract_id number(38) not null, --PK
  phone number(12) not null,
  date_from date not null,
  date_to date not null 
);

Одномоментно, может быть только один активный контракт на один номер телефона. Добавим ограничение:

create unique index contract_phone_uq on contract(phone, date_to);

Активные записи будет помечаться “заглушкой” в date_to = 01.01.4000.

insert into contract values (1,79139331122, sysdate, date'4000-01-01');
insert into contract values (2,79139331155, sysdate, date'4000-01-01');

Для деактивации договора, достаточно найти по номеру телефона и “заглушке” актуальную запись и изменить значение в date_to на текущую дату.

update contract c
   set c.date_to = sysdate
 where c.phone = 79139331155
   and c.date_to = date'4000-01-01';

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

select * from contract t where t.phone = 79139331155;

Для повторной регистрация пользователя и создание нового контракта на номер, который уже был в БД, достаточно опять произвести вставку с "заглушкой".

insert into contract values (3,79139331155, sysdate, date'4000-01-01');

В целом, мы реализовали то, что хотели. Итоговую историю, можно посмотреть на скриншоте ⬆️
Тут не хватает, некоторых штрихов, которые мы обсудим в следующих постах.

К слову сказать, такой подход неоднократно использовался в реальном банковском софте 👨🏻‍💻

Палец вверх, если понравилось 👍

#архитектура #решениезадачи
Канал Oracle Developer | Чатик 💬

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

В прошлом году у нас проходил #конкурс с денежными призами на ТОП лучших задачек.
Помните, наверное, про бомжей, календари и другие 😉
Опыт оказался удачным - вам было интересно, коллеги получили денежку 💵

Предлагаю повторить. Всего будет 6 задачек по SQL или PL/SQL.

Призы
1️⃣ Авторам
1 место - 5К, 2 место - 3К, 3 место - 2К рублей.

2️⃣ Активному участнику в чате
Автор ответа (не важно на какую задачу), набравший максимальное количество реакций в чатике, будет так же одарен.

Кто хочет поучаствовать в качестве автора - прошу в личку 👌
Примеры задачек можно посмотреть по хештегу #конкурс

К сожалению, для призового фонда нужна денежка, поэтому реклама еще немного помозолит глаза 🤷🏻‍♂️

Oracle Developer
#конкурс

Задача. Удаление ненужных договоров

Вернемся к нашей задаче с договорами.

Бизнес решил, что закрытые договора, старше 5 лет (от даты закрытия), можно удалять из системы.
Данные эти не нужны.

Таблица, которую мы создали:

create table contract(
  contract_id number(38) not null, --PK
  phone number(12) not null,
  date_from date not null,
  date_to date not null 
);

Уточнения:
🔸 практически все запросы в системе происходят к активным договорам;
🔸 в месяц заключается - 1M договоров, закрывается - 300K.

Какие есть варианты реализации бизнес требований?

Разбор задачи в четверг 🎓

Обсудить в чатике 💬

#задача
Oracle Developer

Решение задачи. Удаление не нужных договоров

При деактивации договора, в поле date_to проставляется текущая дата. Таких договоров, в месяц может быть - 300К.

Эту задачу можно решить, как минимум, двумя способами.
1️⃣ Применить Range-секционирование к таблице по дате закрытия договора - date_to.

Алгоритм: достаточно определить секцию для удаления, выполнить DDL-команду и всё.

alter table contract drop partition part_name update global indexes;

➕
▫️ DDL команда на удаление выполнится моментально, практически не создав UNDO\REDO информации и нагрузки на БД.
▫️Активные договора всегда будут находится в самой последней секции с date_to = 01.01.4000. Можем попутно получить плюсы от секционирования, связанные с ускорением выполнения запросов.
▫️Не важно сколько данных удаляется 300К или 300М - всегда будет предсказуемое время работы.

➖
▫️Таблицу необходимо переделать в секционированную, если она уже существует - это может быть проблемой (dbms_redifinition).
▫️Если обращение идет не по глобальному индексу (PK), то во всех запросах необходимо использовать ключ секционирования по date_to, иначе будут перебираться все секции. Запросы придется писать внимательно.
▫️DBA скептически относятся к выполнению DDL-команд на таблицах с глобальными индексами, даже с опцией update global indexes. Могут не пропустить такое решение.

2️⃣ Удалять данные пачками обычным DML

delete from contract where date_to < :delete_date and rownum < N;

➕
▫️Не надо менять структуру таблицы;
▫️Достаточно простая реализация удаления пачками по N-строк ниже определенной даты.

➖
▫️Создается дополнительная нагрузка на СУБД - генерация UNDO\REDO.
▫️При увеличении удаляемых данных выполнение начнет проседать, необходимо будет увеличивать частоту удалений и размер пачек. В итоге можно воткнуться в потолок, при котором придется переходить на секционирование.

———
И в первом и во втором случаях, функционал заворачивается в процедурку, которая вызывается из JOBа.

С 1м вариантом JOB запускается раз в сутки\несколько раз в месяц, со 2м вариантом чаще, например, раз в 1 час - удаляем мелкими пачками, чтобы распределить нагрузку в течение дня или удаляем всё ночью.

Кстати, можно создать комбинацию из 1го и 2го вариантов 😉

Палец вверх, если понравилось 👍

Обсудить в чатике плюсы и минусы решений 💬

Плаваете в секционировании? Велком в личку, есть что предложить 🎓

#архитектура #решениезадачи #секционирование
Канал Oracle Developer | Чатик 💬

🚀 Курс "Оптимизация Oracle SQL.Основы" - это отличная возможность освоить искусство оптимизации SQL запросов по уникальной программе "с нуля до уверенного специалиста, который может решать 80-90% типовых проблем".

Старт: 20е мая, окончание 29е августа.

Цели
🔺научиться находить и решать проблемы в PROD среде;
🔺научиться писать сразу оптимальные запросы;
🔺успешно проходить блок по оптимизации на собеседованиях;
🔺систематизировать знания.

Кратко
🔸теория по понедельникам, практика по четвергам, встречи в Zoom;
🔸11 домашних заданий + 7 тестов + курсовая работа;
🔸содержание курса структурировано таким образом, чтобы знания можно было применять уже после первой лекции.
🔸6 блоков с фокусом на решении реальных проблем:
1️⃣ Выявление проблемных запросов.
2️⃣ Чтение и понимание планов запросов.
3️⃣ Анализ планов запросов.
4️⃣ Решение проблем.
5️⃣ Внесение изменений.
(планы, хинты, awr,ash, v$active_session_history, статистика, трассировка, tkprof, nested loops, hash join, index, full table scan и многое многое другое).

Стоимость: 79K рублей или 42 тысячи рублей в рассрочку на 2 месяца (оплата любым способом из любой страны с чеком, крипта).
Сначала бронируйте участие. Оплата ближе к запуску.

Осталось мест: не осталось. Следующая возможность попасть на курс - сентябрь. Бронируйте заранее.

Минимальные требования: знаете SQL, имели опыт работы с PL/SQL, имеете представление о СУБД Oracle. Отлично, если сталкиваетесь с задачами по оптимизации и не можете их решить. Высокая мотивация дойти до конца. QA, аналитик, разработчик.

Отзывы
Видео-отзывы ребят первого и второго потоков. Скрины отзывов в посте выше. Если у вас есть сомнения, посмотрите.

Реальная жизнь
А теперь представьте себе такую ситуацию ✨
К вам прибегают пользователи/аналитики/начальник и кричат "все пропало! все тормозит! нужно срочно решать проблему, PROD стоит!" 😱
Еще 5 минут назад вы предвкушали спокойный день, попивая халявный кофе на кофепоинте, весело общаясь с коллегой. А теперь нужно срочно куда-то бежать, что-то делать, решать проблему, которой "лучше бы не было". Вы просто обычный специалист, который ищет медленные запросы "через дебаг кода". Что делать будете? 😁

Можно еще усложнить, добавив в эту ситуацию то, что сломалась не ваша ламповая базенка в вашей же организации, до которой дотянуться легко и просто, а где-то на площадке заказчика, в банке😳
Прошел холодок по спине? Руки вспотели? Как дебажить будете? Полетите с Новосибирска в Москву (доступов то нет)? 😁

Ответы на курсе. Бронируйте пока еще есть 1 место Мест нет 🤷🏻‍♂️
Следующая возможность попасть на курс - сентябрь. Бронируйте заранее.

#оптимизация #курс
Канал Oracle Developer | Чатик 💬

SQL марафон из 5 задач с призом 🏆

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

Решил попробовать новый формат. Это будут задачи для начинающих. Однако, мидлам и сеньерам тоже будет полезно размять мозг 🧠
Примерно такого уровня вопросы, мне попадались на собеседованиях на Java-разработчика.

Особенность - ответы будут сразу публиковаться с задачкой, но в скрытом режиме. Всего будет 5 постов-задач, с интервалом в 1.5 часа.

🔹Рекомендую, перед открытием спойлера, посидеть и подумать 😉
🔹Синтаксис, естественно, для Oracle.
🔹Я привожу не все возможные решения 👌
🔹Оставляем "за бортом" вопросы оптимизации.
🔹Определения для таблиц можно взять с репозитория
🔹Телега не позволяет использовать спойлер и SQL-форматирование одновременно😔

⚠️ Для интереса. Автору, опубликовавшему max количество оригинальных решений суммарно на все задачки в чатике, будет вручен денежный приз миллион - 5000 рублей или эквивалент USDT 💸💸 Добавляйте в ответ #решение_n.

Первая задача в 11:00 МСК ⏰

#задача #sql #марафон
Oracle Developer | Чат 💬