Index skip scan oracle что это
Обновлено: 06.07.2024
Меня это немного смущает, надеюсь, кому-нибудь поможет. Я читаю отличную книгу Маркуса Винанда Use The Index Luke и кое-что о конкатенированных индексах.
Создан индекс (EMPLOYEE_ID, SUBSIDIARY_ID) , поэтому, когда он запрашивает
Вот такой план выполнения:
Но вот в чем дело: в моей таблице сотрудников (empno, ename, init, job, mgr, bdate, msal, comm, deptno) я создал объединенный индекс для (ENAME, JOB)
Запрос select ename from employees where job = 'TRAINER'; дает мне следующий план выполнения:
Так что теперь я немного запутался.
1) Почему, несмотря на заказ, мой индекс все еще использовался?
2) Работает ли сканирование с пропуском индекса для любого объединенного индекса, в котором я не использую первый столбец в предложении where?
3) Влияет ли сканирование с пропуском индекса на производительность?
4) Почему существует и предикат доступа, и предикат фильтра?
И пока я здесь, у меня есть еще один вопрос
5) Нужно ли мне принимать меры при индексации дат?
3 ответа
Очевидно, что если бы ваш запрос содержал и ENAME , и JOB , то Oracle использовал бы индекс либо с INDEX RANGE SCAN , либо с INDEX UNIQUE SCAN . Однако передний край индекса, ENAME , не был указан в предикатах запроса. Итак, у Oracle Cost Based Optimizer (CBO) есть выбор. Он может выбрать выполнение FULL TABLE SCAN (игнорируя индекс) или INDEX SKIP SCAN .
Полагаю, вы знаете, что такое FULL TABLE SCAN , поэтому я не буду вдаваться в подробности.
Итак, что такое INDEX SKIP SCAN ? Что ж, CBO имеет возможность, в зависимости от формы и размера индекса, выполнить сканирование с пропуском. Обычно это происходит, когда имеется относительно небольшое количество различных значений для ведущего столбца индекса. Что происходит, Oracle берет индекс и фактически разбивает его на несколько индексов. Предположим, что в ведущем столбце 4 различных значения (1–4). Итак, Oracle просматривает подмножество индекса, в котором ведущий столбец равен 1, и выполняет сканирование диапазона этого подмножества индекса, затем делает то же самое для подмножеств индекса, где ведущий столбец равен 2, затем 3, затем 4. В некоторых случаях, в зависимости от того, сколько различных значений имеет ведущий столбец и насколько избирательно сканирование диапазона во втором столбце, этот путь доступа может быть менее затратным, чем FULL TABLE SCAN .
Это еще одна причина, по которой при прочих равных условиях вы можете разместить меньше выборочных столбцов на переднем крае индекса. (Другая основная причина - сжатие.)
1) Именно для этого и нужен INDEX SKIP SCAN.
2) Да, можно, но это зависит от вашей статистики
3) Может, а может и нет -> зависит от вашей статистики
4) Путь доступа касается выбора блоков данных для загрузки, а фильтр - того, как отфильтровываются строки из блока данных.
5) Индекс по ДАТЕ работает аналогично индексу по другим типам данных. DATE имеет длину 7 байтов.
Oracle имеет возможность с помощью сканирования с пропуском индекса использовать составной индекс, когда вы не указываете ведущий столбец индекса в своем предикате. Однако, как правило, это намного менее эффективно, чем обычное сканирование индекса. Концептуально вы можете представить себе сканирование индекса для каждого отдельного значения ведущего столбца индекса. Обычно Oracle рассматривает такой план только в том случае, если ведущий столбец имеет несколько различных значений, а конечный столбец (столбцы) особенно избирательны. Я бы не ожидал, что это будет правдой - по-видимому, ename почти уникален, а job гораздо менее избирательно. Я ожидал, что полное сканирование таблицы будет более эффективным, так что я предполагаю, что что-то в вашей статистике "шаткое". Если ваша таблица особенно мала, это, безусловно, может привести к тому, что планы запросов будут необычными просто потому, что каждый план кажется исключительно дешевым.
В реальном мире очень мало случаев, когда кто-то видит «сканирование с пропуском индекса» в плане запроса и думает: «Отлично! Это тот план, который мне нужен». Обычно это означает, что что-то пошло не так, но, возможно, все пошло не так далеко, как могло бы быть.
Не найдя на хабре статьи, объединяющей в удобном для чтения виде информацию о методах доступа к данным, используемых СУБД Oracle, я решил совершить «пробу пера» и написать эту статью.
Общая информация
Не углубляясь в детали, можно утверждать что Oracle хранит данные в таблицах, вместе с которыми могут существовать особые структуры данных – индексы, призванные ускорить запросы к таблицам. При выполнении запросов Oracle по-разному обращается к таблицам и индексам – способы доступа к данным в различных ситуациях и являются предметом этой статьи.
Для примеров мы будем использовать следующую таблицу и данные в ней:
Для анализа плана выполнения запроса будем пользоваться следующими средствами:
После создания индекса и использования его в примерах и перед созданием следующего индекса, он должен быть удален. Это можно сделать с помощью следующего запроса:
TABLE FULL SCAN
Данный метод доступа, как следует из названия, подразумевает перебор всех строк таблицы с исключением тех, которые не удовлетворяют предикату where (если таковой есть). Применяется он либо в случае, когда условия предиката отсутствуют в индексе, либо когда индекса нет в принципе. Примеры:
TABLE ACCESS BY ROWID, он же ROWID
- Мы указали идентификатор строки в предикате where;
- ROWID запрошенной записи был найден в индексе;
INDEX FULL SCAN
Данный метод доступа просматривает все листовые блоки индекса для поиска соответствий условиям предиката. Для того чтобы Oracle мог применить этот метод доступа, хотя бы одно из полей ключа должно иметь ограничение NOT NULL, т.к. только в этом случае соответствующая строка таблицы попадет в индекс. Этот метод обычно быстрее чем TABLE FULL SCAN, но медленнее, чем INDEX RANGE SCAN (см. ниже).
INDEX FAST FULL SCAN
Этот метод доступа применяется, когда выполнены все требования для INDEX FULL SCAN, а также все данные, выбираемые запросом, содержатся в индексе и таким образом доступ к самой таблице не требуется. В отличие от INDEX FULL SCAN этот метод может читать блоки индекса в несколько параллельных потоков и таким образом порядок возвращаемых значений не регламентирован. Oracle также не может использовать этот метод для bitmap-индексов.
INDEX RANGE SCAN
Данный метод доступа используется Oracle в том случае, если в предикат where входят столбцы индекса с условиями = (в случае если индексированные значения неуникальны), >, <, а также like ‘pattern%’, причем wildcard-символы должны стоять после искомой подстроки. В отличие от TABLE FULL SCAN, при использовании этого метода доступа Oracle не перебирает все листовые блоки и поэтому в большинстве случаев INDEX RANGE SCAN быстрее.
Пример:
INDEX UNIQUE SCAN
Данный метод доступа применяется когда в силу ограничений UNIQUE/PRIMARY KEY, а также условия предиката, запрос должен вернуть ноль или одно значение.
Пример:
INDEX SKIP SCAN
Этот метод доступа используется в случае, если в предикате where не используется первый столбец индекса.
Для примера использования этого метода доступа нам потребуется другая таблица (обратите внимание, что количество строк, данные и т.д. будут зависеть от того, что есть в используемой схеме, и поэтому данный пример может не воспроизвестись сразу):
Индексы - это специальные объекты в БД, которые хранят в себе записи для каждого встречающегося в индексированной колонке значения. Внутреннее устройство индексов таково, что они позволяют быстро находить строки в таблице, содержащие определенные значения.
Чтобы лучше понять как работает индекс и для чего он нужен, представьте телефонный справочник, в котором абоненты расположены в случайном порядке. Чтобы найти нужного абонента, придется просматривать все записи в справочнике по порядку до тех пор, пока не встретится нужный. Но если расположить всех абонентов в алфавитном порядке, по адресам и т.д, то времени на поиски будет затрачено гораздо меньше. Для того же и используются индексы в БД - чтобы уменьшить время на поиски нужных записей в таблице.
То есть индексы - это отдельные объекты в БД, которые используются для того, чтобы ускорить поиск данных. В Oracle существует несколько типов индексов, далее здесь будут рассматриваться так называемые "B-tree" индексы. Это "классический" тип индексов, который используется на практике, и часто, когда говорят слово "индекс", то подразумевают именно его.
Создание индекса
Создадим индекс на колонку dept_id в таблице employees :
Здесь employee_dept_id_idx - это имя индекса, оно могло быть любым.
На длину имен индексов также действует ограничение в 30 символов.
При создании первичного или уникального ключа в таблице Oracle создает индексы на эти колонки автоматически.
Удаление индекса
Индексы удаляются по своим именам. Удалим индекс employee_dept_id_idx :
Составные индексы
Индексы могут создаваться на несколько колонок. Такие индексы называются составными.
Составные индексы можно добавлять тогда, когда в таблице ищут данные сразу по нескольким колонкам. Порядок колонок в составном индексе важен. Однозначного правила, которое бы работало всегда, нет, но чаще всего следует добавлять колонки в индекс в порядке:
- Частоты их использования в запросах
- Количества уникальных значений, содержащихся в колонке.
Рассмотрим это на примере. Если у нас есть таблица employees , из которой часто получают данные по фамилии и коду должности, т.е. из таблицы часто запрашивают данные в подобном виде:
То в таком случае можно попробовать добавить составной индекс, состоящий из колонок emp_name и job_code :
Порядок колонок в индексе не обязательно должен совпадать в порядке встречи колонок в условии запроса; Исходим мы здесь из того предположения, что:
- По имени будут искать гораздо чаще
- Количество уникальных имен в таблице больше, чем количество уникальных должностей.
До версии 9i порядок колонок в индексе был более важен, т.к. обязательным условием использования индекса было обращение в запросе к колонке, которая является первой в индексе. В нашем случае, если бы мы написали запрос следующим образом:
индекс не использовался бы, т.к. в запросе нет фильтрации по колонке emp_name , которая идет первой в составном индексе.
Index skip scan
Начиная с версии 9i в Oracle появилась возможность использовать составной индекс, даже если его лидирующая колонка не используется в запросе.
Index skip scan может использоваться тогда, когда количество уникальных значений лидирующей колонки индекса относительно невелико. Опять же, решение об использовании или неиспользовании принимает оптимизатор Oracle.
По-прежнему, лучше стараться создавать составные индексы таким образом, чтобы лидирующая колонка использовалась чаще всего, так как наличие возможности использования index scip scan вовсе не означает, что он будет использован всегда.
Зачем использовать составные индексы
- Могут сократить кол-во записей за счет комбинирования колонок
- Если выбираются только колонки из индекса, то это сократит количество операций чтения, т.к. в этом случае данные будут выбраны из индекса без обращения к таблице.
Уникальные индексы
Уникальные индексы гарантируют, что значения колонок, входящих в него, будут уникальны. В этом смысле они похожи на уникальные ключи и первичные ключи. Отличие в том, что индекс - это объект в базе данных, задача которого - обеспечить быстрый поиск данных. Ключи же являются отражением бизнес-требований к приложению. Мы создаем уникальный ключ не для ускорения поиска, а для того, чтобы обеспечить уникальность данных, потому что этого требует задача.
Ключи могут использовать индексы для реализации своих задач, например, первичный ключ может создать (или использовать уже имеющийся) индекс.
Когда нужно создавать индекс
Однозначно ответить на этот вопрос нельзя, т.к. наличие индекса не означает, что он будет использоваться. Решение о том, использовать индекс или нет, принимает оптимизатор Oracle.
Описание некоторых методов доступа к данным (access path), соединения наборов данных (join method) и преобразований запросов (query transformation) используемых оптимизатором / в планах выполнения запросов Oracle
Методы доступа и соединения наборов данных
Index Full Scan
Используется для эффективной замены табличного доступа при выполнении сортировок (ORDER BY), группировок (GROUP BY) и операции sort merge join (вместо традиционных full table scan с последующей сортировкой) при выполнении определённых условий
Index Fast Full Scan
INDEX SKIP SCAN
Практический пример использования оптимизатором index skip scan при наличии составного индекса IX_AA_AFLID_DATE_INS по столбцам AA(AFFILIATE_ID, DATE_INSERT)
FILTER
Фильтрация по условию набора строк (row set), полученного в рез-тате предыдущей операции доступа к данным. Отражается в секции predicate info:
, или отдельной операцией плана выполнения, например, в случаях:
filter(NULL IS NOT NULL)
специальный случай операции FILTER, используемый для исключения из универсального плана выполнения избыточных операций с данными при наличии невыполнимых условий
Например, если для ненулевого по определению поля EMP.EMPNO указать в в запросе невыполнимое условие empno is null:
При этом в трейсе оптимизатора финальное уточнение стоимости выглядит след.образом:
CONCATENATION
Операция выбора путей доступа к данным / объединения результатов.
В первом случае применяется в случаях, когда план выполнения запроса выбирается динамически, например, в зависимости от значения связанной переменной:
8 = 7+1
В секции Predicate Information можно видеть малодокументированную функцию SYS_OP_MAP_NONNULL, используемую внутри DECODE для успешного сравнения (с результатом TRUE) нулевых значений
функция эта встречается, например, также в описании старого бага Oracle < 10.2 Bug 3899748 Incorrect join cardinality for lossless NVL predicates:
В другом случае в результате применения OR-expansion transformation операция CONCATENATION используется в плане для обозначения объединения результатов вместо UNION ALL:
CARTESIAN JOIN
Начиная с Oracle 10g использование картезианского произведения на уровне системы, сессии, запроса может быть запрещено параметрами с разной степенью эффективности:
HASH JOIN
Управление использованием hash join с помощью oracle events на уровне сессии/системы:
или скрытым, начиная с 10g, параметром:
HASH UNIQUE / SORT UNIQUE
- _gby_hash_aggregation_enabled = TRUE (значение по умолчанию нового параметра Oracle 10g). Значение FALSE отключает использование HASH UNIQUE
- optimizer_features_enable <= 9.2.0 отключает HASH UNIQUE
HASH GROUP BY / SORT GROUP BY
Для отключения использования операции HASH GROUP BY также, как для отключения Hash unique могут быть использованы параметры:
Native Full Outer Join
с почти удвоенными количеством consistent gets и, соответственно. стоимостью запроса по сравнению с Native Full Outer Join:
Управляющие подсказки и параметры:
NESTED LOOP JOIN
Применение NLJ Batching регулируется подсказкой /*+ NO_NLJ_BATCHING(inner_table) */, отключающей механизм буферизации IO и возвращающей план выполнения к стандартному для Oracle 11g виду:
, того же рез-то можно добиться изменением параметра _nlj_batching_enabledна уровне сессии | системы:
Подсказки, стимулирующие использование Nested Loops Join:
Semi-join / Anti-join
HASH JOIN ANTI SNA / HASH JOIN ANTI NA
Sort Merge Join
Sort join: Оба источника входных данных / таблицы сортируются по ключу соединения (join key)
Merge join: Совместная обработка / объдинение (merging) отсортированных списков
В случае с отсортироваными значениями одна из операций SORT JOIN исключается за ненадобностью:
SORT UNIQUE NOSORT
операция последовательной сортировки результатов одноблочного чтения для получения набора неповторяющихся значений, применяемая при выполнении SELECT DISTINCT
LOAD AS SELECT
Julian Dyke отмечает, что в ранних версиях операция наблюдалась только при выполнении EXPLAIN PLAN, и не наблюдается при реальном выполнении (AUTOTRACE или V$SQL_PLAN), начиная с Oracle 10.2 операцию можно наблюдать в выполняемых планах оптимизатора
INDEX MAINTENANCE
PX COORDINATOR FORCED SERIAL
Трейс оптимизатора при этом рапортует:
В случаях, когда Oracle не в состоянии самостоятельно определить безопасность PL/SQL кода для параллельного выполнения, рекомендуются следующие способы пометить функции, как пригодные для параллельного выполнения: How To Enable Parallel Query For A Function? [ID 1093773.1]:
2) допустимый / устаревающий метод, пригодный только для определения функции в составе пакета:
TEMP TABLE TRANSFORMATION
Преобразование промежуточного набора данных во временную таблицу во время выполнения запроса, может использоваться:
- при материализации подзапросов в запросах с применением subquery factoring
- в запросах, при выполнении которых используется star transformation
В случаях subquery factoring (WITH clause) временная таблица формируется при использовании подсказки /*+ MATERIALIZE*/ или без подсказки при выполнении определённых условий (например, количество использований таблицы-подзапроса в запросе):
Временная таблица SYS_TEMP_% строится на следующих шагах плана:
И затем, на шагах 6 и 8 сканируется только подготовленная временная таблица SYS_TEMP_% (исключая таким образом избыточный доступ к исходной таблице T)
MAT_VIEW CUBE ACCESS
Используется при выполнении query rewrite, доступа с версии 11.1
На примере тестовой схемы OLAPTRAIN без query rewrite запрос выполняется дорого с полным доступом к таблицам:
При использовании query rewrite данные получаются напрямую из куба CB$SALES_CUBE, значительно уменьшая стоимость и воемя выполнения:
Те же данные можно получить прямым запросом к кубу, например через mview с вышеуказанными в Predicate Information условиями:
PARTITION LIST EMPTY
Операция не предполагает выполнения последующих / дочерних операций ни с одной из партиций
Наблюдалась в 11.2.0.3 при ошибочном применении преобразования Table Expansion в виде:
доступ к партициям при этой операции определялся следующими противоречивыми предикатами по ключу партицирования:
Методы преобразования запросов (query transformation)
Join Elimination (JE)
При наличии ограничений целостности на столбцах, по которым выполняется соединение таблиц в запросе [например, PK->FK], трансформация типа join elimination может исключать из запроса таблицу в случае, когда сам факт соединения таблиц не влияет на результат запроса [т.е. правильное выполнение запроса не требует обращения к исключаемой таблице]
First K Rows Optimization
Применяется в процессе построения плана запроса, использующего функцию ROWNUM в условиях (rownum predicate)
Версии Oracle 10.1+
Star Transformation (ST)
преобразование запроса с целью исключения обработки большого количества строк таблиц с фактическими данными (fact table) за счёт комбинированного индексного доступа по подготовленному набору внешних ключей, полученных из сканирования/фильтрации небольших справочных таблиц (dimension table)
Set to Join Conversion (SJC)
Filter Push-Down (FPD)
Понимание индекса Oracle (1): INDEX SKIP SCAN и INDEX RANGE SCAN
㈠ Index SKIP SCAN
когда Таблица имеет составной индекс, и в запросе в качестве условий содержатся другие столбцы, отличные от первого столбца в индексе, а режим оптимизатора - CBO. В настоящее время план запроса может использовать SS.
Пропуск сканирования обнаружит количество уникальных значений в главном столбце индекса, и каждое уникальное значение будет использоваться в качестве входного элемента регулярного сканирования, на основании чего выполняется поиск, и эти запросы в конечном итоге объединяются.
Например: таблица сотрудников (sex, employee_id, address) имеет составной индекс (sex, employee_id).
В случае скачков индекса мы можем логически представить их как два индекса:
Один из них (мужчина, сотрудник_ид) и один (женщина, сотрудник_ид).
select * from employees where employee_id=1;
После выдачи этого запроса оракул сначала вводит пол в качестве мужчины и ищет запись employee_id = 1. Затем введите запись секса как женщина и найдите запись employee_id = 1. Наконец, объедините два набора результатов
Официальный представитель ORACLE сказал, что пропуск индекса можно использовать только тогда, когда в ведущем столбце меньше уникальных значений. Это на самом деле легко понять, то есть меньше входов, что также является условием пропуска сканирования
ORACLE также признает, что сканирование с пропуском не так быстро, как прямые запросы индекса, но можно сказать, что сканирование с пропуском индекса намного быстрее, чем сканирование таблицы.
До Oracle9i этот индекс можно было использовать, когда запрос SQL содержит пол и employee_id или когда в запросе указывается пол. Следующие запросы не могут использовать индекс:
select employee_id from employees where employee_id=7788;
Правило выполнения пропуска сканирования индекса Oracle9i позволяет использовать индексы соединения, даже если в запросе SQL не указан пол
Эта функция устраняет необходимость предоставлять второй индекс в строке employee_id
Сканирование индекса пропуска подходит для ситуаций, когда места на жестком диске и дискового пространства достаточно мало
Поскольку один индекс может удовлетворить использование двух условий запроса, более естественно сэкономить место, чем строить два индекса по отдельности.
Сначала посмотрите на картинку:
Предположим, я хочу найти запись с employee_id, равным 109. Как видно из рисунка, запись 109 существует в блоках 3 и 5.
Но как пропустить сканирование, чтобы найти эти два блока?
ORACLE может выбрать соответствующую запись в SKIP SCAN и использовать информацию корневого узла и узла ветвления для определения местоположения записанных конечных блоков, а именно блоков 3 и 5.
Если вы хотите найти запись с employee_id, равным 109, после того, как Oracle введет запись M, она может напрямую перейти к блоку 3. Без сканирования блока 1 и блока 2.
После ввода входа F вы можете напрямую перейти к блоку 5, не сканируя блок 4 и блок 6.
Итак, после вышеприведенного запроса, после пропуска сканирования, внутреннее может быть:
select empno from t where sex='M' and empno=8
union
select empno from t where sex='F' and empno=8;
Мы можем себе представить, что если в ведущем столбце индекса будет много уникальных значений, производительность пропускающего сканирования будет значительно снижена, поскольку может быть много объединений.
Иногда, чтобы избежать сканирования с пропуском индекса, необходимо создать новый индекс
㈡ INDEX Range Scan
INDEX Range SCAN - очень распространенный метод доступа к таблице
В INDEX Range SCAN Oracle обращается к смежным элементам индекса, а затем извлекает записи таблицы в соответствии с rowid в индексе.
Например: запросить всех сотрудников в отделе 80
INDEX RANGE SCAN - сканирование диапазона. Например, от 1 до 100, разделенных на 5 диапазонов. Если вы хотите запросить 45, вам нужно искать в 3-м диапазоне. Это будет очень быстро.
Индекс уникального сканирования и индекс диапазона сканирования имеют одинаковый путь поиска в дереве B
Только индекс уникального сканирования индекса прекращает поиск после того, как находит блок, который должен содержать ключ индекса, который вы ищете, потому что ключ уникален
Сканирование диапазона индекса будет продолжать следовать за указателем, пока не будут выполнены условия
И сканирование диапазона индекса - это просто запрос к индексу, он не имеет никакого отношения к тому, сканировать ли таблицу.
Если выбранные столбцы находятся в индексе, вам не нужно сканировать таблицу
Если таблица сканируется, доступ к таблице должен осуществляться по rowid, как показано в приведенном выше примере
Доступ к таблицам с помощью сканирования диапазона индекса можно получить через Пересоздайте таблицу в порядке индекса Повысить эффективность
Есть две причины:
① Если вы читаете только часть данных, предположим, что 20%, если порядок данных таблицы не в порядке, вы можете фактически прочитать всю таблицу.
Если порядок таблиц соответствует индексу, необходимо прочитать только 20% блоков в таблице. Это простой случай
Complicated В сложных случаях, когда порядок не в порядке, к блоку можно обращаться неоднократно в разное время на протяжении всего запроса.
Когда вы захотите снова получить доступ к этому блоку, возможно, он был заменен или изменен, что обходится дороже.
И если порядок одинаков, доступ к одному и тому же блоку сконцентрирован в непрерывном и коротком периоде времени с несколькими переменными, и для одного и того же блока не будет нескольких IO.
Читайте также: