Индекс пропустить сканирование оракула, что это такое

Обновлено: 21.11.2024

Сканирование с пропуском индекса происходит, когда начальный столбец составного индекса «пропущен» или не указан в запросе. По сути, сканирование с пропуском позволяет логически разделить составной индекс на более мелкие субиндексы, что делает каждый субиндекс жизнеспособным кандидатом для использования оптимизатором для разработки эффективного плана. Количество логических субиндексов определяется количеством различных значений в начальном столбце. Пропускать сканирование выгодно, если в начальном столбце составного индекса мало различных значений, а в неначальном ключе индекса много различных значений.

Часто пропуск сканирования блоков индекса выполняется быстрее, чем сканирование блоков таблицы, и быстрее, чем полное сканирование индекса.

Ситуации, в которых оптимизатор может пропустить сканирование, приведены ниже: -

В предикате запроса не указан ведущий столбец составного индекса.
В начальном столбце составного индекса существует несколько различных значений, но много различных значений существует в неведущем ключе индекса.

Вот так:

Полное сканирование индекса

Индексировать уникальные сканы

enq: TX — конфликт индекса — событие ожидания Oracle

2 мысли о «Сканировании с пропуском индекса»

Я имел дело с несколькими запросами в производственной среде. Его стоимость и время выполнения очень высоки. Однако я понял тот факт, что сканирование с пропуском не помогает в этих запросах, на самом деле является основной проблемой из-за плохой избирательности выбранных столбцов. Вот мои выводы:

Хеш-значение плана: 1440992606

| 0 | ВЫБЕРИТЕ ЗАЯВЛЕНИЕ | | 256К| 52М| | 12М (1)| 08:31:47 |
|* 1 | ХЭШ-СОЕДИНЕНИЕ | | 256К| 52М| 402М| 12М (1)| 08:31:47 |
|* 2 | HASH JOIN RIGHT SEMI | | 4023К| 356М| | 253К (4)| 00:10:48 |
|* 3 | ДОСТУП К СТОЛУ ПОЛНЫЙ | БДЦ | 17 | 272 | | 12 (9)| 00:00:01 |
|* 4 | ДОСТУП К СТОЛУ ПОЛНЫЙ | ТМС | 5120К| 376М| | 253К (4)| 00:10:47 |
|* 5 | ДОСТУП К ТАБЛИЦАМ ПО ИНДЕКСУ ROWID| цена | 327К| 38М| | 11М (1)| 08:20:04 |

|* 6 | ИНДЕКС ПРОПУСТИТЬ СКАНИРОВАНИЕ | PRQ_IDX10 | 73М| | | 398К (2)| 00:16:58 |

Хеш-значение плана: 27315579

| 0 | ВЫБЕРИТЕ ЗАЯВЛЕНИЕ | | 256К| 52М| | 1579К (5)| 01:07:12 |
|* 1 | ХЭШ-СОЕДИНЕНИЕ | | 256К| 52М| 41М| 1579К (5)| 01:07:12 |
|* 2 | ДОСТУП К СТОЛУ ПОЛНЫЙ | цена | 327К| 38М| | 1303К (5)| 00:55:28 |
|* 3 | HASH JOIN ПРАВИЛЬНОЕ ПОЛУ| | 4023К| 356М| | 253К (4)| 00:10:48 |
|* 4 | ДОСТУП К СТОЛУ ПОЛНЫЙ | БДЦ | 17 | 272 | | 12 (9)| 00:00:01 |

|* 5 | ДОСТУП К СТОЛУ ПОЛНЫЙ | ТМС | 5120К| 376М| | 253К (4)| 00:10:47 |

В моем случае не выполняется сканирование с пропуском индекса DB 11G
В чем может быть причина?

объясните план для
выберите * из сотрудников
где first_name='Raphaely' ;

В Oracle 9i Cost-Based Optimizer (CBO) оснащен множеством полезных функций, одной из которых является «сканирование с пропуском индекса». В предыдущих выпусках составной индекс можно было использовать только в том случае, если первый столбец, передний край индекса, упоминался в предложении WHERE инструкции. В Oracle 9i это ограничение снято, поскольку оптимизатор может выполнять сканирование с пропуском для получения идентификаторов строк для значений, не использующих префикс. Это означает, что даже если у вас есть составной индекс для более чем одного столбца и вы используете один столбец без префикса в своем SQL, он все равно может использовать индекс. (Раньше я думал, что он не будет использовать индекс :)) Не всегда гарантируется, что сканирование с пропуском индекса будет использоваться, это связано с тем, что оптимизатор на основе затрат (CBO) рассчитает стоимость использования индекса, и если это больше, чем при полном сканировании таблицы, то он может не использовать index. Этот подход выгоден, потому что:

Это уменьшает количество индексов, необходимых для поддержки ряда запросов. Это повышает производительность за счет сокращения обслуживания индекса и уменьшения неиспользуемого пространства, связанного с несколькими индексами.
Столбец префикса должен быть наиболее разборчивым и широко использоваться в запросах. Эти два условия не всегда идут рука об руку, что затрудняет принятие решения.В таких случаях пропуск сканирования снижает вероятность принятия «неправильного» решения.

Сканирование с пропуском индекса работает иначе, чем обычное сканирование индекса (диапазона). Обычный диапазон сканирования сначала работает сверху вниз, а затем перемещается по горизонтали. Но Skip scan включает в себя несколько сканирований диапазона. Поскольку в запросе отсутствует начальный столбец, он будет переписывать запрос на более мелкие запросы, и каждый из них будет выполнять сканирование диапазона. Рассмотрим следующий пример, где мы создаем тестовую таблицу и создаем индекс для первых двух столбцов a и b . Также мы помещаем некоторые фиктивные данные в тестовую таблицу. Посмотрите, как выбирается индекс, когда мы выполняем оператор select со столбцом b в предложении where.

До Oracle 9i было невозможно выполнить сканирование индекса (диапазона или уникального) для объединенного индекса, если только предложение WHERE не включало предикат для ведущего столбца (столбцов) индекса. Сканирование с пропуском индекса позволяет Oracle использовать индекс, если первый столбец не указан в предложении WHERE.

Рассмотрите индекс:

Скажем, для deptno существует только 3 разных значения: (1, 2 и 3). Приведенный выше запрос эквивалентен следующему запросу, который технически может использовать индекс без сканирования с пропуском:

Пропуск сканирования не требует повторного написания запроса; он даже не выполняет внутреннюю перезапись, как некоторые другие функции. Он просто просматривает поддеревья индекса для каждого из возможных значений ведущего столбца.

Подумайте, что произойдет, если у параметра deptno будет 5000 различных значений. Вместо 3 сканирований поддерева запрос выполнит 5000 из них. Ясно, что это было бы гораздо менее эффективно; в этом случае было бы намного лучше создать индекс с заданием в качестве ведущего столбца. Если статистика собрана правильно, оптимизатор на основе затрат будет знать кардинальность начального столбца и сможет самостоятельно решить, подходит ли сканирование с пропуском индекса.

Если у вас есть запрос, который может сканировать второй и последующие столбцы индекса, но не использует сканирование с пропуском индекса:

<УЛ>

Убедитесь, что вы используете Oracle 9i или более позднюю версию. Сканирование с пропуском индекса недоступно в v8i и более ранних версиях.

Попробуйте добавить в SQL подсказку INDEX_SS. Это даст оптимизатору указание использовать сканирование с пропуском индекса.

Выполните запрос через план объяснения и проверьте наличие строки, включающей ИНДЕКС ПРОПУСК СКАНИРОВАНИЯ .

Все еще не используете сканирование с пропуском?

<УЛ>

У вас должно быть предложение WHERE во втором столбце индекса. Skip Scan позволяет пропустить только первый столбец, а не два или более.

Пропустить сканирование не будет пропускать второй или последующий столбец индекса. например. Если у вас есть предложения WHERE в первом и третьем столбцах, в лучшем случае вы получите сканирование диапазона в первом столбце.

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

Он начал использовать пропуск сканирования, когда вы добавили подсказку? Если это так, Oracle считает, что Skip Scan будет неэффективным; поэтому без намека не употребляется. Сравните запрос с функцией Skip Scan и без нее, чтобы убедиться, что у вас лучший план.

Сканирование с пропуском индекса: потенциальный вариант использования или нет? (Shine On You Crazy Diamond) 30 января 2018 г.

Отвечая на недавний вопрос на форуме LinkedIn, я задумался о том, есть ли потенциальный вариант использования INDEX SKIP SCAN, который я раньше не рассматривал.

