Index Range scan
Индексное сканирование по диапазону.
Очень часто используемый вид сканирования. Применяется при поиске по диапазону значений или при равенстве из неуникальных индексов.
Поиски типа:
where col1 between или where col1 > … или where col2 = ...

Возможен при выполнении условий:
1. В условиях запроса используется один или несколько лидирующих столбцов индекса.
2. Индекс может быть уникальным или обычным.

Поиск в индексе происходит от узла в котором находится стартовое значение диапазона (берется из запроса). Затем происходит сканирование всех значений после этого стартового значения, в соответствии с условием. Если надо, алгоритм переходит с листа на лист, не поднимаясь к узлу. Помните, что листы связаны между собой как двусвязный список. Поэтому возможны любые направления прохода по индексу asc/desc.

В плане запроса: INDEX RANGE SCAN

Данные извлекаются в сортированном порядке. Это исключает доп сортировку при операциях group by/order by по ключу.

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

Пример 1.
create index emp_department_ix on employees (department_id);

select * from employees t where t.department_id = 10;

Будет range scan. Казалось бы, почему тут не unique scan? Потому что индекс emp_department_ix не уникальный. И в листах у него не уникальные значения, а значит, добравшись до искомого листа, придется проходить по диапазону равному = 10. Таких элементов будет больше чем “1”. Потому и применяется range scan.

Пример 2.
select * from employees t where t.employee_id <= 130;

В плане фигурирует range scan по unique index - emp_emp_id_pk. Казалось бы, индекс уникальный, почему не unique scan? Потому, что в where задан диапазон - меньше либо равно. При этом не важно, уникальный индекс или нет. При сканировании придется проходить листы/значения меньше числа 130 включительно.


#оптимизация #индекс

Задача: Необходимо написать запрос, который позволит понять, идентичны ли данные в двух таблицах. Порядок хранения данных в таблицах значения не имеет.

create table t1(a number, b number);
create table t2(a number, b number);

Пример данных:

T1:
a   b
1   1
2   2
2   2
3   3
4   4

T2:
a   b
1   1
2   2
3   3
3   3
4   4

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

Кстати, эта задача со свежего тестового задания “Совкомбанк”. Признаться, где-то я эти задачи уже видел... лет 10 назад 😉

У меня давно бродит идея собрать тестовые задания, которые вы получаете и создать некий банк заданий с решениями. Можно разместить их в github. Кое-что уже есть - Магнит, Беркут, Ингосстрах и др. разных лет.

Если мысль нравится и готовы поделиться заданиями/решениями - отправляйте в чат или личку. Размещу в github с сохранением авторства.

#задача

Index skip scan

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

Проще всего объяснить на примере таблицы employees.
Есть индекс:

create index emp_name_ix on employees (last_name, first_name);

Выполняется запрос:

select * from employees t where t.first_name = 'Mark';

В предикатах не задан лидирующий столбец (“last_name”) индекса.
Скорее всего, будет использоваться Index Skip Scan.

Как же он работает?
Для лидирующего столбца получаются все уникальные значения. Дальше для каждого уникального значения выполняется range scan с учетом значения заданных столбцов.

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

Возвращаясь к нашему примеру.

-- Уникальных значений 102.

select count(distinct t.last_name) from employees t;

Будет построено 102 условных поисковых дерева и по каждому будет совершен поиск по (last_name = ‘i-ое last_name’ and first_name = ‘Mark’).

Если лидирующий столбец имеет большое количество уникальных значений, скорее всего, index skip scan выбран не будет.
Но тут надо понимать, что лидирующим столбцом в B-tree индексах, лучше использовать столбец, как раз таки, с большим разнообразием значений.

Из практики, я не припомню, когда бы index skip scan был полезен. Разработчики старательно избегают его. Наличие skip scan - это повод для оптимизации.
Если есть какие-то узкие кейсы его полезности, напишите в комментариях. Всем будет полезно.

#оптимизация #индекс

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

Вы довольно активно поучаствовали в решение задачки вторника 🔥👍
Смотрите чатик под постом с задачей.

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

Решение, которое я когда-то сделал, на скорую руку: https://github.com/kivilev/test_tasks/blob/main/sovkombank/task1.sql

Планирую в этой репе размещать тестовые задания от вас. Так что не забудьте прислать 😉

Возможно, кому-то они помогут или натолкнут на интересную мысль 💡

Оцените задачку "Совкомбанка" ⬇️ 😁

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

Задача:
Есть таблица с данными в виде дерева. Необходимо написать запрос для получения дерева от корневого узла, узел 5 и все его потомки не должны попасть в результат, нужно вывести для каждого узла имя его родителя, данные отсортировать в порядке возрастания ID с учетом иерархии.

create table t (id number, pid number, nam varchar2(255 char));

Пример данных:
ID PID NAM
1 Корень
2 1 Узел2
3 1 Узел3
4 2 Узел4
5 4 Узел5
6 5 Узел6
7 4 Узел7

