Техники Bitmap-индекса Oracle

И снова добрый вечер!

Запускаем второй поток нашего нового курса «Реляционные СУБД» ^[1], который мы чуть дотюнили по итогам первого прогона: дополнительные занятия по кластерам MySQL и Postgres, оказался востребованным docker и ещё разные «доработки напильником». Так что ждите открытые уроки (в которые вынесли часть старых тем) и интересные материалы. Сегодня мы покопаемся в техниках Oracle.

Поехали.

Bitmap-индексы Oracle сильно отличаются от стандартных индексов B-дерева. В bitmap-структурах создается двухмерный массив со столбцом для каждой строки в индексируемой таблице. Каждый столбец представляет отдельное значение в bitmap-индексе. Этот двухмерный массив показывает каждое значение индекса, умноженное на количество строк в этой таблице.

Oracle распаковывает bitmap (со скоростью извлечения строки) в буфер данных ОЗУ для быстрого сканирование на предмет совпадения значений. Эти совпадающие значения передаются Oracle в виде списка Row-ID, и значения Row-ID могут напрямую обращаться к необходимой информации.

Особое преимущество bitmap-индексирования проявляется, когда одна таблица включает несколько bitmap-индексов. Мощность каждого столбца может быть невысокой. Создание нескольких bitmap-индексов предоставляет очень сильный подход для быстрого ответа на сложные SQL-запросы.

Используя методологию bitmap-объединения, Oracle обеспечивает снижает время отклика до менее секунды при работе с несколькими столбцами с малым количеством элементов.

Также обратите внимание на важные заметки о максимальных значениях Oracle bitmap-индекса ^[2].

Например, представим, что есть база данных автомобилей с большим числом маломощных столбцов: car_color, car_make, car_model и car_year. Каждый столбец содержит менее 100 различных значений, и индекс b-дерева был бы совершенно бесполезен в такой базе данных 20 миллионов автомобилей.

Однако, слияние этих индексов в запрос может обеспечить высокое время отклика гораздо быстрее, чем традиционный метод чтения каждой из 20 миллионов строк в базовой таблице. Например, предположим мы хотим найти старые синие Toyota Corolla, произведенные в 1981 году:

select
   license_plat_nbr
from
   vehicle
where
   color = "blue"
and
   make = "toyota"
and
   year = 2015;

Для работы с этим запросом Oracle использует специализированный метод оптимизации под названием объединение bitmap-индексов. В этом методе каждый список Row-ID (кратко — RID) формируется отдельно при помощи bitmap’ов, а для сравнения RID-списков и поиска пересекающихся значений используется специальная процедура слияния.

По мере роста числа различных значений, размер bitmap увеличивается экспоненциально. Так индекс 100 значений может работать в 1000 раз быстрее, чем bitmap-индекс 1000 различных значений столбца.

Стоит помнить, что bitmap-индексы подходят только для статических таблиц и материализованных представлений, которые обновляются ночью и пересобираются после пакетной загрузки строк. Если в вашей таблице происходит несколько DML в секунду, БУДЬТЕ ОСТОРОЖНЫ при реализации bitmap-индексов!

• 1 — 7 различных ключевых значений — Запросы bitmap-индексам с низкой мощностью выполняются очень быстро;
• 8 — 100 различных ключевых значений — С увеличением числа различных значений, производительность пропорционально снижается;
• 100 — 10000 различных значений — При более чем 100 различных значениях bitmap-индексы становятся огромными и SQL-производительность стремительно падает;
• Более 10000 различных ключевых значений — на этом этапе производительность в десять раз ниже, чем при индексе с 100 различными значениями.

Bitmap-индексы Oracle — очень мощная фича Oracle, но есть и подводные камни!

Вы захотите использовать bitmap-индекс в следующих случаях:

1. Столбец таблицы обладает малой мощностью — для ЧЕРНОВОГО руководства, рассмотрите bitmap для любого индекса с менее чем 100 различными значениями:

   select region, count(*) from sales group by region;

2. НИЗКИЙ DML таблицы — использование вставки/обновления/удаления должно быть низком. На обновление bitmap-индексов требуется много ресурсов, поэтому они лучше подходят для таблиц, доступных только для чтения, и таблиц, пакетно обновляемых каждую ночь;
3. Несколько столбцов — Ваши SQL-запросы ссылаются на несколько маломощных значений в их where операторах. SQL-оптимизатор Oracle на базе стоимости (кратко — CBO) будут вопить при наличии у вас bitmap-индексов.

Устранение неполадок bitmap-индексов Oracle

Самые распространенных проблемы реализации bitmap-индексов включают следующие:

• Маленькая таблица — CBO может потребовать полного сканирования таблицы, если она слишком маленькая!
• Плохие статы — Убедитесь, что вы анализируете bitmap с dbms_stats сразу после создания:

CREATE BITMAP INDEX 
emp_bitmap_idx
ON index_demo (gender);

exec dbms_stats.gather_index_stats(OWNNAME=>'SCOTT', INDNAME=>'EMP_BITMAP_IDX');

• Тестирование с подсказкой — чтобы использовать ваш новый bitmap-индекс, используйте подсказку INDEX Oracle:

select /*+ index(emp emp_bitmap_idx) */ 
   count(*)
from 
   emp, dept
where 
   emp.deptno = dept.deptno;

Ждём вопросы и комментарии тут или заходите к нам на новый открытый урок ^[3].

Автор: MaxRokatansky

Источник ^[4]