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 место. Спешите!

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

Друзья, всем привет!
Соскучились? )

Был занят проведением интенсива по секционированию.
Первый поток был успешно завершен 3 июля.

Коллеги послушали теорию, получили ответы на свои вопросы, успели применить на практике свои знания.
Фидбек отличный, ребятам зашло. Обязательно опубликую отзывы. Спасибо за участие 👍

Вывод простой. Тема актуальная, заинтересованность есть. Буду проводить еще, ближе к зиме.

В целом, планов громадьё! На подходе курс по PL/SQL, а там глядишь, и оптимизация подоспеет. Посмотрим, как будет хватать времени ⏳

Кстати, давно не было новых видосов. Теперь свободного времени больше, буду исправляться 😉

Всем хорошей трудовой недели 🛠

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

Задача: Есть две таблички. Связь Master(PK) - Detail(FK).

create table sale(
  sale_id number(30) primary key,
  sale_date date default sysdate
);

create table sale_detail(
  sale_id number(30) not null,  
  item_id number(20) not null,
  quantity number(10) not null,
  constraint sale_detail_fk
  foreign key(sale_id) references sale(sale_id)
);

Требуется произвести вставку именно в такой последовательности:

insert into sale_detail(sale_id, item_id, quantity) values (1, 1, 100);
insert into sale(sale_id, sale_date) values (1, sysdate);

При этом возникает такая ошибка:

ORA-02291: integrity constraint (xxx.SALE_DETAIL_FK) violated - parent key not found

Можно ли так делать? Если да, то, как исправить?
Порядок insert менять нельзя.

Детали в посте четверга 🎓

#задача

Отложенность проверки ограничений

У ограничения (key, check) можно задать свойство, когда это ограничение будет проверяется. Существует две разновидности:
1. IMMEDIATE - проверка осуществляется непосредственно при выполнении операции (по умолчанию).
2. DEFERRED - проверка осуществляется перед commit. При этом ограничение должно быть создано с возможностью включить этот тип (deferrable).

В большинстве кейсов, хватает первого вида. Но иногда, второй тип очень выручает.

Синтаксис:

alter table имя_табл add constraint имя_ограничения check (выражение) deferrable initially immediate;

alter table имя_табл add constraint имя_ограничения check (выражение) deferrable initially deferred;

Подробней в документации.

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

Простой пример. Две таблицы. Продажа и товары/позиции в продаже.
В позициях - задано количество каждого товара и цена одной единицы. В продаже - указывается сумма по всем позициям total_price - SUM(quantity*item_price).

create table sale(
  sale_id     number(30) primary key,
  sale_date   date default sysdate,
  total_price number(20,2)
);

create table sale_detail(
  sale_id    number(30) not null,  
  item_id    number(20) not null,
  quantity   number(10) not null,
  item_price number(10,2) not null
  constraint sale_detail_fk foreign key(sale_id) references sale(sale_id)
);

При создании продажи и детализации, можно поступить так:
1. Создать продажу с нулевой общей суммой. Получить ID-продажи.
2. Используя ID продажи, вставить все позиции и посчитать сумму.
3. Выполнить обновление поля “общая сумма” в продаже.
⚠️ Не очень оптимально, появляется лишний update.

Можно пойти другим путем.
1. Сгенерировать ID-продажи (скорее всего, sequence).
2. Вставить позиции, посчитав при этом “общую сумму”.
3. Вставить саму продажу с ID и рассчитанной “общей суммой”.

❗️ Конечно, FK должен быть отложенным, иначе при попытке вставки в sale_detail возникнет ошибка (нет записи в sale).

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

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

#constraint

Задача: Есть две таблички. Связь Master(PK) - Detail(FK).
В Detail таблицу производится вставка. Такого ключа в Master-таблице нет.
Возникает ошибка.

Решение:
Изменить определение foreign key. C незамедлительной проверки (immediate, по умолчанию) на отложенную проверку (deferred).