Требуемый результат:
ID PID NAM PARENT_NAM
1 Корень
2 1 Узел2 Корень
4 2 Узел4 Узел2
7 4 Узел7 Узел4
3 1 Узел3 Корень

Эта задача с тестового задания “Совкомбанк”. Коллегам с банка - привет 😉
Задача не сложная, но идеально подойдет тем, кто хочет попрактиковаться в написании иерархических запросов.

Велком в комментарии, предлагайте свои решения ⬇️

#задача

Index Full Scan

Сканируются все блоки индекса с сохранением упорядоченности данных.
Соответственно, возможно только одноблочное чтение (single block I/O). Лист за листом. Это одно из отличий от index fast full scan, о котором я расскажу в следующем посте.

Поскольку данные извлекаются все в сортированном виде, это помогает исключать дополнительный этап сортировки в таких операциях как merge join таблиц, обычная сортировка (order by), группировки (group by).

На всякий случай поясню, merge join перед соединением двух таблиц должен отсортировать множества. Группировка - так же требует сортировки множества.

Фактически, замена full table scan в некоторых ситуациях:
▫️данных в индексе достаточно для получения результата;
▫️есть шаг с доп. сортировкой в операциях типа order by, group by, join;
▫️порядок использования колонок в order by должен совпадать с порядком колонок в индексе;
▫️порядок использования колонок в group by может не совпадать с порядком в индексе.

#оптимизация #индекс

Задача:
Есть таблица с данными в виде дерева. Необходимо написать запрос для получения дерева от корневого узла, узел 5 и все его потомки не должны попасть в результат... Полную постановку смотрите в посте вторника.

На примере решения от Алексея Пугачева (@FleshyDumpling) - см комментарии под постом:

 select t.id, t.pid, t.nam, prior t.nam as parent_nam
   from t
connect by prior t.id = t.pid and t.id <> 5
  start with t.pid is null
  order siblings by t.id;

🔸connect by prior t.id = t.pid - проход с вершины в листы сверху-вниз.
🔸and t.id <> 5 - исключать узлы с id = 5
🔸start with pid is null - начинаем обход со всем узлов, у которых нет предка (= вершины).
🔸order siblings by t.id - сортировка внутри уровня по id

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

Решение в репозитории

#решениезадачи #иерархическиезапросы

Index Fast Full Scan (FFS)
Второй вид полного сканирования данных индекса.

В чем же отличия от Index Full Scan?

Считываются все блоки данных индекса в неупорядоченном виде. А если порядок не важен, значит можно блоки читать пачками (multiblock_read) и параллельно, как они хранятся в файлах. За счет чего достигается высокая скорость.

В Full Index Scan полное считывание происходит в упорядоченном виде, поэтому просто так взять и всё считать не получится. Грубо говоря, процессу чтения приходится “прыгать” из блока в блок, которые могут быть в разных файлах или разных частях одного и того же файла, чтобы сохранить упорядоченность.

FFS применяется, как меньшая по количеству чтений альтернатива Full Table Scan, если:
🔹 в индексе есть все необходимые столбцы для выдачи результата;
🔹 сортировка данных не важна;

Можно использовать через хинт - INDEX_FFS.

Надеюсь после этого поста, вас не смутит вопрос на собеседовании “в чем главное отличие Index fast full scan и index full scan”.

#оптимизация #индекс

Коллеги, всем привет!
Сегодня немного пофантазируем 🙌🏻

Вы разработчик баз данных в молодом банке “Пеньковв”. Проектируется информационная система. Ставится простая задача. Необходимо создать таблицу для хранения всех транзакций по счетам пользователей.

Объемы данных
Предположительные объемы данных - в месяц 100 млн строк. Количество транзакций будет потихоньку увеличиваться с ростом клиентской базы. Сильно не заморачиваясь, возьмем ориентировочно, в год - 1.2 млрд строк.

Срок жизни данных
🔸Доступ к данным по транзакциям в онлайн режиме (интернет-банк) пользователь может получить только за последний год с текущей даты.
🔸К данным за период от 1 до 3х лет, можно получить доступ только в отделении банка через оператора, с ожиданием в две недели.
🔸К данным за период с 3 лет так же через оператора, но вероятность такого обращения крайне мала, ожидание занимает один месяц.
🔸По требованиям ЦБ - необходимо хранить данные по транзакциям за последние 10 лет.
🔸Данные более 10 лет - можно смело удалять.

Руководство хочет, чтобы:
🔸вне зависимости от количества данных, запросы по отдельному клиенту выполнялись одинаково быстро.
🔸стоимость владения этими данными была как можно ниже.

Под снижением стоимости владения понимается использование более дешевых СХД (систем хранения данных):
🔸данные текущего года - на дорогих высокоскоростных СХД.
🔸с 1 до 3х лет - на более дешевых низкоскоростных СХД.
🔸более 3х лет - данные хранятся на недорогих ленточных СХД.

Это, вполне себе, обычная задача DBD уровня от крепкого мидла и выше.

Вы знаете, каким образом решить эту задачу? Как создать таблицу? Какие приемы использовать?
Будьте честны с собой и проголосуйте 😉

Подробности, как всегда, в четверг.

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

Судя по голосованию в посте вторника - тяга к знаниям присутствует. Не буду томить.
Такого рода задачи решаются достаточно эффективно с помощью секционирования (партиционирования).

Это один из важных компонентов СУБД Oracle при работе с very large database (VLDB). Рано или поздно, вы столкнетесь с секционированием в реальной жизни - либо на работе, либо на собеседовании. При этом не важно кто вы, Java-разработчик или DBD.

Когда-то я сам проходил через эти дебри. Это заняло достаточно много времени и сил. Вот если бы у меня тогда был грамотный наставник или подходящий курс… но вы счастливчики, у вас есть такой шанс 🔥 Представляю.

Online-интенсив “Секционирование в СУБД Oracle”
Это действительно уникальное событие. После двух дней совместного изучения и недели самостоятельной работы, вы наконец-то сможете использовать секционирование в реальной жизни, без проблем отвечать на собеседованиях и вырастите как специалисты.

С 11 до 18 в субботу (26.06) и воскресенье (27.06) будем разбирать теорию в Zoom, сразу же практиковаться в СУБД. В том числе будем разбирать задачу из поста вторника и другие кейсы. Самый сок, никакой воды.

Практика очень важна, поэтому для закрепления, будет недельная проектная работа по секционированию. В конце недели, мы разберем её на занятии, я дам обратную связь. Кстати, её вы сможете включить в свои pet-projects, повысив шансы на трудоустройство (актуально для молодых специалистов).

Хочется уделить внимание каждому участнику, поэтому количество мест ограничено. Всего - 20.
Будет ли другой поток? Посмотрим.

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

Возвращаясь к задаче вторника. Она может возникнуть на работе или на собеседовании уже завтра. Вы готовы к этому? Жмите кнопку, пока есть места ⬇️

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

Пакет DBMS_ROWID

Пакет интересен с точки зрения получения информация о ROWID (#rowid):
номер блока, номер объекта, номер файла и другие части составляющие rowid.

А зачем нам вся эта информация?

Бывает, полезно понимать какому объекту принадлежит строка. Осуществить это можно с использованием функции dbms_rowid.rowid_object.

Например, секционированная таблица - это логический объект. Физически, данные хранятся в секциях/подсекциях. Запросом ниже, мы сможем понять к какому конкретно физическому объекту относится строка.

select s.*, t.subobject_name
  from секционированная_табл s
  join user_objects t on t.object_id = dbms_rowid.rowid_object(s.rowid);

Это бывает необходимо при манипуляциях с секциями (очистка, обмен и т.п.).
Кстати, пока есть места в интенсиве по секционированию. Хотите знать больше? Записывайтесь.

#архитектура #rowid #dbms_rowid #секционирование

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

Задача:

drop table some_tab;

create table some_tab(
 field1 varchar2(10 char),
 field2 char(10 char)
);

insert into some_tab(field1, field2)
values ('abcd', 'abcd');
commit;

select count(1) cnt
  from some_tab
 where field1 = field2;

Как нужно изменить условие в where, чтобы при сравнении двух полей, результат запроса был “1”?

Существует, как минимум, два варианта решения. Подробности решения в посте четверга.

#задача

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

Решение:
Давайте разберемся, почему исходный запрос не работает?
Ведь было указано два одинаковых строковых литерала при вставке ('abcd').

Ответ кроется в типе колонки “field2”. Значения в типе char добиваются пробелами. До указанного размера в определении таблицы (в нашему случае - “10”). Физически в строках в блоках данных строки так и хранятся с пробелами.

На физическом уровне, тип varchar2 хранит размер конкретной строки и саму строку без каких-либо пробелов. Тип varchar2 используется в болшинстве случаев.

Char может использоваться для полей хранящих строки одинакового размера - типа M/F (пол), CA/NY/DE (штаты) и т.п.

В итоге, как сравнивать поля?
🔸 добиваем пробелами значение из поля с varchar2, чтобы оно было равно значению в поле char

where rpad(field1,10) = field2

🔸 убираем пробелы в значениях поле типа char

where field1 = trim(field2)

Знаете другие варианты решения? Напишите в комментах ⬇️

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

Задача:
Необходимо строку преобразовать в тип date одним запросом.
Формат преобразования: 'Month dd, YYYY, HH24:MI'

Особенность: если строка не подходит под формат, возвращать дату по умолчанию ("14.07.1983 14:42:00").

Например:
'Январь 21, 1984, 11:00' => 21.01.1984 11:00:00
'Января 21, 1984, 11:00' => ошибка => 14.07.1983 14:42:00 (по умолчанию)

Нельзя использовать PL/SQL-код. Версия СУБД от 12.2.

#задача

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

Решение:
В задаче два момента.
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 место. Спешите!

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