Виды соединения таблиц
Представьте запрос типа:
Существуют три основных типа соединения:
1. Nested loops (цикл в цикле).
2. Merge join (соединение слиянием).
3. Hash join (hash-соединение).
Каждый тип соединения подходит для своих ситуаций. Зависит от структур данных, количества выбираемых данных и других факторов.
Чаще всего, особенно в OLTP-системах, подходит nested-loops.
СУБД выбираем автоматически какой тип соединения применить. Иногда оптимизатор ошибается. При помощи хинтов, мы можем подсказать, какой тип выбрать.
Важно не путать соединение на логическом уровне (left join, cross join, join и др.) c физическим уровнем.
В следующем посте, расскажу про соединение через nested loops.
#оптимизация #nestedloops #hashjoin #mergejoin
Представьте запрос типа:
select *Задумывались ли вы, как СУБД будет их соединять?
from T1
join T2 on T2.t1_id = T1.id;
Существуют три основных типа соединения:
1. Nested loops (цикл в цикле).
2. Merge join (соединение слиянием).
3. Hash join (hash-соединение).
Каждый тип соединения подходит для своих ситуаций. Зависит от структур данных, количества выбираемых данных и других факторов.
Чаще всего, особенно в OLTP-системах, подходит nested-loops.
СУБД выбираем автоматически какой тип соединения применить. Иногда оптимизатор ошибается. При помощи хинтов, мы можем подсказать, какой тип выбрать.
Важно не путать соединение на логическом уровне (left join, cross join, join и др.) c физическим уровнем.
В следующем посте, расскажу про соединение через nested loops.
#оптимизация #nestedloops #hashjoin #mergejoin
August 26, 2020
Алгоритм соединения вложенными циклами (Nested loops join) — разновидность алгоритма соединения.
В общем случае, алгоритм получает на вход n таблиц и условия соединения. Результатом его работы является набор строк с результатами соединения.
Упрощая до двух таблиц, алгоритм можно описать следующим образом: для каждой строки одной из таблиц (ведущей) выполняется поиск в другой таблице (ведомой) строк, соответствующих условию соединения.
Псевдокод работы:
В плане запроса выглядит как показано на скриншоте. Шаг соединения двух таблиц - NESTED LOOPS.
В следующем посте расскажу когда и где применять.
#оптимизация #nestedloops
В общем случае, алгоритм получает на вход n таблиц и условия соединения. Результатом его работы является набор строк с результатами соединения.
Упрощая до двух таблиц, алгоритм можно описать следующим образом: для каждой строки одной из таблиц (ведущей) выполняется поиск в другой таблице (ведомой) строк, соответствующих условию соединения.
Псевдокод работы:
Для каждой строки [r] из [Ведущая таблица]Крайне желательно, чтобы у ведомой таблицы был индекс на поля соединения. Привет индексам на foreign key.
Для каждой строки [s] из [Ведомая таблица]
Если УдовлетворяетУсловию ([r],[s],[Условие соединения])
Вывести ([r],[s]);
В плане запроса выглядит как показано на скриншоте. Шаг соединения двух таблиц - NESTED LOOPS.
В следующем посте расскажу когда и где применять.
#оптимизация #nestedloops
August 28, 2020
Когда использовать Nested loops?
Как вы уже поняли из предыдущего поста данный метод проще всего представить в виду двух циклов: первый - верхний, второй - вложенный.
Соответственно, чем меньше итераций на каждом из циклов тем лучше.
Крайне желательно, чтобы выбирался незначительный объем как из первой так и из второй таблиц. На первое место ставят таблицу с низким количеством записей удовлетворяющим условиям в where, и дальше от неё пляшут.
Идеальный случай:
1. В первую таблицу заходим по PK или низкоселективному индексу.
2. Во вторую таблицу заходим по низкоселективному индексу.
По поводу индекса во второй таблице, скорее всего, это будет индекс на поля связки с master-таблицей. Это одна из причин, почему на FK делают индексы.
Речь шла про оперативные таблицы, а не справочники с малым количеством записей.
#оптимизация #nestedloops
Как вы уже поняли из предыдущего поста данный метод проще всего представить в виду двух циклов: первый - верхний, второй - вложенный.
Соответственно, чем меньше итераций на каждом из циклов тем лучше.
Крайне желательно, чтобы выбирался незначительный объем как из первой так и из второй таблиц. На первое место ставят таблицу с низким количеством записей удовлетворяющим условиям в where, и дальше от неё пляшут.
Идеальный случай:
1. В первую таблицу заходим по PK или низкоселективному индексу.
2. Во вторую таблицу заходим по низкоселективному индексу.
По поводу индекса во второй таблице, скорее всего, это будет индекс на поля связки с master-таблицей. Это одна из причин, почему на FK делают индексы.
Речь шла про оперативные таблицы, а не справочники с малым количеством записей.
#оптимизация #nestedloops
August 31, 2020
Пример:
Есть клиент, у него есть банковские счета.
Таблица клиентов - 10М, банковских счетов - 100М.
Мы знаем PK клиента, нам нужно вернуть информацию по клиенту и всю информацию по банковским счетам.
Nested loops подходит идеально для этого случая. Для первого цикла будет выбрана одна запись (PK), для второго цикла от силы до 10 записей.
Есть клиент, у него есть банковские счета.
Таблица клиентов - 10М, банковских счетов - 100М.
Мы знаем PK клиента, нам нужно вернуть информацию по клиенту и всю информацию по банковским счетам.
Nested loops подходит идеально для этого случая. Для первого цикла будет выбрана одна запись (PK), для второго цикла от силы до 10 записей.
select cl.*, ba.*#оптимизация #nestedloops
from client cl
join bank_account ba on ba.cl_id = cl.cl_id
where cl.cl_id = … ;
August 31, 2020
Nested loops, коллекции и хинты
Довольно часто коллекции используются как источник данных.
Например:
На каком-то из предыдущих шагов нашей программы мы наполнили коллекцию. Значения в ней это number. ID наших клиентов. По этим клиентам мы должны получить информацию по банковским счетам. И что-то сделать с результатом.
Код:
Смотрим предполагаемый план выполнения запроса. Он в заголовке.
Oracle предлагает соединять таблицы через hash_join. В run-time план может поменяться, но тем не менее почему так получилось?
#оптимизация #nestedloops #hints
Довольно часто коллекции используются как источник данных.
Например:
На каком-то из предыдущих шагов нашей программы мы наполнили коллекцию. Значения в ней это number. ID наших клиентов. По этим клиентам мы должны получить информацию по банковским счетам. И что-то сделать с результатом.
Код:
create or replace type t_numbers is table of number(30);Идеальный вариант для nested loops. На первой итерации всего три строки. Вторая итерация, так же отберет небольшое количество (до 10 счетов на одного клиента).
/
declare
v_ids t_numbers := t_numbers(10, 999, 7777);
begin
for i in (select *
from table(v_ids) t
join bank_account ba on ba.cl_id = value(t)) loop
-- что-то делаем
end loop;
end;
/
Смотрим предполагаемый план выполнения запроса. Он в заголовке.
Oracle предлагает соединять таблицы через hash_join. В run-time план может поменяться, но тем не менее почему так получилось?
#оптимизация #nestedloops #hints
September 2, 2020
Потому что Oracle заранее не знает, какое количество элементов\строк будет в коллекции.
1. Если мы знаем хотя бы порядок, можно использовать хинт cardinality.
2. Далее, мы понимаем, что эта коллекция должна идти первой в соединении - используем хинт leading.
3. И наконец, понимая, что nested loops будет лучшим решением в данной ситуации задаем хинт use_nl.
Таким образом, мы жестко забиваем гвоздями план выполнения.
Итоговый запрос:
Не везде и всегда это подходит, поэтому я утверждаю, что оптимизация довольно сложный процесс.
Пожалуй, на этом примере, я пока остановлю свои посты про оптимизацию SQL-запросов. В следующих про PL/SQL 😉
#оптимизация #nestedloops #hints
1. Если мы знаем хотя бы порядок, можно использовать хинт cardinality.
2. Далее, мы понимаем, что эта коллекция должна идти первой в соединении - используем хинт leading.
3. И наконец, понимая, что nested loops будет лучшим решением в данной ситуации задаем хинт use_nl.
Таким образом, мы жестко забиваем гвоздями план выполнения.
Итоговый запрос:
select /*+ cardinality(t 10) use_nl(t ba) leading(t ba) */План в заголовке.
*
from table(t_numbers(:v_ids)) t
join bank_account ba on ba.cl_id = value(t);
Не везде и всегда это подходит, поэтому я утверждаю, что оптимизация довольно сложный процесс.
Пожалуй, на этом примере, я пока остановлю свои посты про оптимизацию SQL-запросов. В следующих про PL/SQL 😉
#оптимизация #nestedloops #hints
September 2, 2020
🌐 Навигация по темам канала Oracle Developer
📘 Основы
#sql #plsql #теория #архитектура
#функции #секционирование #временныетаблицы
#транзакции #исключения #курсоры
#аналитическиефункции #иерархическиезапросы
#системныепредставления #компиляция #представления #коллекции #циклы
🛠 Практика и задачи
#задача #решениезадачи #asktom
🚀 Оптимизация и производительность
#оптимизация #nestedloops #индекс
🧩 Инструменты и технологии
#тестирование #postgresql #oracle #docker #oracle23c #ide #ai
💼 Карьера и Развитие
#карьера #собеседование #hr #тинькофф #magnit #вкусноиточка #leroymerlin #сбер #яндекс #Эффективность #КарьерныйРост #Denis_Kivillev #Работа
🎓 Дополнительные материалы
#видео #подкаст #аудиоподкаст #конкурс #марафон #юмор
—
В почти 700 (!) постах не мудрено заплутать. Поэтому решил сделать подобие навигации 😉
Не забывайте пользоваться обычным поиском, я не добавил сюда низкочастотные хештеги.
Канал Oracle Developer | Чатик💬
📘 Основы
#sql #plsql #теория #архитектура
#функции #секционирование #временныетаблицы
#транзакции #исключения #курсоры
#аналитическиефункции #иерархическиезапросы
#системныепредставления #компиляция #представления #коллекции #циклы
🛠 Практика и задачи
#задача #решениезадачи #asktom
🚀 Оптимизация и производительность
#оптимизация #nestedloops #индекс
🧩 Инструменты и технологии
#тестирование #postgresql #oracle #docker #oracle23c #ide #ai
💼 Карьера и Развитие
#карьера #собеседование #hr #тинькофф #magnit #вкусноиточка #leroymerlin #сбер #яндекс #Эффективность #КарьерныйРост #Denis_Kivillev #Работа
🎓 Дополнительные материалы
#видео #подкаст #аудиоподкаст #конкурс #марафон #юмор
—
В почти 700 (!) постах не мудрено заплутать. Поэтому решил сделать подобие навигации 😉
Не забывайте пользоваться обычным поиском, я не добавил сюда низкочастотные хештеги.
Канал Oracle Developer | Чатик💬
October 31, 2024