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

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

Поехали.

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

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



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



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

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

Например, представим, что есть база данных автомобилей с большим числом маломощных столбцов: 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 = 1981;

Для работы с этим запросом 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;

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

Комментарии (7)


  1. asamoilov
    13.12.2018 23:01
    +1

    Ещё одной ценной особенностью таких индексов является то, что индексируются NULL.


    1. MaxRokatansky Автор
      14.12.2018 00:10

      А если нужно индексировать NULL в btree, смотрите сюда как ;)


  1. onix74
    14.12.2018 08:15

    произведенные в 1981 году:

    year = 2015;


    Toyota Corolla

    make = «toyota»

    Это особенность bitmap-индексов? :-)


    1. MaxRokatansky Автор
      14.12.2018 22:46

      Очепяточка в оригинале. Зевнули. Поправили :)


  1. gsl23
    14.12.2018 13:01

    НИЗКИЙ DML таблицы — использование вставки/обновления/удаления должно быть низком. На обновление bitmap-индексов требуется много ресурсов, поэтому они лучше подходят для таблиц, доступных только для чтения, и таблиц, пакетно обновляемых каждую ночь;

    вы уж договаривайте до конца. Требуется много ресурсов — это, мягко говоря, не точно сказано. При изменении строки, накладывается блокировка на изменение всех(!) строк таблицы, содержащихся в этом ключе битмап индекса. И никак с количеством ресурсов это связано.


    1. xtender
      15.12.2018 18:08

      накладывается блокировка на изменение всех(!) строк таблицы, содержащихся в этом ключе битмап индекса.
      Как я уже ниже советовал, почитайте Ричарда Фута!
      Вообще это один из известных мифов, и является полуправдой:
      1. с одной стороны, действительно многие записи с таким же значением будут заблокированы, но
      2. на самом деле, заблокированы будут только те записи, которые находятся в том же куске со списком ROWIDs, что и изменяемая запись, тк при большом кол-ве записей они не хранятся единым куском. (про реальное строение читайте у Р.Фута или просто проанализируйте дамп индекса)
      Проверяется очень легко:
      1. создайте таблицу с битмап индексом, в которой значение только одно:
      SQL> create table tbitmap as select level id, 1 a from dual connect by level<=1e6;
      
      Table created.
      
      SQL> create bitmap index ix_bitmap on tbitmap(a);
      
      Index created.
      

      2. Измените значение индексного поля на 2 в строке с id=1:
      SQL> update tbitmap set a=2 where id=1;
      
      1 row updated.

      3. В другой сессии вы можете спокойно изменить множество строк, кроме тех которые находятся в других блоках битмап карты:
      SQL> update tbitmap set a=3 where id=1e6;
      
      1 row updated.
      
      SQL> update tbitmap set a=3 where id=1e6-100;
      
      1 row updated.
      
      SQL> update tbitmap set a=3 where id=1e6-1000;
      
      1 row updated.
      
      SQL> update tbitmap set a=3 where id=1e6-10000;
      
      1 row updated.
      
      SQL> update tbitmap set a=3 where id=1e6-100000;
      
      1 row updated.
      
      SQL> update tbitmap set a=3 where id=1e6-500000;
      
      1 row updated.
      
      SQL> update tbitmap set a=3 where id=1e6-800000;
      
      1 row updated.
      
      SQL> update tbitmap set a=3 where id=1e6-900000;
      
      1 row updated.

      4. Но если вы попытаетесь проапдейтить строку из того же куска, то тут же нарветесь на блокировку:
      SQL> update tbitmap set a=3 where id=1024;
      -- и висим...
      


  1. xtender
    14.12.2018 19:21

    Ох, блин, парни… Ну это же чистой воды «бурлесоновщина»! Не поленитесь, почитайте Ричарда Фута и исправьте статью!
    Например: richardfoote.wordpress.com/2010/03/03/1196
    А еще лучше всего его посты про них:
    richardfoote.wordpress.com/category/bitmap-indexes