Создание партиционированного индекса

В высоконагруженных OLTP базах данных, буферное ожидание может создать реальную проблему производительности системы. Например, если идет вставка данных из многих сессий в таблицу, то может оказаться что они конкурируют за один и тот же листовой блок индекса и из-за этого появляются ожидания. Увидеть эти ожидания, можно с помощью запроса:

select owner
     , object_name
     , subobject_name
     , value
     , tablespace_name
  from v$segment_statistics
 where statistic_name='buffer busy waits'
   and value > 0
 order by value desc
/
OWNER     OBJECT_NAME SUBOBJECT_NAME VALUE TABLESPACE_NAME
STUDENT    IDX_TEST                                          467305    USERS

В приведенном примере, мы видим, что ожидание было 467305 для объекта IDX_TEST. Это индекс на таблице TAB_TEST. Если мы пересоздадим индекс с использованием 64 партиций:

create index idx_test on tab_test(id) global
     partition by hash(id) partitions 64
/

то запустив запрос:

select sum(value) sm
  from v$segment_statistics
 where statistic_name='buffer busy waits'
   and object_name = 'IDX_TEST'

мы увидем, что ожидания снизились до 3311, что меньше в 140 раз предыдущего результата.