Я уже обсуждал сканирование с пропуском индекса ранее (как и здесь) — функцию, представленную в Oracle9i, которая позволяет CBO рассматривать индекс, даже если начальный столбец индекса не включен в предикат запроса. Однако CBO будет использовать индекс только в том случае, если в отсутствующем начальном столбце относительно мало различных значений, поскольку Oracle должен эффективно сканировать индекс несколько раз для каждого потенциального значения ведущего столбца. Если имеется слишком много различных значений, каждое сканирование может не привести к «пропуску» достаточного количества листовых блоков индекса, что сделает путь доступа INDEX SKIP SCAN слишком неэффективным.

Но мне пришло в голову, что стратегия использования сканирования с пропуском индекса также потенциально может быть применена при выполнении агрегатной функции на основе сортировки. А может и нет.

Итак, небольшой тест, чтобы выяснить это.

Я начинаю с создания таблицы с 1 миллионом строк. Ключевым моментом здесь является то, что столбец CODE имеет только 3 различных значения, поэтому любое агрегирование на основе CODE будет возвращать только 3 или меньше строк.

Теперь я создаю индекс на основе столбцов CODE и SALES, при этом CODE является ведущим столбцом:

Поскольку CODE имеет очень мало различных значений, индекс является кандидатом для INDEX SKIP SCAN, если CODE не указан в предикате. Например:

Таким образом, INDEX SKIP SCAN действительно использовался CBO, и всего 31 непротиворечивый результат — неплохой результат при выборке 100 интересующих строк. Имея такое небольшое количество различных значений CODE, Oracle нужно выполнить лишь относительно небольшое количество сканирований индекса, чтобы получить все возможные интересующие SALES по каждому из (3) значений CODE. CBO подсчитал, что при стоимости всего 5 здесь требуется доступ к относительно небольшому количеству листовых блоков индекса.

Здесь также стоит упомянуть, что Oracle также может очень эффективно использовать индекс, чтобы возвращать только столбец MIN (или, возможно, MAX) SALES для каждого интересующего значения CODE, поскольку ему достаточно прочитать первую запись индекса CODE для впоследствии определите соответствующее минимальное значение ПРОДАЖ:

Всего за 3 непротиворечивых получения Oracle достаточно прочитать первую запись индекса CODE=1, чтобы немедленно определить минимальное связанное значение SALES.

Теоретически Oracle может использовать те же стратегии при обработке агрегированного запроса GROUP BY, чтобы очень быстро и эффективно определить минимальное значение SALES для каждого отдельного CODE в моих данных. Oracle знает, что существует только 3 (несколько) различных ключевых значений, и с помощью комбинации использования индекса для быстрого доступа к минимальным (первым) ПРОДАЖАМ каждого значения КОДА и ПРОПУСКА ИНДЕКСНОГО СКАНИРОВАНИЯ для быстрого повторного сканирования индекса для перехода к следующему. CODE, индекс можно использовать для очень быстрого и эффективного поиска и извлечения необходимого набора данных в следующем запросе:

Однако при относительно дорогом последовательном получении 2477 используется только INDEX FAST FULL SCAN. Обратите внимание, что Oracle использует HASH GROUP BY для своей агрегации по умолчанию (а не какую-либо форму агрегации типа sort/group). В результате индекс здесь не рассматривается, и обратите внимание, что окончательный результирующий набор НЕ находится в порядке КОД (данные возвращаются в порядке КОД 1,2,0).

Можно попытаться принудительно использовать INDEX SKIP SCAN с помощью подсказки:

Теперь в плане используется INDEX SKIP SCAN. Но это не «умно» в использовании INDEX SKIP SCAN с 2469 согласованными получает, предполагая, что Oracle неэффективно использует возможность сканирования (MIN / MAX) для доступа к отдельному CODE и без необходимости читает большинство блоков листа индекса. .

Тем не менее, Oracle теперь использует процесс сортировки/группировки при выполнении агрегирования (о чем свидетельствует второй шаг плана), в результате чего данные теперь возвращаются в порядке CODE.

Даже если мы исключим агрегирование из уравнения с помощью простого запроса на основе MIN, обращающегося к более чем одному значению CODE:

CBO по-прежнему не рассматривает тип обработки INDEX SKIP SCAN в этом сценарии и вместо этого использует относительно неэффективный INDEX RANGE SCAN.

Поэтому, к сожалению, хотя у меня есть существующий индекс, который при эффективном использовании потенциально может очень эффективно возвращать набор результатов GROUP BY, CBO не использует этот индекс. Судя по всему, CBO не может использовать INDEX SKIP SCAN в сочетании с несколькими MIN/MAX сканированиями в сценариях, когда это возможно.

Читайте также: