Правила отключения индексов

Статья
12/15/2008

При отключении индекса закрывается доступ пользователя к этому индексу, а также в случае отключения кластеризованных индексов доступ к данным базовой таблицы. В компоненте SQL Server 2005 Database Engine индекс может быть отключен как автоматически при обновлении SQL Server, так и вручную. Дополнительные сведения см. в разделе Отключение индексов.

Можно отключать индексы любого типа. При отключении индексов действуют следующие правила:

Если индекс уникален, отключаются все ограничения PRIMARY KEY, UNIQUE и FOREIGN KEY, ссылающиеся на индексированные столбцы из других таблиц. Для отключения индексов пользователь должен обладать разрешением ALTER на эти таблицы. В противном случае инструкция ALTER INDEX DISABLE завершается сбоем. Если индекс является кластеризованным, отключаются все входящие и исходящие ограничения FOREIGN KEY для базовой таблицы.
При отключении индекса в предупреждающем сообщении приводятся имена ограничений. После перестройки индекса необходимо вручную включить ограничения с помощью инструкции ALTER TABLE CHECK CONSTRAINT.
При отключении индекса он не обслуживается.
Оптимизатор запросов не учитывает индексы при создании плана выполнения запроса. Запросы, ссылающиеся на отключенные индексы с помощью табличной подсказки, также завершаются сбоем.
Невозможно создать индекс с именем, совпадающим с уже существующим отключенным индексом, так как сведения о нем содержатся в метаданных.
Отключенный индекс может быть удален.

Отключение некластеризованных индексов

При отключении некластеризованного индекса данные индекса физически удаляются. Однако определение индекса остается в метаданных. При отключении некластеризованных индексов применяются следующие дополнительные правила:

Статистика индекса остается в своем прежнем расположении и автоматически обновляется в случае необходимости.
Некластеризованные индексы автоматически отключаются при отключении связанных с ними кластеризованных индексов. Они не могут быть включены до тех пор, пока кластеризованные индексы таблицы или представления не включены, либо не удалены. Некластеризованные индексы должны быть явно включены в том случае, если они не были включены ранее с помощью инструкции ALTER TABLE CHECK CONSTRAINT. Дополнительные сведения см. в разделе Правила включения индексов и ограничений.

Отключение кластеризованных индексов

При отключении кластеризованных индексов применяются следующие дополнительные правила:

Доступ к строкам данных отключенного кластеризованного индекса не может быть осуществлен, за исключением удаления или перестройки кластеризованного индекса. Это подразумевает следующее:
- Неприменимы следующие операции: SELECT, UPDATE, DELETE, INSERT, CREATE INDEX, CREATE STATISTICS, UPDATE STATISTICS (по отношению к индексу) и инструкции ALTER TABLE, изменяющие столбцы и ограничения таблицы.
- Могут быть использованы следующие операции: CREATE VIEW, DROP VIEW, CREATE TRIGGER, DROP TRIGGER, DROP INDEX, ALTER TABLE ENABLE/DISABLE TRIGGER, TRUNCATE TABLE и DROP TABLE.
- Некластеризованные индексы не могут быть созданы, если отключены связанные с ними кластеризованные индексы.
Существующие некластеризованные и XML индексы, связанные с таблицей, автоматически отключаются, и доступ к ним осуществляться не может.
Все кластеризованные и некластеризованные индексы представлений, ссылающихся на таблицу, отключаются. Указанные индексы должны быть перестроены, так же как и индексы ссылочной таблицы.

Отключение ограничений

Приведенные ниже дополнительные правила применяются при отключении ограничений PRIMARY KEY, FOREIGN KEY и UNIQUE:

Ограничения PRIMARY KEY и UNIQUE отключаются при отключении соответствующего индекса с помощью инструкции ALTER INDEX DISABLE.
При отключении ограничения PRIMARY KEY также отключаются все связанные с ним ограничения FOREIGN KEY. Это равносильно заданию для ограничения параметра NOCHECK CONSTRAINT.
Необходимо обладать разрешениями ALTER или CONTROL на эталонную таблицу.
Если по ссылке внешнего ключа объявлено действие CASCADE UPDATE или DELETE, и данная ссылка отключена, то все инструкции по обновлению или удалению, затрагивающие ограничения эталонной таблицы, будут заканчиваться ошибкой.
При отключенном индексе PRIMARY KEY или UNIQUE, а также при отключении индекса в ходе обновления SQL Server в таблицу случайно могут быть занесены значения-дубликаты. Прежде чем включить индекс, необходимо вручную исправить повторяющиеся строки. Возможны следующие варианты действий:
- Удалить или изменить повторяющиеся значения вручную.
- Если индекс UNIQUE не был создан в процессе создания ограничения UNIQUE, то для воссоздания индекса без указания параметра UNIQUE можно использовать инструкцию CREATE INDEX WITH DROP_EXISTING.
- Если индекс был создан в результате создания ограничения PRIMARY KEY или UNIQUE, это ограничение нужно удалить. Затем удаляется индекс. При удалении ограничения PRIMARY KEY удаляются все связанные с ним ограничения FOREIGN KEY.
Отключенные ограничения FOREIGN KEY и CHECK помечаются как is_not_trusted. Эти пометки можно просмотреть в представлениях каталога sys.check_constraints и sys.foreign_keys. При этом ограничение больше не проверяется системой для всех строк таблицы. Даже если заново включить ограничение, оно не будет повторно проверять уже существующие строки таблицы, если в инструкции ALTER TABLE не указать параметр WITH CHECK. При указании параметра WITH CHECK ограничение снова помечается как trusted.
В следующем примере отключается ограничение на зарплату. Параметр NOCHECK CONSTRAINT используется в инструкции ALTER TABLE для отключения ограничения и осуществления вставки, противоречащей указанному ограничению. Параметр WITH CHECK CHECK CONSTRAINT заново включает ограничение, а также осуществляет сверку существующих данных с восстановленным ограничением.
```
CREATE TABLE cnst_example 
(id INT NOT NULL,
    name VARCHAR(10) NOT NULL,
    salary MONEY NOT NULL
    CONSTRAINT salary_cap CHECK (salary < 100000);
)

-- Disable the constraint.
ALTER TABLE cnst_example NOCHECK CONSTRAINT salary_cap;

-- Reenable the constraint.
ALTER TABLE cnst_example WITH CHECK CHECK CONSTRAINT salary_cap;
```

