Задача 2. Создание deadlock. Решение

📝 Постановка
🧑🏻‍💻 Автор

✅ Решение автора

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

select some_field 
  from some_table 
 order by dbms_random.random() for update;

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

Oracle Developer
#конкурс #решениезадачи

Задача 3. Вывод календаря. Решение

📝 Постановка
🧑🏻‍💻 Автор

✅ Решение автора

🗯 "Запрос написан немного в лоб. Может быть коллеги предложат что-то более симпатичное"

WITH par_date AS
 (SELECT to_date('26.09.2023', 'dd.mm.yyyy') AS par_date FROM dual),
t AS
 (SELECT (pd.par_date - 40 + LEVEL) AS d FROM par_date pd CONNECT BY LEVEL <= 80),
tt AS
 (SELECT t.d
        ,to_char(d, 'DY', 'NLS_DATE_LANGUAGE=AMERICAN') AS dd
        ,to_char(d, 'IW') AS w
        ,to_char(d, 'MM') AS m
    FROM t
        ,par_date pd),
ttt AS
 (SELECT *
    FROM tt
  pivot(MAX(tt.d), MAX(tt.m) AS m
     FOR dd IN('MON', 'TUE', 'WED', 'THU', 'FRI', 'SAT', 'SUN'))
   ORDER BY 2)
SELECT ttt.w       AS "НЕД"
       ,EXTRACT(DAY FROM ttt."'MON'") AS "ПН"
       ,EXTRACT(DAY FROM ttt."'TUE'") AS "ВТ"
       ,EXTRACT(DAY FROM ttt."'WED'") AS "СР"
       ,EXTRACT(DAY FROM ttt."'THU'") AS "ЧТ"
       ,EXTRACT(DAY FROM ttt."'FRI'") AS "ПТ"
       ,EXTRACT(DAY FROM ttt."'SAT'") AS "СБ"
       ,EXTRACT(DAY FROM ttt."'SUN'") AS "ВС"
  FROM ttt
      ,par_date pd
 WHERE ttt."'MON'_M" = to_char(pd.par_date, 'MM')
    OR ttt."'TUE'_M" = to_char(pd.par_date, 'MM')
    OR ttt."'WED'_M" = to_char(pd.par_date, 'MM')
    OR ttt."'THU'_M" = to_char(pd.par_date, 'MM')
    OR ttt."'FRI'_M" = to_char(pd.par_date, 'MM')
    OR ttt."'SAT'_M" = to_char(pd.par_date, 'MM')
    OR ttt."'SUN'_M" = to_char(pd.par_date, 'MM');

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

Oracle Developer
#конкурс #решениезадачи

Задача 4. Ускорение выполнение запроса. Решение

📝 Постановка
🧑🏻‍💻 Автор

✅ Решение автора

Для таблицы
1️⃣ HWM - если в таблице мало строк, а HWM высокий, необходимо сбросить HWM.
2️⃣ Проверить наличие policy и оптимизировать их, если проблема в них.
3️⃣ Проверить наличие блокировок и оптимизировать их, если необходимо.
4️⃣ Разбить таблицу на несколько более мелких, чтобы уменьшить время выполнения запросов.
5️⃣ Оптимизировать структуру таблицы, чтобы уменьшить размер записей и ускорить операции чтения и записи.

Для вьюхи
1️⃣ Проверить правильность связанных таблиц и переписать запрос, если необходимо.
2️⃣ Использовать хинты для оптимизации запроса.
3️⃣ Проверить наличие индексов и оптимизировать их, если необходимо.
4️⃣ Проверить статистику и обновить ее, если необходимо.
5️⃣ Проверить наличие блокировок и оптимизировать их, если необходимо.
6️⃣ Использовать материализованные вьюхи для ускорения выполнения запроса.
7️⃣ Разбить сложную вьюху на несколько более простых, чтобы уменьшить время выполнения.
8️⃣ Использовать партиционирование таблицы для улучшения производительности запросов.
9️⃣ Использовать кэширование результатов выполнения запросов на вьюху, чтобы уменьшить время выполнения.

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

—

⚠️ Напомню, что осталось:
Основы оптимизации Oracle SQL. Старт: 15.01
1 место Секционирование в Oracle. Старт: 04.02

Хочешь бустануть свои знания? 🚀 Пиши в личку пока не стало поздно.

Oracle Developer
#конкурс #решениезадачи

Задача 5. Поиск недостающих кодов. Решение

📝 Постановка
🧑🏻‍💻 Автор

✅ Решение автора

with chars as
 (select case
           when level > 10 then
            chr(54 + level)
           else
            to_char(level - 1)
         end as letter
    from dual
  connect by level <= 36),
codes as
 (select c2.letter  c1.letter  c0.letter as code
    from chars c0, chars c1, chars c2)
select *
  from codes t
 where not exists (select 1 from mytbl f where t.code = f.code)
   and t.code between (select min(code) from mytbl) and (select max(code) from mytbl);

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

Oracle Developer
#конкурс #решениезадачи

Задача 6. Сбор мусора Михалычем. Решение

📝 Постановка
🧑🏻‍💻 Автор

✅ Решение автора

