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 должен уметь использовать секционирование☝🏻

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

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