alter table sale_detail drop constraint sale_detail_fk;
alter table sale_detail add constraint sale_detail_fk foreign key (sale_id)
references sale(sale_id)
deferrable initially deferred;

В таком случае, целостность данных будет проверяться только в момент commit.
На табличке, которая уже во всю используется такой фокус по удалению/созданию может не прокатить, зависит от вашей системы - 24/7.
Вероятно, придется использовать свойство ограничения Novalidate. Как-нибудь, расскажу о нем.

Возьмите себе на заметку, когда возникает задача типа “кто был первым курица или яйцо”, скорее всего, вам нужны отложенные проверки.

Если задачка понравилась - ставьте 👍

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

Задача:
В таблице event есть колонка event_unix_time. Это дата/время в Unix time. Число.
Необходимо организовать быстрый поиск по этой колонке.

create table event(
  event_id        number(30) primary key,
  event_unix_time number(21) not null,
  message         varchar2(200 char) not null
);

insert into event values (1, 1113135689, 'm1');
insert into event values (2, 1626075857, 'm2');
insert into event values (3, 1626000057, 'm3');
commit;

Например, такой запрос:

select * from event t where что-то <= date '2020-07-01';

результат: строка с event_id = 1.

Усложнение задачи для пытливых умов: считать время по Московской таймзоне.

#задача

Deterministic-функции

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

У меня для вас свежее видео 🎥
В этот раз копнул в сторону детерминированных/чистых PL/SQL-функций.
Что это такое? Когда использовать? Как можно ускорить выполнение SQL-запросов, используя их?

Ответы на эти вопросы смотрите в уроке. Надеюсь, понравится 😉

Кстати, если помните, весной в голосовании вы выбрали урок про виды индексного доступа. Концепция урока пока бродит в голове. Хочется разжевать тему доступно. Есть желание, попробовать разнообразить его анимацией, чтобы он был наглядней.
К сожалению, Гугл презентации не так богаты в части анимации. Если кто-то встречал софт по созданию визуализаций для алгоритмов - напишите, буду благодарен.

А пока, приятного просмотра (6 минут).

#видео #plsql #функции #deterministic

Задача о поиске по колонке Unix time
постановку см. в посте вторника.

Решение:
Как минимум, два способа:
1️⃣ Создать функциональный индекс по функции преобразования столбца event_unix_time.
2️⃣ Создать виртуальную колонку с использованием функции преобразования + индекс по ней.

Для обоих этих случаев, было бы удобно использовать функцию конвертации из числового unix time в оракловый формат date.

Например, такую:

create or replace function unixtime_to_date(p_unix_time number)return date
deterministic
is
begin
  return(timestamp '1970-01-01 00:00:00 GMT' +
         numtodsinterval(p_unix_time, 'second')) at time zone 'Europe/Moscow';
end;

Просто функцию использовать не получится, нужно объявить её deterministic.
Что это такое и с чем это едят - смотрите в видео-уроке (предыдущий пост).

Решение 1

create index event_unix_time_idx on event(unixtime_to_date(event_unix_time));
select * from event t where unixtime_to_date(event_unix_time) <= date '2020-07-01';

Решение 2

create table event(
  event_id         number(30) primary key,
  event_unix_time  number(21) not null,
  event_dtime      date as (unixtime_to_date(event_unix_time)) not null,
  message          varchar2(200 char) not null
);

create index event_unix_time_idx on event(event_dtime);
select * from event t where event_dtime <= date '2020-07-01';

Если задачка понравилась - ставьте 👍

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

Задача

У нас в канале специалисты различного уровня, поэтому сегодняшняя задачка будет не сильно сложной 😉

Необходимо найти количество восклицательных знаков в строке:
'Hello World! It is a good day!!!'

В данном случае, запрос должен вернуть “4”.

⚠️ Использовать можно только SQL.

🍅 Для сеньоров-помидоров можно чуть усложнить: найдите не менее трех способов решить задачу.

Рекомендую не смотреть комментарии, пока не решите сами 😉

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

#задача