Примерный алгоритм:
1) Сортируем пакеты по убыванию мест в них и таким образом проставляем номер каждому пакету.
2) Для каждого пакета генерим строки с номерами мест для бутылок, которые в него могут влезть.
3) Сопоставляем место в пакете номеру бутылки.
4) Для каждого пакета агрегируем все бутылки, которые смогли сопоставить на шаге (3).

with 
-- пакеты
p as (
select 3 as cnt from dual
 union all
select 2 as cnt from dual
 union all
select 5 as cnt from dual
 union all
select 2 as cnt from dual
 union all
select 1 as cnt from dual
),
-- бутылки
b as (
 select level as num
   from dual
connect by level <= 11
)

-- пакеты с поместившимися бутылками
select 'Вместимость '||c2.pack_cnt as pack, 
       listagg(c2.bottle_num,', ') within group (order by c2.bottle_num) as bottles
  from (
-- бутылки на местах в пакетах
select c1.*,
       -- сопоставляем место в пакете номеру бутылки по порядку
       (select bb.bottle_num
          from (select row_number() over (order by b.num) as bottle_num from b) bb
         where bb.bottle_num = c1.pack_place) bottle_num
  from (
-- места в пакетах   
select p.pack_num,
       p.pack_cnt,
       row_number() over (order by p.pack_num,d.n) as pack_place
  from (select row_number() over (order by p.cnt desc) as pack_num, p.cnt as pack_cnt from p) p,
       (select level as n from dual connect by level <= (select max(p.cnt) from p)) d
 where d.n <= p.pack_cnt
 order by p.pack_num,d.n
       )c1 
       )c2
 group by c2.pack_num,c2.pack_cnt
 order by c2.pack_num;

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

Oracle Developer
#конкурс #решениезадачи

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

На этой и на прошлой недели было опубликовано 6 задачек.
Все они были разные. Какие-то было совершенно практические, какие-то на поболтать, сложные, легкие - на любой вкус и опыт.

Я запущу процесс голосования.
Итоги подведем 3️⃣1️⃣ декабря🎄

Победители получат денежные призы:
🔸Авторам задач: 1 место - 5К, 2 место - 3К, 3 место - 2К.
🔸Автору ответа в чатике (не важно на какую задачу), набравшему максимальное количество реакций.
Коллеги, не скупимся на реакции к ответам в чате 😉

Если #конкурс понравился ставьте 🔥, будем проводить периодически.

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

—-
Тем временем, продолжается набор на курсы:
🔸Основы Oracle PL/SQL - осталось 4 места.
🔸Основы оптимизации Oracle SQL - мест нет.
🔸Секционирование в Oracle - осталось 1 место.
За подробностями в личку.

Oracle Developer
#конкурс

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

Рад сообщить, о наборе на шестой поток курса “Основы Oracle PL/SQL”.

Он будет полезен: QA-инженерам, разработчикам (разного уровня), аналитикам.

Цель курса:
1️⃣ Научить вас читать чужой и писать свой PL/SQL-код, достаточный для современной разработки и закрытия рабочих задач.
2️⃣ Систематизировать знания.

🔹22 видео с теорией;
🔹24 практики = единый проект;
🔹13 онлайн встреч в Zoom с разбором вопросов;
🔹закрытая тг-группа;
🔹старт - 15 января 2024, 3 месяца.

Практика построена таким образом, чтобы вы с нуля, шаг за шагом, создали API на PL/SQL для прототипа платежной системы. Начав с анонимных блоков вы закончите своим мини-фреймворком для Unit-тестирования и использованием utPL/SQL. Пример.

Подробности и программа - здесь. Отзывы - здесь.

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

Осталось 4️⃣ места!
Бронируй сейчас.

Oracle Developer

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

На носу у нас новый год 🎄, а значит самое время подвести итоги:

1️⃣ Канал, наконец-то, перевалил цифру в 3000 подписчиков.
Уже давно не занимался его раскруткой, а это значит, что работает сарафанное радио 🗣

2️⃣ Было опубликовано более 100 постов.
Начали подключаться участники канала к написанию контента. Надеюсь эта тенденция сохранится ✏️

3️⃣ В чате сформировалось сообщество, которое готово прийти на помощь советом, кодом, мнением.
Спасибо, ребята!

4️⃣ Я дописал и провел первый поток по курсу "Основы оптимизации Oracle SQL".
Получился довольно качественный продукт, не похожий на аналоги.

5️⃣ И даже, провели первый конкурс 😊
Результаты, которого, пора подвести.

ТОП-3 задачки 🏆
1 место (5К) - сбор мусора Михалычем
2 место (3К) - создание deadlock
3 место (2К) - вывод календаря
отдельный приз (2К) - самый популярный ответ в чате. их было два.

Новогодние подарки победителям уже отправлены 🎁 🎈
Всем спасибо за участие, в следующем году повторим 👍

~~~~

Кстати, это уже четвертый (!) новый год, с которым я поздравляю вас в нашем канале 🥳

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

С наступающим 2️⃣0️⃣2️⃣4️⃣ годом! 🎄

Создатель канала Oracle Developer, Кивилёв Денис

Поздравить друг друга в чатике 💬 ☃️

#newyear

Задача. Повторная вставка без 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 | Чатик 💬