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

Имеется таблица mytbl. В ней есть столбец code, который содержит заглавные буквы и цифры длиной 3.
Между минимальным и максимальным значениями этого столбца необходимо найти недостающие коды.
Для примера, столбец может содержать такие значения как '8TS' или 'A56'. Но не содержит значения типа 'A_4', '7F' или '2 C'.

Тестовые данные:

create table mytbl(code varchar2(3));
insert into mytbl values('8TS');
insert into mytbl values('A56');
insert into mytbl values('912');
insert into mytbl values('BZ0');

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

Oracle Developer
#конкурс

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

Бомж-аутист Михалыч каждое утро обходит район и собирает в свои пакеты выброшенные пустые бутылки.

У Михалыча в силу обстоятельств каждое утро может быть разное количество пакетов разной вместимости (какой-то пакет вмещает 5 бутылок, какой-то - только 3 и т.д.). Количество найденных пустых бутылок каждое утро тоже может быть разным.

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

Напишите запрос, который для набора пакетов и количества бутылок нарисует таблицу Михалыча.

Например для исходных данных:

Пакеты:
Вместимость 3
Вместимость 2
Вместимость 5
Вместимость 2
Вместимость 1

Бутылки: 11 

Результат запроса должен быть таким:
------------------------------|
Пакет         | Бутылки       |
------------------------------|
Вместимость 5 | 1, 2, 3, 4, 5 |
------------------------------|
Вместимость 3 | 6, 7, 8       |
------------------------------|
Вместимость 2 | 9, 10         |
------------------------------|
Вместимость 2 | 11            |
------------------------------|
Вместимость 1 |               |
------------------------------|

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

Oracle Developer
#конкурс

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

На этой недели было опубликовано 6 конкурсных задачек.
На следующей - решения авторов и голосование. Итоги подведем 31го декабря ⛄️

Если вы были заняты на этой недели, вы можете уделить им время на выходных. Закидывайте решения в чатик 💬
Кто знает, вдруг именно ваш ответ наберет большинство реакций и вы получите 🎁

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

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

Oracle Developer
#конкурс

Задача 1. Вычисление факториала. Решение

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

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

with
step1(num) as
 (select 3 /*стартовое число*/
    from dual),
step2(x,y) as
 (select 1, 1
    from dual
  union all
  select x + 1, y * (x + 1)
    from step2, step1
   where x < step1.num)
select y as factorial 
  from step2, step1 
 where x = num;

⚠️ Напомню, что нет единственно правильного решения, поэтому прошу отнестись с пониманием к авторским решениям.

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

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

Задача 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