COALESCE или REBUILD . Что лучше?

dbstalker, 12 июля

Если вы уже решили, что индекс нужно перепаковать, то есть как минимум три варианта как это можно сделать:

alter index table1_index1 coalesce;
alter index table1_index1 rebuild;
alter index table1_iindex1 rebuild online;

Взвесим все ЗА и ПРОТИВ для каждой команды.

COALESCE

Аргументы ЗА: можно выполнять online, так как таблица не блокируется. Индекс упаковывается в пределах существующей индексной структуры - соединяет блоки листа в пределах имеющихся ветвей дерева. Быстро освобождает индексные листовые блоки для использования. Низкая стоимость. Не требует много дискового пространства.

Аргументы ПРОТИВ: генерируется много записей в журналах повторного выполнения (redo). Используется до 10g. Может вызвать ошибку ORA-01555 (Команда coalesce работает с листовыми блоками определенным количеством малых транзакций. А много малых транзакций, выполненных одной сессией, могут вызвать у другой сессии, выполняющей продолжительную транзакцию, эту ошибку). Не опускает HWM индекса, т.е. место на диске не освобождает. Не может переместить индекс в другое табличное пространство.

REBUILD

Аргументы ЗА: Быстро перемещает индекс в другое табличное пространство. Создает новое дерево, уменьшает его высоту, если это необходимо. Дает возможность быстро изменять storage и tablespace параметры без необходимости удалять индекс. Может быть использована для уменьшения расходования ресурсов - передвигается отметка HWM.

Аргументы ПРОТИВ: Более высокие издержки - требует больше дискового пространства: в течение перестройки необходимо достаточно пространства, чтобы разместить старый и новый индекс в соответствующем табличном пространстве . Может вызвать ошибку ORA-01410: Invalid ROWID (Старая копия индекса становится свободным пространством как только перестройка завершена. Если, например, процессы требующие свободного пространства в то время, когда текущее выполнение запроса еще использует старую копию индекса, то запрос может завершиться ошибкой ORA-10410)

  • Rebuild "offline"Аргументы ЗА: Может использовать существующий индекс для создания новой версии индекса. Аргументы ПРОТИВ: блокирует таблицу во время выполнения.
  • Rebuild online Аргументы ЗА: не блокирует таблицу во время непосредственной перестройки индекса. Индекс доступен практически все время перестройки, кроме времени переключения. Аргументы ПРОТИВ: блокирует таблицу в начале и в конце перестройки. Старый индекс не используется для перестройки индекса: со старым индексом работают пользователи. Все изменения тем временем вносятся в журнальную таблицу, затем уже будут перенесены в новый индекс. Может потребоваться большая сортировка.

Рассмотрим два гипотетических случая и определимся в каждом, что лучше применять Rebuild или COALESCE.

1 случай. Есть таблица, из которой удаляются исторические данные. Например, 10% листовых блоков, содержавших «старые» данные стали пустыми. Они никогда уже не будут заполнены, так как таблица пополняется «новыми» данными. 90% листовых блоков находятся в превосходном состоянии – идеально заполнены, не содержат «пропусков». Coalesce уплотнит 10% листовых блоков, из которых были удалены записи. Остальные 90% листовых блоков этой командой обрабатываться не будут. Rebuild поступит совсем по-другому. Эта команда перелопатит всю индексную структуру, все блоки, занятые индексом, и создаст новый идеальный индекс. Хотя в этом не было такой явной необходимости, при условии, что 90% индекса и так было идеально. Очевидно, что в данном случае оправдано выполнение команды Coalesce.

2 случай. Есть таблица из которой спонтанно удаляются записи размещенные по все структуре индекса. Данных удалено много и в дальнейшем таблица данными не будет заполняться. Coalesce в этом случае несколькими проходами будет объединять смежные листовые блоки. Этот процесс перемещения будет длиться до тех пор, пока будет возможность сращивать листовые блоки. Одна индексная запись может быть перемещена в ходе выполнения этой команды несколько раз. К тому же блоки ветвей не будут задействованы в реорганизации. Rebuild поступает по иному. Эта команда будет работать с существующей (без удаленных данных) индексной структурой, перестроит ее и создаст новый индекс. В данном случае расходование ресурсов командой Rebuild явно оправдано и результат весомее.

Вывод:

Операция Coalesce особенно эффективна, когда процент проблематичного пространства к общему индексному пространству невелик (20% листовых блоков) и фрагментирован индекс несущественно.

Rebuild особенно эффективна, когда процент проблематичного пространства к общему индексному пространству велик и средняя степень фрагментации в пределах индексного блока листа сравнительно высокая.

Давайте еще раз ответим себе на вопрос: Оправдает ли прирост производительности понесенные затраты на перестройку?

Когда вы пересоздаете индекс, физический эффект состоит в уменьшении размера индекса. Это теоретически может привести к трем возможным преимуществам:

  • Оптимизатор может использовать индекс в большем количестве планов выполнения, так как стоимость использования индекса мала.
  • Запросам придется посетить меньше блоков индекса
  • Индекс меньше, значит он может дольше оставаться в цепочке LRU буферного кеша, необходимые блоки индекса будут находиться в буфере чаще . А значит, уменьшается объем дорогих операций ввода-вывода.

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

Однако если есть некоторые промежутки времени, когда база данных не используется, то можно создать пакетное задание, которое не будет мешать другим задачам, и будет перестраивать проблемные индексы.

Приглашаем присоединиться к поднятому в этом посте вопросу. Поделитесь своими мыслями и наработками.

1 комментарий

Прокоментировать

lynx™
13 июля 2010 г. в 20:05

rebuild online в случае, если база загружена - может вообще никогда не закончиться

 

Новый комментарий

Я не спамер: введите суму 5+7



 

От авторов блога

О Блоге - прочитай перед началом.

Задать вопрос и получить ответ - уже решено 94 вопросов

Глоссарий - список терминов и сокращений


 
 

Бизнес форум

Последние темы:

Расписание автобусов
18 июля, 3 ответа
Отдых в августе
17 июля, 4 ответа