Oracle не использует индекс
Обновлено: 03.07.2024
Я столкнулся с этим вопросом в интервью и понятия не имел, как ответить:
Существует таблица с индексом по столбцу, и вы запрашиваете:
Запрос занимает слишком много времени, и вы обнаружите, что индекс не используется. Если вы думаете, что при использовании индекса производительность запроса будет лучше, как вы можете заставить запрос использовать индекс?
Вы можете использовать подсказки оптимизатора
select /*+ INDEX(table_name index_name) */ from table и т. Д. , ,
Может быть много причин, по которым Индекс не используется. Даже после того, как вы укажете подсказки , есть шансы, что оптимизатор Oracle сочтет иначе, и решит не использовать индекс . Вам нужно пройти через часть EXPLAIN PLAN и посмотреть, сколько стоит выписка с INDEX и без INDEX.
Предполагая, что Oracle использует CBO . Чаще всего, если оптимизатор считает, что стоимость с INDEX высока, даже если вы укажете ее в подсказках, оптимизатор будет игнорировать и продолжать полное сканирование таблицы. Ваше первое действие должно проверять DBA_INDEXES, чтобы знать, когда статистика LAST_ANALYZED. Если не проанализировано, вы можете установить таблицу, индекс для анализа .
В крайних случаях вы можете попробовать , настроив статистику самостоятельно.
Если вы думаете, что при использовании индекса производительность запроса будет лучше, как вы можете заставить запрос использовать индекс?
Сначала вы, конечно, убедитесь, что индекс дал лучший результат для возврата полного набора данных, верно?
Подсказка индекса - это ключевой момент, но более современный способ его указания - это метод именования столбцов, а не метод именования индексов. В вашем случае вы бы использовали:
В более сложных случаях вы могли бы. , ,
ответ дан David Aldridge, с репутацией 42781, 3.12.2009Существует соответствующий индекс для column_having_index, и его использование фактически увеличивает производительность, но Oracle не использует его. , ,
Вы должны собрать статистику в своей таблице, чтобы оптимизатор мог увидеть, что доступ к индексу может помочь. Использование прямой подсказки не является хорошей практикой.
У меня очень большая таблица в oracle 11g, которая имеет очень простой индекс в поле char (обычно это Y или N). Если я просто выполняю очередь, как показано ниже, для возврата требуется около 10 секунд
Однако, если я заставляю его использовать индекс, который я создаю, он занимает 80 мс
Также, если я буду работать под планом объяснения, как ниже:
Для моего плана я получил:
Для второго pla я получил:
Что доказывает, что если я не укажу явный оракул, чтобы использовать индекс, он его не использует, мой вопрос в том, почему оракул не использует этот индекс? Oracle, как правило, достаточно умна, чтобы принимать решения в 10 раз лучше меня, это первый раз, когда я на самом деле вынуждаю оракул использовать индекс, и мне это не очень удобно.
У кого-нибудь есть хорошее объяснение для решения оракула не использовать индекс в этом очень явном случае?
спросил(а) 2015-05-28T03:47:00+03:00 6 лет, 6 месяцев назадВ столбце QueueProcessed, вероятно, отсутствует гистограмма, поэтому Oracle не знает, что данные искажены.
Если Oracle не знает, что данные искажены, он будет считать предикат равенства QueueProcessed = 'N' , возвращает DBA_TABLES.NUM_ROWS/DBA_TAB_COLUMNS.NUM_DISTINCT. Оптимизатор считает, что запрос возвращает половину строк в таблице. На основе времени возврата 80 мс реальное число возвращаемых строк невелико.
Сканирование диапазона индекса обычно работает хорошо, когда они выбирают небольшой процент строк. Сканирование индекса индекса считывается из структуры данных по одному блоку за раз. И если данные распределены случайным образом, в любом случае может потребоваться прочитать каждый блок данных из таблицы. По этим причинам, если запрос обращается к большой части таблицы, более эффективно использовать многоблочный просмотр полной таблицы.
Оценка плохой мощности от перекошенных данных заставляет Oracle думать, что полное сканирование таблицы лучше. Проблема с созданием гистограммы.
Пример схемы
Создайте таблицу, залейте ее перекошенными данными и соберите статистику в первый раз.
Проблема с первым исполнением.
В этом случае параметры статистики по умолчанию не собирают гистограммы в первый раз. План показывает полное сканирование таблицы и оценки строк = 50000, ровно половину.
Создание гистограммы
Обычно обычно задаются параметры статистики по умолчанию. Гистограмма не может быть собрана по нескольким причинам. Они могут быть отключены вручную - проверьте задачи, задания или настройки, установленные администратором баз данных.
Кроме того, гистограммы собираются только автоматически на столбцах, которые перекошены и используются. Сбор гистограмм может занять некоторое время, нет необходимости создавать гистограмму в столбце, который никогда не используется в соответствующем предикате. Oracle отслеживает использование столбца и может извлечь выгоду из гистограммы, хотя данные теряются, если таблица отбрасывается.
Запуск выборочного запроса и повторной сбор статистики приведет к отображению гистограммы:
Теперь используются строки = 100 и индекс.
Создание гистограммы
Попытайтесь определить, почему гистограмма отсутствовала. Убедитесь, что статистика собрана с настройками по умолчанию, нет странных предпочтений в столбцах или таблицах, и эта таблица не будет постоянно удалена и перезагружена.
Если вы не можете полагаться на задание статистики по умолчанию для своего процесса, вы можете вручную собрать гистограммы с параметром method_opt следующим образом:
Я столкнулся с этим вопросом в интервью и понятия не имел, как ответить:
Есть таблица с индексом по столбцу, и вы запрашиваете:
Запрос занимает слишком много времени, и вы обнаруживаете, что индекс не используется. Если вы думаете, что при использовании индекса производительность запроса будет лучше, как вы можете заставить запрос использовать индекс?
Вы можете использовать подсказки оптимизатора
select /*+ INDEX(table_name index_name) */ from table и т.д .
Может быть много причин если индекс не используется. Даже после того, как вы укажите подсказки, есть шансы, что оптимизатор Oracle сочтет иначе и решит, что нет использовать индекс . Вам нужно пройти через часть EXPLAIN PLAN и посмотреть, сколько стоит выписка с INDEX и без INDEX.
Предполагая Oracle использует CBO . Чаще всего, если оптимизатор считает, что стоимость с INDEX высока, даже если вы укажете ее в подсказках, оптимизатор будет игнорировать и продолжать полное сканирование таблицы. Ваше первое действие должно проверять DBA_INDEXES, чтобы знать, когда статистика LAST_ANALYZED. Если не проанализировано, вы можете установить таблицу, индекс для анализа .
В крайних случаях вы можете попробовать настройка статистики по своему усмотрению.
Если вы думаете, что при использовании индекса производительность запроса будет лучше, как вы можете заставить запрос использовать индекс?
Сначала вы, конечно, убедитесь, что индекс дал лучший результат для возврата полного набора данных, верно?
Индексная подсказка является ключевым моментом здесь, но более современный способ его указания - это метод именования столбцов, а не метод именования индексов. В вашем случае вы бы использовали:
В более сложных случаях вы могли бы .
Существует соответствующий индекс для column_having_index, и его использование фактически увеличивает производительность, но Oracle не использует его .
Вам нужно собрать статистику в своей таблице, чтобы оптимизатор мог увидеть, что доступ к индексу может помочь. Использование прямой подсказки не является хорошей практикой.
У меня очень большая таблица в oracle 11g, которая имеет очень простой индекс в поле char (обычно это Y или N). Если я просто выполняю очередь, как показано ниже, для возврата требуется около 10 секунд
Однако, если я заставляю его использовать индекс, который я создаю, он занимает 80 мс
Также, если я буду работать под планом объяснения, как ниже:
Для моего плана я получил:
Для второго pla я получил:
Что доказывает, что если я не укажу явный оракул, чтобы использовать индекс, он его не использует, мой вопрос в том, почему оракул не использует этот индекс? Oracle, как правило, достаточно умна, чтобы принимать решения в 10 раз лучше меня, это первый раз, когда я на самом деле вынуждаю оракул использовать индекс, и мне это не очень удобно.
У кого-нибудь есть хорошее объяснение для решения оракула не использовать индекс в этом очень явном случае?
В столбце QueueProcessed, вероятно, отсутствует гистограмма, поэтому Oracle не знает, что данные искажены.
Если Oracle не знает, что данные искажены, он будет считать предикат равенства QueueProcessed = 'N' , возвращает DBA_TABLES.NUM_ROWS/DBA_TAB_COLUMNS.NUM_DISTINCT. Оптимизатор считает, что запрос возвращает половину строк в таблице. На основе времени возврата 80 мс реальное число возвращаемых строк невелико.
Сканирование диапазона индекса обычно работает хорошо, когда они выбирают небольшой процент строк. Сканирование индекса индекса считывается из структуры данных по одному блоку за раз. И если данные распределены случайным образом, в любом случае может потребоваться прочитать каждый блок данных из таблицы. По этим причинам, если запрос обращается к большой части таблицы, более эффективно использовать многоблочный просмотр полной таблицы.
Оценка плохой мощности от перекошенных данных заставляет Oracle думать, что полное сканирование таблицы лучше. Проблема с созданием гистограммы.
Пример схемы
Создайте таблицу, залейте ее перекошенными данными и соберите статистику в первый раз.
Проблема с первым исполнением.
В этом случае параметры статистики по умолчанию не собирают гистограммы в первый раз. План показывает полное сканирование таблицы и оценки строк = 50000, ровно половину.
Создание гистограммы
Обычно обычно задаются параметры статистики по умолчанию. Гистограмма не может быть собрана по нескольким причинам. Они могут быть отключены вручную - проверьте задачи, задания или настройки, установленные администратором баз данных.
Кроме того, гистограммы собираются только автоматически на столбцах, которые перекошены и используются. Сбор гистограмм может занять некоторое время, нет необходимости создавать гистограмму в столбце, который никогда не используется в соответствующем предикате. Oracle отслеживает использование столбца и может извлечь выгоду из гистограммы, хотя данные теряются, если таблица отбрасывается.
Запуск выборочного запроса и повторной сбор статистики приведет к отображению гистограммы:
Теперь используются строки = 100 и индекс.
Создание гистограммы
Попытайтесь определить, почему гистограмма отсутствовала. Убедитесь, что статистика собрана с настройками по умолчанию, нет странных предпочтений в столбцах или таблицах, и эта таблица не будет постоянно удалена и перезагружена.
Если вы не можете полагаться на задание статистики по умолчанию для своего процесса, вы можете вручную собрать гистограммы с параметром method_opt следующим образом:
Читайте также: