Ответы на вопросы с собеса в Сбер

Вопросы в предыдущем посте вторника.

Ответы
4. Что такое нормализация и денормализация? Приведенный пример это нормализованная БД?
Это основы проектирования сущностей в БД. Понятия гуглятся на раз-два. Чуть задену денормализацию. Хороший пример - это поисковые таблицы в OLTP-средах. Например, для этого кейса.

Что касается конкретного примера. Вопрос, конечно поставлен интересно "нормализованная ли это БД". Исходя из организации двух табличек, скорее, да. Не совсем ясно, что такое счет1/счет2. Являются ли счета отдельной сущностью или нет. Или достаточно текстового представления. Интервьювер явно хочет обсуждения 😉

5. Найти ошибку из приведенного ниже SQL-запроса.
Все просто. Алиас выражения "y" используется в качестве предиката в where. Будет ошибка.
Фикс: вместо "у" написать полное выражение.

11. Каков ответ на запрос ниже? Внесите исправления, чтобы запрос вел себя как ожидалось.
Ответ будет: not equals. Ибо null = null некорректное сравнение. Условие должны быть как null is null.
Подобные вопросы с сравнением null встречаются постоянно.

12. Что будет? select nvl(null,12) from dual;
Будет 12. Подробней про nvl в этом посте. Если еще вспомните про функцию coalesce, то завоюете сердце интервьювера 😁 Пост про отличия между nvl и coalesce.

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

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

Понравилось? Ставь 👍

#решениезадачи #собеседование #сбер
Oracle Developer

Решение задачи на особенности работы с null (Сбер)

Демо-данные для примера

create table test1(a number);
insert into test1 values(1);
insert into test1 values(2);
insert into test1 values(null);
insert into test1 values('');
commit;

Что нужно помнить
1️⃣ null - это неопределенность.
2️⃣ Кавычки без значения ('') - это null, если это не тип CHAR.
2️⃣ Для сравнения с null нельзя просто написать равенство или неравенство, нужно использовать is null или is not null
3️⃣ Использование null в IN бесполезно.

* - правила справедливы для Oracle

Что получим в итоге
Для краткости рассмотрим кейсы, когда А = 1 и A = null (для '' будет такой же)
A = 1
1) все строки, т.к. условие выполняется всегда
2) аналогично
3) аналогично
4) ничего 1!=1, условие всегда не выполняется
5) все строки, т.к. 1 in (1) всегда будет выполняться.
6) ни одной строки, т.к. условие никогда не будет выполнено.

А = null
1) все строки, т.к. условие выполняется всегда
2) ничего, null - особенное значение.
3) все строки, т.к. условие выполняется всегда
4) ничего, null сравниваем через is.
5) аналогично
6) аналогично.

Я не очень люблю подобные задачи... на собесе, помнится, в каких-то неочевидных пунктах запутался, бывает )

Итог: собеседование в Сбер прошел, предложили неплохую ставку, но все равно отказался 🤷🏻‍♂️ А вам достался материал 😉

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

#решениезадачи #собеседование #сбер
Oracle Developer

Решение задачи "вывод строчных английских букв"

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

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

Приведу буквально одно:

select chr(ascii('a') + level - 1) 
  from dual 
connect by level <= ascii('z') - ascii('a') + 1;

Всем неравнодушным респект, было интересно посмотреть на такое разнообразие 🔥

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

Решение задачи. Транспонирование данных

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

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

with exmpl as
 (select *
    from table(tt_tbl_row(tr_tbl_row(1,
                                     tt_dml(tr_dml('NAME', 'Иван'),
                                            tr_dml('SURNAME', 'Иванов'),
                                            tr_dml('MIDDLE_NAME', 'Иванович'),
                                            tr_dml('AGE', '45'),
                                            tr_dml('GENDER', 'M'))),
                          tr_tbl_row(2,
                                     tt_dml(tr_dml('NAME', 'Петр'),
                                            tr_dml('SURNAME', 'Петров'),
                                            tr_dml('MIDDLE_NAME', 'Петрович'),
                                            tr_dml('AGE', '35'),
                                            tr_dml('GENDER', 'M'))),
                          tr_tbl_row(3,
                                     tt_dml(tr_dml('NAME', 'Михаил'),
                                            tr_dml('SURNAME', 'Иванов'),
                                            tr_dml('MIDDLE_NAME', 'Михайлович'),
                                            tr_dml('AGE', '25'),
                                            tr_dml('GENDER', 'M'))))))
select *
  from (select e.id
              ,v.name
              ,v.value
          from exmpl e
         cross join table(e.t_row_val) v)
pivot (max(value) for name in('NAME' as name,
                         'SURNAME' as surname,
                         'MIDDLE_NAME' as middle_name,
                         'AGE' as age,
                         'GENDER' as gender))
 where upper(surname) like 'ИВАНОВ';

Объяснение

Имеем коллекцию, состоящую из id и вложенной таблицы key - value

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

|id | name |value |
|—-|—————|————|
|1 |NAME | Иван |
|1 |SURNAME |Иванов |

Для этого используем CROSS JOIN (так как надо соединить все строки вложенной таблицы с ее id) и функцию table(), позволяющую работать с вложенной таблицей так, как будто это обычная таблица

В полученном результате названия полей - значения в колонке name, что не позволяет фильтровать по ним данные.

Нам заранее известны названия полей, что позволяет транспонировать матрицу результатов предыдущего шага при помощи оператора PIVOT. По условию задачи данные валидны, поэтому можем использовать агрегатную функцию MAX (или любую другую, которая выведет нам значения из колонки value) внутри оператора PIVOT.

Транспонировав результат, остается только отфильтровать данные (с учетом регистра строки в поле SURNAME)

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

Коллеги, накидали вариантов в чате, тоже полезно заценить решения 😉

👍, если понравилась задачка.

Автор: Константин Андронов
#решениезадачи #pivot #транспонирование
Oracle Developer

Восстанавливаем текст запроса. Анализ

Давайте проведем анализ приведенного плана:
🔹select statement - значит выполняется select;
🔹nested loops - операция соединения множеств, значит, как минимум, есть join;
🔹порядок выполнения операций: 3-4-2-5-1-0;
🔹возле шагов с id =3 и 4 стоят звездочки, это отсылка к блоку "Predicate Information";
🔹на 3м шаге табличка departments фильтруется по полю manager_id с предикатом = :v с алиасом "t";
🔹на 4м шаге используется диапазонное сканирование индекса (range scan) с доступом по предикату соединения department_id с алиасом "e".

Уже только по "Predicate information" можно сделать вывод, что правильный вариант №2.

В этом плане мне нравится маленькая изюминка - наличие второго nested loops, т.е. второго соединения множеств.

Откуда он берется?
С 11й версии Oracle добавил оптимизацию получения данных.

В первом nested loops отбираются все необходимые строки для employees по индексу EMP_DEPARTMENT_IX, а уже затем результат соединения (2й шаг) соединяется с таблицей employees (5й и 1й шаги). Достаются необходимые колонки из employees для материализации результата. Наглядней было бы с дополнительным блоком "Column Projection".

Таким образом, в первом соединении не вытаскиваются лишние данные из employees (а вдруг они вообще не понадобятся, если не подойдут по предикатам соединения).
—
Задание направлено на понимание планов запросов, когда какие операции используются и т.п. Этакий reverse engineering.
Тут "изи" вариант - с вариантами выбора.

В моем курсе по оптимизации, мы разбираем подобные задачи для усвоения материала и натаскивания на собеседования ✅
Первый поток уже во всю идет, окончание в декабре 🎓

Понравилась задачка? Ставь 👍
Обсудить в чатике 💬

#оптимизация #решениезадачи
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
#конкурс #решениезадачи

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

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

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

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

Выполняется 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 | Чатик 💬

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

При решении задачи воспользуемся вторым подходом/типом 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 | Чатик 💬

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

При деактивации договора, в поле 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 | Чатик 💬

Задача с PL/SQL-коллекциями. Решение

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

Решение и анализ

1️⃣й вариант
🔹 t_arr - это ассоциативный массив, объявлен как локальный тип в анонимном PL/SQL-блоке. Ключ - числа, значение - varchar2(128).
🔹 a1 и a2 - это две переменных = два массива.
🔹 Инициализация асоциативного массива не требуется. Поэтому, когда происходит присвоение значения к a1(0) ошибки не будет. “0” - это ключ для значения ‘zero’.
🔹 a2 := a1; - значения первой коллекции копируются во вторую. Если бы это была Java, то мы бы присвоили ссылку a2 на а1.
🔹 a1(0) := 'empty'; - присваивается новое значение в старой коллекции, при этом значение в a2 не меняется (привет вопросам на собесе по Java).

Ответ: empty | zero

2️⃣й вариант
Я бы сказал, рассчитан на внимательность. Честно говоря, не очень люблю такие задачки. Особенно они популярны на Oracle-сертификации.
🔹t_arr и t_arr2 - два локальных типа ассоциативного массива. Да, по сути они одинаковы, но все же это разные типы.
🔹эта строка a2 := a1; вызовет глубокое недоумение у СУБД. Как объекты с разным типом могут быть присвоены друг другу? никак.

Ответ: Тут вообще, произойдет ошибка. Блок не выполнится. Ничего не будет выведено.

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

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

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

Решение задачи. Спроектировать отношение между таблицами.

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

Решение
1️⃣ Поскольку, у нас отношение многие-ко-многим, придется создать таблицу связки/маппинга, в которой будет храниться отношение сущностей к друг другу.

2️⃣ Как минимум три столбца:
id - уникальный ID в таблице
client_id - уникальный ID в таблице “клиенты”
address_id - уникальный ID в таблице “адреса”

Может возникнуть вопрос, зачем нам ID? ведь можно использовать пару столбцов для PK.
Генерация и хранение ID практически ничего не стоят. Как минимум, различного рода репликации, переливки ооочень любят ID, а еще б какую-нибудь временную метку.
С ID однозначно идентифицирующую строку - работать проще. В некоторых компаниях, явно прописывается наличие суррогантного ID в любой таблице.

3️⃣ Связка должна быть уникальна, поэтому будет уникальный ключ на пару столбцов - client_id/address_id.
За кадром, Oracle создаст уникальный индекс, который будет поддерживать уникальность на физическом уровне. При наличии этого индекса, мы легко сможем искать по client_id (лидирующий столбец).

4️⃣ Также необходим еще один индекс по address_id.
Почему? Велком на курс по Оптимизации 🎓

5️⃣ Не забудем про foreign keys на таблицы client и address, которые будут контролировать целостность данных.

Итого

create table client_address
(
id number(38) not null,
client_id number(38) not null,
address_id number(38) not null
);

alter table client_address add constraint client_address_pk primary key (id);

alter table client_address add constraint client_address_cl_ad_uq unique (client_id, address_id);

create index client_address_address_i on client_address (address_id);

alter table client_address add constraint client_address_address_fk foreign key (address_id)
references address (id);
alter table client_address add constraint client_address_client_fk foreign key (client_id)
references client (id);

Задачка на архитектуру - довольно простая и короткая, но очень показательная.
Бывает так, что ставит в тупик крепкого мидла или сеньера (по их версии). Лично мне она попадалась в Сбер-банке.

Я люблю её спрашивать на Mock-собеседованиях. Это такие тренировочные собеседования, симуляции. Можно без страха, попробовать, подсветить свои пробелы и т.д.
Например, перед тем как пробоваться на позицию Java-разработчика, я прошел парочку Mock-собеседований. Это хороший прогрев перед поиском. Если, вдруг, интересно попробовать - пишите в личку.

Да, тут много чего можно еще навертеть. Чем ребята в чатике успешно занимались 😉👍

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

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

Решение задачи. Оптимизация Min/Max запроса

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

Разберем задачку по оптимизации. Постановка в этом посте.

Отвечая на вопросы джуна
1️⃣ Все ли оптимально?
Нет, выполняется index full scan - упорядоченное одноблочное чтение всех строк из индекса.
Конечно, лучше, чем TABLE ACCESS FULL, но все же в данной ситуации можно этого избежать.

2️⃣ Если не ок, что можно изменить, не меняя структуру объекта?
Достаточно переписать запрос, разложить его на два: с MIN и с MAX.
Как только мы перепишем запрос, Oracle начнет применять операцию INDEX *** (MIN/MAX)
Вместо звездочек будет подставлен тот тип доступа к индексу, который подходит исходя из предикатов запроса.
В нашем случае, INDEX FULL SCAN.

В плане выполнения (см скрин), не смотря на усложнение исходного запроса, мы увидим минимальное количество A-rows (полученных строк). Операции с id = 2 и 4 возвращают по 1 строке. Считывая при этом минимальное количество блоков данных.

Было

select min(employee_id) min_id,
       max(employee_id) max_id
  from employees;

Стало

select (select min(employee_id) from employees) min_id
      ,(select max(employee_id) from employees) max_id
  from dual;

Что же это за оптимизация такая?
B-tree индекс - это дерево, в котором в листьях в упорядоченном виде хранятся ключи.
Попав в первый лист (крайний левый) и считав первое значение - получим MIN.
Попав в последний лист (крайний правый) и считав последнее значение в нем - получим MAX.
Так зачем сканировать весь индекс и считывать все строки?
Конечно, не зачем. Вот и вся суть операции INDEX *** (MIN/MAX).

В нашем случае, есть индекс EMP_EMP_ID_PK по полю employee_id, которое как раз используется в MIN/MAX.
—

В оптимизации запросов к СУБД Oracle, очень много мелких тонкостей. Абстракция SQL течет, и нам как писателям запросов, нужно понимать, что происходит "под капотом" 🎓

Пока суть да дело, уже третий поток набранный в мае, подходит к концу. Ребята недавно прошли дедлайн по домашкам, все сдают, всё нравится. Пройти секцию с оптимизацией на собеседовании для них становится плевым делом 😉

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

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

🌐 Навигация по темам канала Oracle Developer

📘 Основы
#sql #plsql #теория #архитектура
#функции #секционирование #временныетаблицы
#транзакции #исключения #курсоры
#аналитическиефункции #иерархическиезапросы
#системныепредставления #компиляция #представления #коллекции #циклы

🛠 Практика и задачи
#задача #решениезадачи #asktom

🚀 Оптимизация и производительность
#оптимизация #nestedloops #индекс

🧩 Инструменты и технологии
#тестирование #postgresql #oracle #docker #oracle23c #ide #ai

💼 Карьера и Развитие
#карьера #собеседование #hr #тинькофф #magnit #вкусноиточка #leroymerlin ​#сбер #яндекс #Эффективность #КарьерныйРост #Denis_Kivillev #Работа

🎓 Дополнительные материалы
#видео #подкаст #аудиоподкаст #конкурс #марафон #юмор

—
В почти 700 (!) постах не мудрено заплутать. Поэтому решил сделать подобие навигации 😉
Не забывайте пользоваться обычным поиском, я не добавил сюда низкочастотные хештеги.

Канал Oracle Developer | Чатик💬

Решение. Медленный запрос с продуктами.

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

Обсудим решение нашей простенькой задачки из предыдущего поста.
—
Итак, аналитик Василий обещал вам проставиться в пятницу бутылочкой вкусного пивка 🍺
А все потому, что вы ускорили его запрос!

К вам подошел коллега Игорь и решил поинтересоваться как вы справились с этой задачкой.

💭 "Игорь, это же элементарно! В предикатах участвуют два столбца category и price. Category имеет довольно разнообразное значение. Я где-то слышал, что такие значения называют высококардинальными значениями. И Price, само собой, тоже. Я подумал, что было бы круто создать составной индекс по этим двум полям. Отбираться будет меньше 5-15% от общего количества, а значит индекс будет эффективен", - довольно ответили вы 😊

И сразу же показали, скрипт создания индекса.

create index products_category_price_i on products (category, price);

А что бы убедить Игоря, показали explain план запроса (см скрин).

"Да! но как ты получил план запроса?", - спросил Игорь.
"А это уже профессиональная тайна", - довольно ответили вы 😊

Senior разработчик Кирилл, совершенно случайно услышавший ваш разговор, добавил: "вообще, можно было бы и одним полем обойтись" 😉

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

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