Как разработчик программного обеспечения или инженер, в какой-то момент нашей работы мы все имеем дело с разными механизмами SQL. Когда мы пишем SQL-запрос, в частности оператор SELECT, базовая база данных должна использовать план SQL-запроса в качестве карты, чтобы найти набор данных, который вы пытаетесь получить. Следовательно, как и все другие цифровые карты (например, Google Maps), ваша база данных должна искать наиболее оптимальный путь для выполнения вашего запроса. Это не что иное, как ваш план запроса, выбранный базой данных в заданных условиях.
С учетом сказанного у нас всегда есть выбор, чтобы построить лучший путь для вашего механизма SQL, чтобы предоставить вам наилучший возможный план запроса. Вопрос в том, как? Что ж, ничто не заменит организацию ваших таблиц таким образом, чтобы просмотр данных превратился в легкую прогулку.
Как следует из названия, здесь я расскажу о плане выполнения запросов в базе данных Oracle. За прошедшие годы база данных Oracle сильно изменилась, и сейчас мы наблюдаем время, когда база данных может выбрать лучший план SQL без нашего вмешательства. Сказав это, как разработчик SQL, мы не можем избежать плана запроса. Следовательно, давайте попробуем понять различные показатели плана выполнения Oracle SQL.
ПРИМЕЧАНИЕ. Ниже приведен пример SQL для объяснения нескольких терминов, которые мы собираемся использовать, чтобы понять, как создается план SQL с использованием инструкции SQL.
выберите * из отделов, где Department_id=20
В этом отделе_ид = 20 становится предикатом запроса, который конкретно сообщает запросу о том, какие данные требуются. Поскольку это определяется с помощью оператора «=», этот предикат становится предикатом равенства. Предикат является частью предложения WHERE.
Что такое план выполнения SQL?
Я бы сказал, что план выполнения Oracle SQL — это карта для обхода различных наборов данных, таких как таблицы и индексы. На высоком уровне, как и обычная карта, у нее будут разные маршруты (доступ к таблице), логистика (другой тип соединения) и командировочные расходы (стоимость запроса).
Как получить план выполнения SQL?
Наиболее распространенными способами создания плана запроса являются использование EXPLAN_PLAN команды и DBMS_XPLAN.DISPLAY функции.
С помощью команды EXPLAN_PLAN:-
Использование функции DBMS_XPLAN.DISPLAY :-
Что есть в плане выполнения?
В общем, у нас есть план выполнения SQL, который строго разделен на идентификатор, операцию, имя объекта, стоимость и количество элементов. Для лучшего понимания я попытался классифицировать их, как показано ниже.
- Расходы
- мощность
- Доступ к таблице
- Тип присоединения
Теперь давайте обсудим их подробно.
Стоимость. Oracle Optimizer — это оптимизатор, основанный на затратах. Когда мы запускаем SQL в Oracle DB, он извлекает несколько планов выполнения запросов, и один из многих выбирается оптимизатором и используется в качестве пути для извлечения данных. В том же контексте стоимость является расчетным представлением того, сколько ресурсов ЦП, ввода-вывода и сетевых ресурсов используется оптимизатором для выполнения различных операций для получения данных.
Стоимость – это предполагаемый объем работы, которую план должен выполнить.
Пожалуйста, обратитесь к изображению ниже.
Стоимость всего плана (указана в строке 0) и каждой отдельной операции отображается в плане выполнения.
Оптимизатор всегда выбирает план с наименьшей стоимостью. Ожидается, что чем ниже стоимость, тем более эффективным будет план.
Кардинальность: Кардинальность — это приблизительная оценка количества строк, возвращаемых за операцию. Это определяется различными аспектами, такими как динамическая выборка, распределение данных в столбце соединения.
Более высокая кардинальность => вы будете получать больше строк => вы будете выполнять больше работы => запрос займет больше времени. Таким образом, стоимость (обычно) выше.
Доступ к таблице:
Метод доступа — или путь доступа — показывает, как данные будут доступны из каждой таблицы (или индекса). Метод доступа показан в поле операции плана объяснения. См. ниже.
Oracle поддерживает доступ к таблице ниже
Полное сканирование таблицы: считывает все строки из таблицы и отфильтровывает те, которые не соответствуют предикатам предложения where. Имейте в виду, это дорогое сканирование. Полное сканирование таблицы будет использоваться, когда
Индекс не существует.
Предикат запроса применяет функцию к индексированному столбцу.
Выдается запрос SELECT COUNT(*), и индекс существует, но индексированный столбец содержит пустые значения.
предикат запроса не использует передний край индекса B-дерева.
Статистика таблицы устарела.
Таблица маленькая.
Таблица имеет высокую степень параллелизма.
> В запросе используется подсказка полного сканирования таблицы.
Доступ к таблице по ROWID. Идентификатор строки указывает файл данных и блок данных, содержащий строку, и расположение строки в этом блоке. Поиск строки по ее идентификатору строки — это самый быстрый способ получить одну строку, поскольку он указывает точное местоположение строки в базе данных. Когда оптимизатор выбирает доступ к таблице через Rowid
В большинстве случаев база данных обращается к таблице по идентификатору строки после сканирования одного или нескольких индексов.
Уникальное сканирование индекса. Уникальное сканирование индекса возвращает не более 1 идентификатора строки. Когда оптимизатор выбирает Индексировать уникальное сканирование
Он будет использоваться при наличии предиката равенства для уникального индекса (B-дерева) или индекса, созданного в результате ограничения первичного ключа.
Сканирование диапазона индексов. Сканирование диапазона индексов — это упорядоченное сканирование значений. Оптимизатор обычно выбирает сканирование диапазона для выборочных запросов.
По умолчанию база данных хранит индексы в порядке возрастания и сканирует их в том же порядке. Например, запрос
выберите * из отдела, где Department_id ›= 20
Выше с предикатомdepartment_id ›= 20 используется сканирование диапазона для возврата строк, отсортированных по индексным ключам 20, 30, 40 и т. д. Если несколько записей индекса имеют одинаковые ключи, база данных возвращает их в порядке возрастания идентификатора строки, так что за 0,AAAPvCAAFAAAAAFaAAa следует 0,AAAPvCAAFAAAAAFaAAg и т. д.
Сканирование диапазона индексов по убыванию идентично сканированию диапазона индексов, за исключением того, что база данных возвращает строки в порядке убывания. Обычно база данных использует сканирование по убыванию при упорядочении данных в порядке убывания или при поиске значения меньше указанного значения.
Когда оптимизатор выбирает Сканирование диапазона индексов
Один или несколько ведущих столбцов индекса указываются в условиях.
Условие задает комбинацию одного или нескольких выражений и логических (булевых) операторов и возвращает значение ИСТИНА, ЛОЖЬ или НЕИЗВЕСТНО. Примеры условий включают:
id_отдела = :id
id_отдела ‹ :id
id_отдела › :id
И комбинация предыдущих условий для ведущих столбцов в индексе, например, id_отдела › :low И id_отдела ‹ :hi.
Для ключа индекса возможны значения 0, 1 или более.
ПРИМЕЧАНИЕ.Чтобы оптимизатор рассмотрел сканирование диапазона, поиск с подстановочными знаками в форме
col1 LIKE '%ASD'не должен занимать первое место.
Полное сканирование индекса: полное сканирование индекса не считывает каждый блок в структуре индекса, вопреки тому, что следует из его названия. При полном сканировании индекса обрабатываются все листовые блоки индекса, но только достаточное количество блоков ветвей, чтобы найти первый листовой блок. Он используется, когда все столбцы, необходимые для выполнения оператора, находятся в индексе, и это дешевле, чем сканирование таблицы. Он использует одноблочные операции ввода-вывода. Его можно использовать в любой из следующих ситуаций.
Предикат ссылается на столбец в индексе. Этот столбец не обязательно должен быть ведущим столбцом.
Предикат не указан, но выполняются все следующие условия:
Все столбцы в таблице и в запросе находятся в индексе.
По крайней мере один проиндексированный столбец не пуст.
Запрос включает ORDER BY для индексированных столбцов, не допускающих значение NULL.
Быстрое полное сканирование индекса: Быстрое полное сканирование индекса считывает блоки индекса в несортированном порядке, как они существуют на диске. Это сканирование не использует индекс для проверки таблицы, но считывает индекс вместо таблицы, по существу используя сам индекс как таблицу.
Когда оптимизатор выбирает Быстрое полное сканирование индекса
Это альтернатива полному сканированию таблицы, когда индекс содержит все столбцы, необходимые для запроса, и хотя бы один столбец в ключе индекса имеет ограничение NOT NULL. Его нельзя использовать для устранения операции сортировки, поскольку доступ к данным не следует ключу индекса. Он также будет читать все блоки в индексе, используя многоблочное чтение, в отличие от полного сканирования индекса.
Сканирование с пропуском индекса. Индекс, созданный для нескольких столбцов таблицы, называется многостолбцовым, составным или составным индексом. Сканирование с пропуском индекса происходит, когда начальный столбец составного индекса «пропускается» или не указан в запросе.
Сканирование присоединения к индексу. Сканирование присоединения к индексу – это хэш-соединение нескольких индексов, которые вместе возвращают все столбцы, запрошенные запросом. Базе данных не требуется доступ к таблице, поскольку все данные извлекаются из индексов.
Стоимость извлечения строк из таблицы выше, чем чтение индексов без извлечения строк из таблицы. Соединение с индексом часто обходится дорого. Например, при сканировании двух индексов и их объединении зачастую дешевле выбрать наиболее селективный индекс, а затем исследовать таблицу.
Сканирование растрового индекса.Растровый индекс использует набор битов для каждого значения ключа и функцию сопоставления, которая преобразует позицию каждого бита в идентификатор строки. Oracle может эффективно объединять битовые индексы, которые соответствуют нескольким предикатам в предложении WHERE, используя логические операции для разрешения условий И и ИЛИ.
Тип присоединения:
Метод объединения описывает, как данные от двух операторов, производящих данные, будут объединены вместе. Вы можете определить методы соединения, используемые в операторе SQL, просмотрев столбец операций в плане объяснения.
Хэш-соединения: хэш-соединения используются для объединения больших наборов данных. Оптимизатор использует меньшую из двух таблиц или источников данных для построения в памяти хэш-таблицы на основе ключа соединения. Затем он сканирует большую таблицу и выполняет тот же алгоритм хеширования для столбцов соединения. Затем он проверяет ранее созданную хеш-таблицу на наличие каждого значения и, если они совпадают, возвращает строку.
Соединения с вложенными циклами. Соединения с вложенными циклами полезны, когда объединяются небольшие подмножества данных и если существует эффективный способ доступа ко второй таблице (например, поиск по индексу). Для каждой строки в первой таблице (внешней таблице) Oracle обращается ко всем строкам во второй таблице (внутренней таблице). Считайте это двумя встроенными циклами FOR.
Сортировка объединения слиянием. Сортировка объединения слиянием полезна, когда условие соединения между двумя таблицами является условием равенства, например, ‹, ‹=, › или ›=. Соединения слиянием с сортировкой могут работать лучше, чем соединения с вложенными циклами для больших наборов данных. Присоединение состоит из двух шагов:
Операция сортировки соединения: оба входа сортируются по ключу соединения.
Операция объединения слиянием: отсортированные списки объединяются.
Декартово соединение: декартово соединение происходит, когда оптимизатор соединяет каждую строку из одного источника данных с каждой другой строкой из другого источника данных. Обычно это признак плохого плана. Нам определенно нужно углубиться в Query, чтобы избежать такого типа соединения.
Это все, о чем я хотел поговорить. Спасибо за чтение. Пожалуйста, поделитесь своими отзывами и вопросами по адресу [email protected]
Использованная литература :