Отключение индексов в представлениях

При отключении кластеризованных индексов в представлении данные индекса физически удаляются. При отключении индексов в представлениях применяются следующие дополнительные правила:

При отключении кластеризованного индекса в представлении вносить изменения в базовую таблицу не запрещается.
При отключении кластеризованных индексов в представлении некластеризованные индексы в нем отключаются автоматически.
Строки данных, связанные с кластеризованными и некластеризованными индексами, удаляются. Однако представление и определения индексов сохраняются в метаданных и могут быть восстановлены при перестройке индексов.
Инструкция ALTER INDEX ALL REBUILD перестраивает и включает все отключенные индексы таблицы, за исключением индексов представления. Для включения индексов представлений используется отдельная инструкция ALTER INDEX ALL REBUILD.
При перестройке кластеризованных индексов в представлении не происходит автоматического включения некластеризованных индексов.
Некластеризованные индексы включаются вручную при их перестройке, проводящейся после перестройки кластеризованных индексов.

Выполнение операций над отключенными индексами в оперативном режиме

Перестройка отключенных некластеризованных индексов в оперативном режиме возможна, если таблица не содержит отключенные кластеризованные индексы. Однако при использовании инструкций ALTER INDEX REBUILD и CREATE INDEX WITH DROP_EXISTING отключенные кластеризованные индексы могут быть перестроены только в автономном режиме. Дополнительные сведения об операциях над индексами в оперативном режиме см. в разделе Оперативное выполнение операций над индексом.

Статистика отключенных индексов

При работе со статистиками отключенных индексов применяются следующие дополнительные правила:

Инструкция CREATE STATISTICS не может применяться к таблицам, содержащим отключенные кластеризованные индексы.
Параметр базы данных AUTO_CREATE_STATISTICS создает новые статистики для столбцов с отключенными индексами при выполнении следующих условий:
- Параметр AUTO_CREATE_STATISTICS установлен в значение ON
- Для данного столбца не существует статистик.
- Статистики необходимы при оптимизации запросов.
Если в таблице содержатся отключенные кластеризованные индексы, процедура sp_autostats выдает ошибку.
Процедура sp_updatestats не может быть применена для обновления статистик отключенных кластеризованных индексов.
Процедура sp_createstats используется для создания статистик столбцов, которые могут быть начальными столбцами отключенного индекса. Если указан параметр indexonly, то статистики столбцов отключенных индексов не создаются до тех пор, пока указанные столбцы не будут задействованы другим включенным индексом.

Команды DBCC

Если кластеризованные индексы отключены, то сведения о базовой таблице не могут быть выданы с помощью инструкции DBCC CHECKDB. Вместо этого результатом выполнения этой инструкции будет сообщение об отключенном кластеризованном индексе. Инструкция DBCC INDEXDEFRAG не может быть использована для дефрагментации отключенного индекса. Данная инструкция возвращает сообщение об ошибке. Инструкция DBCC DBREINDEX может быть использована для перестройки отключенного индекса.

Просмотр состояния отключенного индекса

При отключении индекса или ограничений PRIMARY KEY или UNIQUE выдается предупредительное сообщение, в котором перечисляются все затронутые индексы и ограничения FOREIGN KEY и CHECK. Состояние отключенного индекса также можно просмотреть в представлении каталога sys.indexes или с помощью функции INDEXPROPERTY. Состояния отключенных ограничений FOREIGN KEY и CHECK можно просмотреть в представлениях каталога sys.foreign_keys и sys.check_constraints соответственно. Дополнительные сведения см. в разделе Просмотр сведений об индексах.

Примеры

В следующем примере отключается некластеризованный индекс в таблице Employee.

USE AdventureWorks;
GO
ALTER INDEX IX_Employee_ManagerID 
ON HumanResources.Employee DISABLE;

См. также

Поделиться через