Что такое код alter index


Содержание

ALTER INDEX

Описание

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

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

Чтобы перекомпоновать и восстановить баланс индекса, отключите и включите индекс. Этот метод создает заново сбалансированный индекс.

Если индекс используется, ALTER INDEX не дает ни какого эффекта, пока индекс не освободится от использования.

Инструкция ALTER INDEX терпит неудачу и возвращает ошибку, если индекс определен, как UNIQUE, PRIMARY KEY или FOREIGN KEY ограничение. Для изменения таких индексов, используйте DROP INDEX, чтобы удалить индекс, и создать его заново с помощью CREATE INDEX.

Индекс может быть изменен его создателем и пользователем SYSDBA.

Обратите внимание: Чтобы добавить или удалить столбцы или ключи, используйте DROP INDEX, чтобы удалить индекс, и создать его заново с помощью CREATE INDEX.

Синтаксис

Аргумент Описание
name Имя существующего индекса.
ACTIVE Включает индекс — изменяет состояние INACTIVE индекса на ACTIVE.
INACTIVE Выключает индекс — изменяет состояние ACTIVE индекса на INACTIVE.

Примеры

Следующие инструкции отключают и включают индекс, чтобы его перекомпоновать:

Создаем индекс в таблице базы данных MS SQL Server

И ндексы — это ключ к повышению производительности вашей информационной системы , основанной на одной из реляционных СУБД. Широкоизвестна аналогия индексов с оглавлением книги , по которому читатель быстро находит нужную страницу. К сожалению , начинающие разработчики информационных систем иногда как-то недооценивают значение индексов при работе с базами данных. Поэтому , для такой категории пользователей имеет смысл рассказать хотя бы в двух словах о том, как работать с индексами в MS SQL Server.

Прежде чем переходить к чисто прикладному вопросу — Как создать индексы в MS SQL Server Express Edition, логично дать для начала кое-какие элементарные сведения.

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

Далее. Есть такое понятие — первичный ключ ( P rimary K ey , PK ), который однозначно идентифицирует запись в таблице. Лучшим кандидатом на первичный ключ, допустим, в справочных таблицах, является код (или идентификатор) справочника. У первичного ключа есть одна полезная особенность — для него индекс создаётся автоматически. По определению, первичный ключ может содержать только уникальные значения и не содержать значение NULL . Структуры поиска по полю, являющимся первичным ключом, работают более эффективно в силу перечисленных выше причин — уникальности и гарантированного индекса.

Приведём ещё несколько примеров информационных систем, в которых индексировать таблицы по уникальному ключу следует как отче наш. Предположим , вы создаёте базу данных клиентов и сделанных ими заказов. В такой системе у вас будут две основные ‘ оперативные ‘ таблицы — таблица Клиенты и таблица Заказы. При занесении нового клиента в базу , ваша система должна автоматически присваивать ему уникальный > Как правило , все современные СУБД имеют тип поля автоинкремент , созданного как раз для автоматической генерации уникального идентификатора записи. Так вот , для данного случая не подлежит сомнению тот факт , что необходимо индексировать таблицу Клиенты по полю >Или другой случай. Вы создаете информационную систему для страховки автомобилей — объектов страхового случая. Вполне естественной в такой базе данных будет присутствие оперативной таблицы застрахованных автомобилей. И в этом случае при занесении нового автомобиля , система должна присваивать ему уникальный > и выстраивать для него соотв. индекс .

В продолжении темы наиболее распространенных примеров можно привести ещё и банковскую информационную систему. Представим , что вы как разработчик отвечаете за подсистему работы с частными лицами. В такой системе будут необходимы как минимум две операционные таблицы : таблица Клиентов ( Clients) и таблица Счетов ( Accounts). Так вот , в этом случае ваша программа управления вкладами частных лиц при открытии счета нового клиента должна завести такого нового клиента , и присвоить ему уникальный идентификатор >Но поскольку один клиент может иметь несколько счетов (то бишь вкладов) , то логично связать таблицы Clients и Accounts по полю >и создать для него индексы в обеих таблицах.

В общем , я думаю , что логику вы уловили .

Ну вот, после небольшого теоретического вступления, можно и переходить к прикладным вопросам. Представим, что в базе данных MyShop есть таблица заказов Zakazy, содержащая информацию о сделанных потребителями (customers, классика жанра :-) заказах:

Наша учебная таблица Zakazy имеет сл. структуру:

ZakazID — уникальный идентификатор (код) заказа

Number — обозначение заказа

Data- дата заказа

Customer >код заказчика , оформившего заказ

Summa — цена вопроса :- ) — сумма заказа

Прим. О том , как создать таблицу в базе данных более подробно можно посмотреть в этой статье

В соответствии с изложенной выше теорией , первичным ключом таблицы Zakazy имеет смысл сделать поле Zakaz >Для этого в SQL Server Management Studio становимся на нужную таблицу и по правой кнопке выбираем в контекстном меню Создать столбец :

Попадаем в настройки структуры таблицы . Становимся на поле Zakaz >правая кнопка мыши и выбираем Задать первичный ключ :

После такой манипуляции структура таблицы примет следующий вид — обратите внимание на значок ключа в ZakazyID:

Первичный ключ готов!

О том, как создать внешний ключ (Foreign Key) можно посмотреть в этой статье

Теперь немного о том , какие поля (столбцы) таблицы следует индексировать — создавать для них индексы. Тут можно дать следующую рекомендацию — прежде всего индексировать следует те поля , которые участвуют в различных ‘ключевых’запросах. Причём начинать надо с запросов, что носят наиболее стабильный характер и реже всего изменяются по логике работы вашей информационной системы. Предположим, для нашего примера, что по логике программы нужно постоянно делать выборки из таблицы Zakazy по коду клиента (заказчика), ну скажем, по такому SQL-запросу:

Select * From Zakazy Where Customer >

В приведённом примере , однозначно надо индексировать нашу учебную таблицу по полю Customer >Делаем это сл. образом. В нужной таблице становимся на Индексы , и по правой кнопке выбираем Создать индекс :

. и попадаем в форму создания индекса :

На форме выше кликнем по кнопке Добавить и в сл. форме выбираем поле (столбец) таблицы для индексации — Customer >для нашего случая :

В форме задания опций индекса выбираем тип индекса (некластеризованный) и задаём имя индекса :

Жмём на форме выше OK, на ветке Индексы в контекстном меню Обновить и видим наш вновь созданный индекс CustID:

В завершении необходимо заметить , что чрезмерное увлечение индексами (тем более , если часть из которых уже не нужна) может даже снизить эффективность поиска и выборки данных из таблицы. В общем , как и во всяком любом другом деле , здесь нужна определённая оптимизация — а то можно и перестараться .

Еще несколько полезных советов для пользователей о работе с MS SQL Server можно посмотреть здесь

Индексы

В этой статье рассматриваются индексы и их роль в оптимизации времени выполнения запросов. В первой части статьи обсуждаются разные формы индексов и способы их хранения. Далее исследуются три основные инструкции языка Transact-SQL, применяемые для работы с индексами: CREATE INDEX, ALTER INDEX и DROP INDEX. Потом рассматривается фрагментация индексов ее влияния на производительность системы. После этого дается несколько общих рекомендаций по созданию индексов и описывается несколько специальных типов индексов.

Общие сведения

Системы баз данных обычно используют индексы для обеспечения быстрого доступа к реляционным данным. Индекс представляет собой отдельную физическую структуру данных, которая позволяет получать быстрый доступ к одной или нескольким строкам данных. Таким образом, правильная настройка индексов является ключевым аспектом улучшения производительности запросов.

Индекс базы данных во многом сходен с индексом (алфавитным указателем) книги. Когда нам нужно быстро найти какую-либо тему в книге, мы сначала смотрим в индексе, на каких страницах книги эта тема рассматривается, а потом сразу же открываем нужную страницу. Подобным образом, при поиске определенной строки таблицы компонент Database Engine обращается к индексу, чтобы узнать ее физическое местонахождение.

Но между индексом книги и индексом базы данных есть две существенные разницы:

Читатель книги имеет возможность самому решать, использовать ли индекс в каждом конкретном случае или нет. Пользователь базы данных такой возможности не имеет, и за него это решение принимает компонент системы, называемый оптимизатором запросов. (Пользователь может манипулировать использованием индексов посредством подсказок индексов, но эти подсказки рекомендуется применять только в ограниченном числе специальных случаев.)

Индекс для определенной книги создается вместе с книгой, после чего он больше не изменяется. Это означает, что индекс для определенной темы всегда будет указывать на один и тот же номер страницы. В противоположность, индекс базы данных может меняться при каждом изменении соответствующих данных.

Если для таблицы отсутствует подходящий индекс, для выборки строк система использует метод сканирования таблицы. Выражение сканирование таблицы означает, что система последовательно извлекает и исследует каждую строку таблицы (от первой до последней), и помещает строку в результирующий набор, если для нее удовлетворяется условие поиска в предложении WHERE. Таким образом, все строки извлекаются в соответствии с их физическим расположением в памяти. Этот метод менее эффективен, чем доступ с использованием индексов, как объясняется далее.

Индексы сохраняются в дополнительных структурах базы данных, называющихся страницами индексов. Для каждой индексируемой строки имеется элемент индекса (index entry), который сохраняется на странице индексов. Каждый элемент индекса состоит из ключа индекса и указателя. Вот поэтому элемент индекса значительно короче, чем строка таблицы, на которую он указывает. По этой причине количество элементов индекса на каждой странице индексов намного больше, чем количество строк в странице данных.

Это свойство индексов играет очень важную роль, поскольку количество операций ввода/вывода, требуемых для прохода по страницам индексов, значительно меньше, чем количество операций ввода/вывода, требуемых для прохода по соответствующим страницам данных. Иными словами, для сканирования таблицы, скорей всего, потребовалось бы намного больше операций ввода/вывода, чем для сканирования индекса этой таблицы.

Индексы компонента Database Engine создаются, используя структуру данных сбалансированного дерева B+. B+-дерево имеет древовидную структуру, в которой все самые нижние узлы находятся на расстоянии одинакового количества уровней от вершины (корневого узла) дерева. Это свойство поддерживается даже тогда, когда в индексированный столбец добавляются или удаляются данные.

На рисунке ниже показана структура B+-дерева для таблицы Employee и прямой доступ к строке в этой таблице со значением 25348 для столбца Id. (Предполагается, что таблица Employee проиндексирована по столбцу Id.) На этом рисунке можно также видеть, что B+-дерево состоит из корневого узла, узлов дерева и промежуточных узлов, количество которых может быть от нуля и больше:

Поиск в этом дереве значения 25348 можно выполнить следующим образом. Начиная с корня дерева, выполняется поиск наименьшего значения ключа, большего или равного требуемому значению. Таким образом, в корневом узле таким значением будет 29346, поэтому делается переход на промежуточный узел, связанный с этим значением. В этом узле заданным требованиям отвечает значение 28559, вследствие чего выполняется переход на узел дерева, связанный с этим значением. Этот узел и содержит искомое значение 25348. Определив требуемый индекс, мы можем извлечь его строку из таблицы данных с помощью соответствующих указателей. (Альтернативным эквивалентным подходом будет поиск меньшего или равного значения индекса.)

Индексированный поиск обычно является предпочтительным методом поиска в таблицах с большим количеством строк по причине его очевидного преимущества. Используя индексированный поиск, мы можем найти любую строку в таблице за очень короткое время, применив лишь несколько операций ввода/вывода. А последовательный поиск (т.е. сканирование таблицы от первой строки до последней) требует тем больше времени, чем дальше находится требуемая строка.

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

Кластеризованные индексы

определяет физический порядок данных в таблице. Компонент Database Engine позволяет создавать для таблицы лишь один кластеризованный индекс, т.к. строки таблицы нельзя упорядочить физически более чем одним способом. Поиск с использованием кластеризованного индекса выполняется от корневого узла B+-дерева по направлению к узлам дерева, которые связаны между собой в двунаправленный связанный список (doubly linked list), называющийся цепочкой страниц (page chain).

Важным свойством кластеризованного индекса является та особенность, что его узлы дерева содержат страницы данных. (Узлы кластеризованного индекса всех других уровней содержат страницы индекса.) Таблица, для которой определен кластеризованный индекс (явно или неявно), называется кластеризованной таблицей. Структура B+-дерева кластеризованного индекса показана на рисунке ниже:

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

Кластеризованные индексы обеспечивают очень быстрый доступ к данным, когда запрос осуществляет поиск в диапазоне значений.

Некластеризованные индексы

Структура некластеризованного индекса точно такая же, как и кластеризованного, но с двумя важными отличиями:

некластеризованный индекс не изменяет физическое упорядочивание строк таблицы;

страницы узлов некластеризованного индекса состоят из ключей индекса и закладок.

Если для таблицы определить один или более некластеризованных индексов, физический порядок строк этой таблицы не будет изменен. Для каждого некластеризованного индекса компонент Database Engine создает дополнительную индексную структуру, которая сохраняется в индексных страницах. Структура B+-дерева некластеризованного индекса показана на рисунке ниже:

Закладка в некластеризованном индексе указывает, где находится строка, соответствующая ключу индекса. Составляющая закладки ключа индекса может быть двух видов, в зависимости от того, является ли таблица кластеризованной таблицей или кучей (heap). (Согласно терминологии SQL Server, кучей называется таблица без кластеризованного индекса.) Если существует кластеризованный индекс, то закладка некластеризованного индекса показывает B+-дерево кластеризованного индекса таблицы. Если таблица не имеет кластеризованного индекса, закладка идентична идентификатору строки (RID — Row Identifier), состоящего из трех частей: адреса файла, в котором хранится таблица, адреса физического блока (страницы), в котором хранится строка, и смещения строки в странице.

Как уже упоминалось ранее, поиск данных с использованием некластеризованного индекса можно осуществлять двумя разными способами, в зависимости от типа таблицы:

куча — прохождение при поиске по структуре некластеризованного индекса, после чего строка извлекается, используя идентификатор строки;

кластеризованная таблица — прохождение при поиске по структуре некластеризованного индекса, после чего следует прохождение по соответствующему кластеризованному индексу.

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

Язык Transact-SQL и индексы

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

Создание индексов

Индекс для таблицы создается с помощью инструкции CREATE INDEX. Эта инструкция имеет следующий синтаксис:

Параметр index_name задает имя создаваемого индекса. Индекс можно создать для одного или больше столбцов одной таблицы, обозначаемой параметром table_name. Столбец, для которого создается индекс, указывается параметром column1. Числовой суффикс этого параметра указывает на то, что индекс можно создать для нескольких столбцов таблицы. Компонент Database Engine также поддерживает создание индексов для представлений.

Можно проиндексировать любой столбец таблицы. Это означает, что столбцы, содержащие значения типа данных VARBINARY(max), BIGINT и SQL_VARIANT, также могут быть индексированы.

Индекс может быть простым или составным. Простой индекс создается по одному столбцу, а составной индекс — по нескольким столбцам. Для составного индекса существуют определенные ограничения, связанные с его размером и количеством столбцов. Индекс может иметь максимум 900 байтов и не более 16 столбцов.

Параметр UNIQUE указывает, что проиндексированный столбец может содержать только однозначные (т.е. неповторяющиеся) значения. В однозначном составном индексе однозначной должна быть комбинация значений всех столбцов каждой строки. Если ключевое слово UNIQUE не указывается, то повторяющиеся значения в проиндексированном столбце (столбцах) разрешаются.

Параметр CLUSTERED задает кластеризованный индекс, а параметр NONCLUSTERED (применяется по умолчанию) указывает, что индекс не изменяет порядок строк в таблице. Компонент Database Engine разрешает для таблицы максимум 249 некластеризованных индексов.

Возможности компонента Database Engine были расширены, позволяя создать поддержку индексов с убывающим порядком значений столбцов. Параметр ASC после имени столбца указывает, что индекс создается с возрастающим порядком значений столбца, а параметр DESC означает убывающий порядок значений столбца индекса. Таким образом, в использовании индекса предоставляется большая гибкость. С убывающим порядком следует создавать составные индексы на столбцах, значения которых упорядочены в противоположных направлениях.

Параметр INCLUDE позволяет указать неключевые столбцы, которые добавляются к страницам узлов некластеризованного индекса. Имена столбцов в списке INCLUDE не должны повторяться, и столбец нельзя использовать одновременно как ключевой и неключевой.

Чтобы по-настоящему понять полезность параметра INCLUDE, нужно понимать, что собой представляет покрывающий индекс (covering index). Если все столбцы запроса включены в индекс, то можно получить значительное повышение производительности, т.к. оптимизатор запросов может определить местонахождение всех значений столбцов по страницам индекса, не обращаясь к данным в таблице. Такая возможность называется покрывающим индексом или покрывающим запросом. Поэтому включение в страницы узлов некластеризованного индекса дополнительных неключевых столбцов позволит получить больше покрывающих запросов, при этом их производительность будет значительно повышена.

Параметр FILLFACTOR задает заполнение в процентах каждой страницы индекса во время его создания. Значение параметра FILLFACTOR можно установить в диапазоне от 1 до 100. При значении n=100 каждая страница индекса заполняется на 100%, т.е. существующая страница узла так же, как страница, не относящаяся к узлу, не будет иметь свободного места для вставки новых строк. Поэтому это значение рекомендуется применять только для статических таблиц. (Значение по умолчанию, n=0, означает, что страницы узлов индекса заполняются полностью, а каждая из промежуточных страниц содержит свободное место для одной записи.)

При значении параметра FILLFACTOR между 1 и 99 страницы узлов создаваемой структуры индекса будут содержать свободное место. Чем больше значение n, тем меньше свободного места в страницах узлов индекса. Например, при значении n=60 каждая страница узлов индекса будет иметь 40% свободного места для вставки строк индекса в дальнейшем. (Строки индекса вставляются посредством инструкции INSERT или UPDATE.) Таким образом, значение n=60 будет разумным для таблиц, данные которых подвергаются довольно частым изменениям. При значениях параметра FILLFACTOR между 1 и 99 промежуточные страницы индекса содержат свободное место для одной записи каждая.

После создания индекса в процессе его использования значение FILLFACTOR не поддерживается. Иными словами, оно только указывает объем зарезервированного места с имеющимися данными при задании процентного соотношения для свободного места. Для восстановления исходного значения параметра FILLFACTOR применяется инструкция ALTER INDEX.

Параметр PAD_INDEX тесно связан с параметром FILLFACTOR. Параметр FILLFACTOR в основном задает объем свободного пространства в процентах от общего объема страниц узлов индекса. А параметр PAD_INDEX указывает, что значение параметра FILLFACTOR применяется как к страницам индекса, так и к страницам данных в индексе.

Параметр DROP_EXISTING позволяет повысить производительность при воспроизведении кластеризованного индекса для таблицы, которая также имеет некластеризованный индекс. Более подробную информацию смотрите далее в разделе «Пересоздание индекса».

Параметр SORT_IN_TEMPDB применяется для помещения в системную базу данных tempdb данных промежуточных операций сортировки, применяющихся при создании индекса. Это может повысить производительность, если база данных tempdb размещена на другом диске, чем данные.


Параметр IGNORE_DUP_KEY разрешает системе игнорировать попытку вставки повторяющихся значений в индексированные столбцы. Этот параметр следует применять только для того, чтобы избежать прекращения выполнения длительной транзакции, когда инструкция INSERT вставляет дубликат данных в индексированный столбец. Если этот параметр активирован, то при попытке инструкции INSERT вставить в таблицу строки, нарушающие однозначность индекса, система базы данных вместо аварийного завершения выполнения всей инструкции просто выдает предупреждение. При этом компонент Database Engine не вставляет строки с дубликатами значений ключа, а просто игнорирует их и добавляет правильные строки. Если же этот параметр не установлен, то выполнение всей инструкции будет аварийно завершено.

Когда параметр ALLOW_ROW_LOCKS активирован (имеет значение on), система применяет блокировку строк. Подобным образом, когда активирован параметр ALLOW_PAGE_LOCKS, система применяет блокировку страниц при параллельном доступе. Параметр STATISTICS_NORECOMPUTE определяет состояние автоматического перерасчета статистики указанного индекса.

Активированный параметр ONLINE позволяет создавать, пересоздавать и удалять индекс в диалоговом режиме. Данный параметр позволяет в процессе изменения индекса одновременно изменять данные основной таблицы или кластеризованного индекса и любых связанных индексов. Например, в процессе пересоздания кластеризованного индекса можно продолжать обновлять его данные и выполнять запросы по этим данным.

Параметр ON создает указанный индекс или на файловой группе по умолчанию (значение default), или на указанной файловой группе (значение file_group).

В примере ниже показано создание некластеризованного индекса для столбца Id таблицы Employee:

Создание однозначного составного индекса показано в примере ниже:

В этом примере значения в каждом столбце должны быть однозначными. При создании индекса заполняется 80% пространства каждой страницы узлов индекса.

Создание однозначного индекса для столбца невозможно, если этот столбец содержит повторяющиеся значения. Такой индекс можно создать лишь в том случае, если каждое значение (включая значение NULL) встречается в столбце только один раз. Кроме этого, любая попытка вставить или изменить существующее значение данных в столбец, включенный в существующий уникальный индекс, будет отвергнута системой в случае дублирования значения.

Получение информации о фрагментации индекса

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

Для получения информации о внутренней фрагментации индекса применяется динамическое административное представление DMV, называемое sys.dm_db_index_physical_stats. Это DMV возвращает информацию об объеме и фрагментации данных и индексов указанной страницы. Для каждой страницы возвращается одна строка для каждого уровня B+-дерева. С помощью этого DMV можно получить информацию о степени фрагментации строк в страницах данных, на основе которой можно принять решение о необходимости реорганизации данных.

Использование представления sys.dm_db_index_physical_stats показано в примере ниже. (Прежде чем запускать пакет в примере на выполнение, необходимо удалить все существующие индексы таблицы Works_on. Для удаления индексов используется инструкция DROP INDEX, применение которой показано позже.)

Как видно из примера, представление sys.dm_db_index_physical_stats имеет пять параметров. Первые три параметра определяют идентификаторы текущей базы данных, таблицы и индекса соответственно. Четвертый параметр задает идентификатор раздела, а последний определяет уровень сканирования, применяемый для получения статистической информации. (Значение по умолчанию для определенного параметра можно указать посредством значения NULL.)

Наиболее важными из столбцов этого представления являются столбцы avg_fragmentation_in_percent и avg_page_space_used_in_percent. В первом указывается средний уровень фрагментации в процентах, а во втором определяется объем занятого пространства в процентах.

Редактирование информации индекса

После ознакомления с информацией о фрагментации индекса, как было рассмотрено в предыдущем разделе, эту и другую информацию индекса можно редактировать с помощью следующих системных средств:

представления каталога sys.indexes;

представления каталога sys.index_columns;

системной процедуры sp_helpindex;

функции свойств objectproperty;

среды управления Management Studio сервера SQL Server;

динамического административного представления DMV sys.dm_db_index_usage_stats;

динамического административного представления DMV sys.dm_db_missing_index_details.

Представление каталога sys.indexes содержит строку для каждого индекса и строку для каждой таблицы без кластеризованного индекса. Наиболее важными столбцами этого представления каталога являются столбцы object_id, name и index_id. Столбец object_id содержит имя объекта базы данных, которой принадлежит индекс, а столбцы name и index_id содержат имя и идентификатор этого индекса соответственно.

Представление каталога sys.index_columns содержит строку для каждого столбца, являющегося частью индекса или кучи. Эту информацию можно использовать совместно с информацией, полученной посредством представления каталога sys.indexes, для получения дополнительных сведений о свойствах указанного индекса.

Системная процедура sp_helpindex возвращает данные об индексах таблицы, а также статистическую информацию для столбцов. Эта процедура имеет следующий синтаксис:

Здесь переменная @db_object представляет имя таблицы.

Применительно к индексам, функция свойств objectproperty имеет два свойства: IsIndexed и IsIndexable. Первое свойство предоставляет сведения о наличии индекса у таблицы или представления, а второе указывает, поддается ли таблица или представление индексированию.

Для редактирования информации существующего индекса с помощью среды SQL Server Management Studio выберите требуемую базу данных в папке Databases, разверните узел Tables, в этом узле разверните требуемую таблицу и ее папку Indexes. В папке таблицы Indexes отобразится список всех существующих индексов для данной таблицы. Двойной щелчок мышью по индексу откроет диалоговое окно Index Properties со свойствами этого индекса. (Создать новый индекс или удалить существующий можно также с помощью среды Management Studio.)

Представление sys.dm_db_index_usage_stats возвращает подсчет разных типов операций с индексами и время последнего выполнения каждого типа операции. Каждая отдельная операция поиска, просмотра или обновления по указанному индексу при исполнении одного запроса считается использованием индекса и увеличивает на единицу значение соответствующего счетчика в этом DMV. Таким образом можно получить общую информацию о частоте использования индекса, чтобы на ее основе определить, какие индексы используются больше, а какие меньше.

Представление sys.dm_db_missing_index_details возвращает подробную информацию о столбцах таблицы, для которых отсутствуют индексы. Наиболее важными столбцами этого DMV являются столбцы index_handle и object_id. Значение в первом столбце определяет конкретный отсутствующий индекс, а во втором — таблицу, в которой отсутствует индекс.

Изменение индексов

Компонент Database Engine является одной из немногих систем баз данных, которые поддерживают инструкцию ALTER INDEX. Эту инструкцию можно использовать для выполнения операций по обслуживанию индекса. Синтаксис инструкции ALTER INDEX очень сходен с синтаксисом инструкции CREATE INDEX. Иными словами, эта инструкция позволяет изменять значения параметров ALLOW_ROW_LOCKS, ALLOW_PAGE_LOCKS, IGNORE_DUP_KEY и STATISTICS_NORECOMPUTE, которые были описаны ранее при рассмотрении инструкции CREATE INDEX.

Кроме вышеперечисленных параметров, инструкция ALTER INDEX поддерживает три другие параметра:

параметр REBUILD, используемый для пересоздания индекса;

параметр REORGANIZE, используемый для реорганизации страниц узлов индекса;

параметр DISABLE, используемый для отключения индекса. Эти три параметра рассматриваются в следующих подразделах.

Пересоздание индекса

При любом изменении данных, используя инструкции INSERT, UPDATE или DELETE, возможна фрагментация данных. Если эти данные проиндексированы, то также возможна фрагментация индекса, когда информация индекса оказывается разбросанной по разным физическим страницам. В результате фрагментации данных индекса компонент Database Engine может быть вынужден выполнять дополнительные операции чтения данных, что понижает общую производительность системы. В таком случае требуется пересоздать (REBUILD) все фрагментированные индексы.

Это можно сделать двумя способами:

посредством параметра REBUILD инструкции ALTER INDEX;

посредством параметра DROP_EXISTING инструкции CREATE INDEX.

Параметр REBUILD применяется для пересоздания индексов. Если для этого параметра вместо имени индекса указать ALL, будут вновь созданы все индексы таблицы. (Разрешив динамическое пересоздание индексов, вам не нужно будет удалять и создавать их заново.)

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

Параметр DROP_EXISTING более мощный, чем параметр REBUILD, поскольку он более гибкий и предоставляет несколько опций, таких как изменение столбцов, составляющих индекс, и изменение некластеризованного индекса в кластеризованный.

Реорганизация страниц узлов индекса

Параметр REORGANIZE инструкции ALTER INDEX задает реорганизацию страниц узлов указанного индекса, чтобы физический порядок страниц совпадал с их логическим порядком — слева направо. Это удаляет определенный объем фрагментации индекса, повышая его производительность.

Отключение индекса

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

При отключенном кластеризованном индексе таблицы данные этой таблицы будут недоступны, так как все страницы данных таблицы с кластеризованным индексом хранятся в его узлах дерева.

Удаление и переименование индексов

Для удаления индексов в текущей базе данных применяется инструкция DROP INDEX. Обратите внимание, что удаление кластеризованного индекса таблицы может быть очень ресурсоемкой операцией, т.к. потребуется пересоздать все некластеризованные индексы. (Все некластеризованные индексы используют ключ индекса кластеризованного индекса, как указатель в своих страницах узлов.) Использование инструкции DROP INDEX для удаления индекса показано в примере ниже:

Инструкция DROP INDEX имеет дополнительный параметр MOVE TO, значение которого аналогично параметру ON инструкции CREATE INDEX. Иными словами, с помощью этого параметра можно указать, куда переместить строки данных, находящиеся в страницах узлов кластеризованного индекса. Данные перемещаются в новое место в виде кучи. Для нового места хранения данных можно указать или файловую группу по умолчанию, или именованную файловую группу.

Инструкцию DROP INDEX нельзя использовать для удаления индексов, которые создаются неявно системой для ограничений целостности, таких индексов, как PRIMARY KEY и UNIQUE. Чтобы удалить такие индексы, нужно удалить соответствующее ограничение.

Индексы можно переименовывать с помощью системной процедуры sp_rename.

Индексы можно также создавать, изменять и удалять в среде Management Studio с помощью диаграмм баз данных или обозревателя объектов. Но самым простым способом будет использовать папку Indexes требуемой таблицы. Управление индексами в среде Management Studio аналогично управлению таблицами в этой среде.

Рекомендации по созданию и использованию индексов

Хотя компонент Database Engine не накладывает никаких практических ограничений на количество индексов, по паре причин это количество следует ограничивать. Во-первых, каждый индекс занимает определенный объем дискового пространства, следовательно, существует вероятность того, что общее количество страниц индекса базы данных может превысить количество страниц данных в базе. Во-вторых, в отличие от получения выгоды при использовании индекса для выборки данных, вставка и удаление данных такой выгоды не предоставляют по причине необходимости обслуживания индекса. Чем больше индексов имеет таблица, тем больший требуется объем работы по их реорганизации. Общим правилом будет разумно выбирать индексы для частых запросов, а затем оценивать их использование.

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

Индексы и условия предложения WHERE

Если предложение WHERE инструкции SELECT содержит условие поиска с одним столбцом, то для этого столбца следует создать индекс. Это особенно рекомендуется при высокой селективности условия. Под селективностью (selectivity) условия имеется в виду соотношение количества строк, удовлетворяющих условию, к общему количеству строк в таблице. Высокой селективности соответствует меньшему значению этого соотношения. Обработка поиска с использованием индексированного столбца будет наиболее успешной при селективности условия, не превышающей 5%.

Столбец не следует индексировать при постоянном уровне селективности условия 80% или более. В таком случае для страниц индекса потребуются дополнительные операции ввода/вывода, которые уменьшат любую экономию времени, достигаемую за счет использования индексов. В этом случае быстрее выполнять поиск сканированием таблицы, что и будет обычно выбрано оптимизатором запросов, делая индекс бесполезным.

Если условие поиска часто используемого запроса содержит операторы AND, лучше всего будет создать составной индекс по всем столбцам таблицы, указанным в предложении WHERE инструкции SELECT. Создание такого составного индекса показано в примере ниже:

В этом примере происходит создание составного индекса по всем столбцам предложения WHERE. В этом запросе оператором AND соединены два условия, поэтому для обоих столбцов в этих условиях следует создать составной некластеризованный индекс.

Индексы и оператор соединения

В случае операции соединения рекомендуется создавать индекс для каждого соединяемого столбца. Соединяемые столбцы часто представляют первичный ключ одной из таблицы и соответствующий внешний ключ другой таблицы. Если указываются ограничения для обеспечения целостности PRIMARY KEY и FOREIGN KEY для соответствующих соединяемых столбцов, следует создать только некластеризованный индекс для столбца внешнего ключа, т.к. система неявно создаст кластеризованный индекс для столбца первичного ключа.

В примере ниже показано создание индексов, которые будут использованы, если у вас есть запрос с операцией соединения и дополнительным фильтром:

Для запроса в примере рекомендуется создать два отдельных индекса для столбца Id в таблице Employee, и столбца EmpId в таблице Works_on. Кроме этого, следует создать дополнительный индекс для столбца EnterDate.

Покрывающий индекс

Как уже упоминалось ранее, включение всех столбцов запроса в индекс может значительно повысить производительность запроса. Создание такого индекса, называемого покрывающим (covering), показано в примере ниже:

В этом примере в первую очередь из таблицы Address удаляется индекс IX_Address_StateProvinceID. Затем создается новый индекс, который помимо столбца PostalCode включает два дополнительных столбца. Наконец, инструкция SELECT в конце примера показывает запрос, покрываемый индексом. Для этого запроса системе нет необходимости выполнять поиск данных в страницах данных, поскольку оптимизатор запросов может найти все значения столбцов в страницах узлов некластеризованного индекса.

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

Индексы для вычисляемых столбцов

Компонент Database Engine позволяет создавать следующие специальные типы индексов:

индексы для вычисляемых столбцов;

индексы сохранения столбца;

В этом разделе рассматриваются вычисляемые столбцы и связанные с ними индексы.

называется столбец таблицы, в котором сохраняются результаты вычислений данных таблицы. Такой столбец может быть виртуальным или постоянным. Эти два типа столбцов рассмотрены в следующих далее подразделах.

Виртуальные вычисляемые столбцы

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

Таблица Orders в этом примере имеет два виртуальных вычисляемых столбца: total и shippeddate. Столбец total вычисляется с использованием двух других столбцов, price и quantity, а столбец shippeddate вычисляется при использовании функции DATEADD и столбца orderdate.

Постоянные вычисляемые столбцы

Компонент Database Engine позволяет создавать индексы для детерминированных вычисляемых столбцов, где базовые столбцы имеют точные типы данных. (Вычисляемый столбец называется детерминированным, если всегда возвращаются одни и те же значения для одних и тех же данных таблицы.)

Индексированный вычисляемый столбец может быть создан только в том случае, если следующим параметрам инструкции SET присвоено значение ON (эти параметры обеспечивают детерминированность столбца):

Кроме этого, параметру NUMERIC_ROUNDABORT нужно присвоить значение off.

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

После выполнения инструкции CREATE INDEX вычисляемый столбец total будет присутствовать в таблице физически. Это означает, что все обновления базовых столбцов вычисляемого столбца будут вызывать его обновление.

Столбец можно сделать постоянным и другим способом, используя параметр PERSISTED. Этот параметр позволяет задать физическое наличие вычисляемого столбца, даже не создавая соответствующего кластеризованного индекса. Эта возможность требуется для создания физических вычисляемых столбцов, которые создаются на столбцах с приблизительным типом данных (float или real). (Как упоминалось ранее, индекс для вычисляемого столбца можно создать только в том случае, если его базовые столбцы имеют точный тип данных.)

ALTER INDEX

Use the ALTER INDEX statement to change or rebuild an existing index.

CREATE INDEX for information on creating an index

The index must be in your own schema or you must have ALTER ANY INDEX system privilege.


To execute the MONITORING USAGE clause, the index must be in your own schema.

To modify a domain index, you must have EXECUTE object privilege on the indextype of the index.

Schema object privileges are granted on the parent index, not on individual index partitions or subpartitions.

You must have tablespace quota to modify, rebuild, or split an index partition or to modify or rebuild an index subpartition.

(The XMLIndex_parameters are documented in Oracle XML DB Developer’s Guide .)

( The XMLIndex_parameters are documented in Oracle XML DB Developer’s Guide .)

Specify the schema containing the index. If you omit schema , then Oracle Database assumes the index is in your own schema.

Specify the name of the index to be altered.

Restrictions on Modifying Indexes The modification of indexes is subject to the following restrictions:

If index is a domain index, then you can specify only the PARAMETERS clause, the RENAME clause, the rebuild_clause (with or without the PARAMETERS clause), the parallel_clause , or the UNUSABLE clause. No other clauses are valid.

You cannot alter or rename a domain index that is marked LOADING or FAILED . If an index is marked FAILED , then the only clause you can specify is REBUILD .

Oracle Database Data Cartridge Developer’s Guide for information on the LOADING and FAILED states of domain indexes

Use the deallocate_unused_clause to explicitly deallocate unused space at the end of the index and make the freed space available for other segments in the tablespace.

If index is range-partitioned or hash-partitioned, then Oracle Database deallocates unused space from each index partition. If index is a local index on a composite-partitioned table, then Oracle Database deallocates unused space from each index subpartition.

Restrictions on Deallocating Space Deallocation of space is subject to the following restrictions:

You cannot specify this clause for an index on a temporary table.

You cannot specify this clause and also specify the rebuild_clause .

Refer to deallocate_unused_clause for a full description of this clause.

KEEP integer The KEEP clause lets you specify the number of bytes above the high water mark that the index will have after deallocation. If the number of remaining extents is less than MINEXTENTS , then MINEXTENTS is set to the current number of extents. If the initial extent becomes smaller than INITIAL , then INITIAL is set to the value of the current initial extent. If you omit KEEP , then all unused space is freed.

Refer to ALTER TABLE for a complete description of this clause.

The allocate_extent_clause lets you explicitly allocate a new extent for the index. For a local index on a hash-partitioned table, Oracle Database allocates a new extent for each partition of the index.

Restriction on Allocating Extents You cannot specify this clause for an index on a temporary table or for a range-partitioned or composite-partitioned index.

Refer to allocate_extent_clause for a full description of this clause.

Use this clause to compact the index segments. Specifying ALTER INDEX . SHRINK SPACE COMPACT is equivalent to specifying ALTER INDEX . COALESCE .

For complete information on this clause, refer to shrink_clause in the documentation on CREATE TABLE .

Restriction on Shrinking Index Segments You cannot specify this clause for for a bitmap join index or for a function-based index.

Use the PARALLEL clause to change the default degree of parallelism for queries and DML on the index.

Restriction on Parallelizing Indexes You cannot specify this clause for an index on a temporary table.

For complete information on this clause, refer to parallel_clause in the documentation on CREATE TABLE .

Use the physical_attributes_clause to change the values of parameters for a nonpartitioned index, all partitions and subpartitions of a partitioned index, a specified partition, or all subpartitions of a specified partition.

the physical attributes parameters in CREATE TABLE

Restrictions on Index Physical Attributes Index physical attributes are subject to the following restrictions:

You cannot specify this clause for an index on a temporary table.

You cannot specify the PCTUSED parameter at all when altering an index.

You can specify the PCTFREE parameter only as part of the rebuild_clause , the modify_index_default_attrs clause, or the split_partition_clause .

Use the storage_clause to change the storage parameters for a nonpartitioned index, index partition, or all partitions of a partitioned index, or default values of these parameters for a partitioned index. Refer to storage_clause for complete information on this clause.

Use the logging_clause to change the logging attribute of the index. If you also specify the REBUILD clause, then this new setting affects the rebuild operation. If you specify a different value for logging in the REBUILD clause, then Oracle Database uses the last logging value specified as the logging attribute of the index and of the rebuild operation.

An index segment can have logging attributes different from those of the base table and different from those of other index segments for the same base table.

Restriction on Index Logging You cannot specify this clause for an index on a temporary table.

logging_clause for a full description of this clause

Oracle Database Data Warehousing Guide for more information about parallel DML

These keywords are deprecated and have been replaced with LOGGING and NOLOGGING , respectively. Although RECOVERABLE and UNRECOVERABLE are supported for backward compatibility, Oracle strongly recommends that you use the LOGGING and NOLOGGING keywords.

Илон Маск рекомендует:  Что такое код udm_cat_list

RECOVERABLE is not a valid keyword for creating partitioned tables or LOB storage characteristics. UNRECOVERABLE is not a valid keyword for creating partitioned or index-organized tables. Also, it can be specified only with the AS subquery clause of CREATE INDEX .

rebuild_ cl au se

Use the rebuild_clause to re-create an existing index or one of its partitions or subpartitions. If index is marked UNUSABLE , then a successful rebuild will mark it USABLE . For a function-based index, this clause also enables the index. If the function on which the index is based does not exist, then the rebuild statement will fail.

When you rebuild the secondary index of an index-organized table, Oracle Database preserves the primary key columns contained in the logical rowid when the index was created. Therefore, if the index was created with the COMPATIBLE initialization parameter set to less than 10.0.0, the rebuilt index will contain the index key and any of the primary key columns of the table that are not also in the index key. If the index was created with the COMPATIBLE initialization parameter set to 10.0.0 or greater, then the rebuilt index will contain the index key and all the primary key columns of the table, including those also in the index key.

Restrictions on Rebuilding Indexes The rebuilding of indexes is subject to the following restrictions:

You cannot rebuild an index on a temporary table.

You cannot rebuild a bitmap index that is marked INVALID . Instead, you must drop and then re-create it.

You cannot rebuild an entire partitioned index. You must rebuild each partition or subpartition, as described for the PARTITION clause.

You cannot specify the deallocate_unused_clause in the same statement as the rebuild_clause .

You cannot change the value of the PCTFREE parameter for the index as a whole ( ALTER INDEX ) or for a partition ( ALTER INDEX . MODIFY PARTITION ). You can specify PCTFREE in all other forms of the ALTER INDEX statement.

For a domain index:

You can specify only the PARAMETERS clause (either for the index or for a partition of the index) or the parallel_clause . No other rebuild clauses are valid.

You can rebuild an index only if the index is not marked IN_PROGRESS .

You can rebuild an index partition only if the index is not marked IN_PROGRESS or FAILED and the partition is not marked IN_PROGRESS .

You cannot rebuild a local index, but you can rebuild a partition of a local index ( ALTER INDEX . REBUILD PARTITION ).

For a local index on a hash partition or subpartition, the only parameter you can specify is TABLESPACE .

Use the PARTITION clause to rebuild one partition of an index. You can also use this clause to move an index partition to another tablespace or to change a create-time physical attribute.

The storage of partitioned database entities in tablespaces of different block sizes is subject to several restrictions. Please refer to Oracle Database VLDB and Partitioning Guide for a discussion of these restrictions.

Restriction on Rebuilding Partitions You cannot specify this clause for a local index on a composite-partitioned table. Instead, use the REBUILD SUBPARTITION clause.

Use the SUBPARTITION clause to rebuild one subpartition of an index. You can also use this clause to move an index subpartition to another tablespace. If you do not specify TABLESPACE , then the subpartition is rebuilt in the same tablespace.

The storage of partitioned database entities in tablespaces of different block sizes is subject to several restrictions. Please refer to Oracle Database VLDB and Partitioning Guide for a discussion of these restrictions.

Restrictions on Modifying Index Subpartitions The modification of index subpartitions is subject to the following restrictions:

The only parameters you can specify for a subpartition are TABLESPACE , ONLINE , and the parallel_clause .

You cannot rebuild the subpartition of a list partition.

Indicate whether the bytes of the index block are stored in reverse order:

REVERSE stores the bytes of the index block in reverse order and excludes the rowid when the index is rebuilt.

NOREVERSE stores the bytes of the index block without reversing the order when the index is rebuilt. Rebuilding a REVERSE index without the NOREVERSE keyword produces a rebuilt, reverse-keyed index.

Restrictions on Reverse Indexes Reverse indexes are subject to the following restrictions:

You cannot reverse a bitmap index or an index-organized table.

You cannot specify REVERSE or NOREVERSE for a partition or subpartition.

Use the parallel_clause to parallelize the rebuilding of the index.

Specify the tablespace where the rebuilt index, index partition, or index subpartition will be stored. The default is the default tablespace where the index or partition resided before you rebuilt it.

Specify COMPRESS to enable key compression, which eliminates repeated occurrence of key column values. Use integer to specify the prefix length (number of prefix columns to compress).

For unique indexes, the range of valid prefix length values is from 1 to the number of key columns minus 1. The default prefix length is the number of key columns minus 1.

For nonunique indexes, the range of valid prefix length values is from 1 to the number of key columns. The default prefix length is number of key columns.

Oracle Database compresses indexes that are nonunique or unique indexes of at least two columns. If you want to use compression for a partitioned index, then the index must have compression enabled at the index level.

Specify NOCOMPRESS to disable key compression. This is the default.

Restriction on Key Compression You cannot specify COMPRESS for a bitmap index.

Specify ONLINE to allow DML operations on the table or partition during rebuilding of the index.

Restrictions on Online Indexes Online indexes are subject to the following restrictions:

Parallel DML is not supported during online index building. If you specify ONLINE and subsequently issue parallel DML statements, then Oracle Database returns an error.

You cannot specify ONLINE for a bitmap join index or a cluster index.

For a nonunique secondary index on an index-organized table, the number of index key columns plus the number of primary key columns that are included in the logical rowid in the index-organized table cannot exceed 32. The logical rowid excludes columns that are part of the index key.

Specify whether the ALTER INDEX . REBUILD operation will be logged.

Refer to the logging_clause for a full description of this clause.

This clause is valid only for domain indexes in a top-level ALTER INDEX statement and only for domain and XMLIndex indexes when used in the rebuild_clause .

For a domain index, the PARAMETERS clause specifies the parameter string that is passed uninterpreted to the appropriate ODCI indextype routine.

For an XMLIndex index, the PARAMETERS clause specifies the parameter string that defines the XMLIndex implementation.

The maximum length of the parameter string is 1000 characters.


If you are altering or rebuilding an entire index, then the string must refer to index-level parameters. If you are rebuilding a partition of the index, then the string must refer to partition-level parameters.

If index is marked UNUSABLE , then modifying the parameters alone does not make it USABLE . You must also rebuild the UNUSABLE index to make it usable.

If you have installed Oracle Text, then you can rebuild your Oracle Text domain indexes using parameters specific to that product. For more information on those parameters, refer to Oracle Text Reference .

Restrictions on the PARAMETERS Clause The PARAMETERS clause is subject to the following restrictions:

You can specify this clause only for a domain or XMLIndex index.

You can modify index partitions only if index is not marked IN_PROGRESS or FAILED , no index partitions are marked IN_PROGRESS , and the partition being modified is not marked FAILED .

Oracle Database Data Cartridge Developer’s Guide for more information on indextype routines for domain indexes

Oracle XML DB Developer’s Guide for more information on XMLIndex , including the syntax and semantics of the XMLIndex_parameters_clause

CREATE INDEX for more information on domain indexes

This clause is valid only for domain indexes. Use this clause to recompile an invalid domain index explicitly. This clause is useful primarily when the underlying indextype has been altered to support system-managed domain indexes, so that the existing domain index has been marked INVALID . In this situation, this ALTER INDEX statement migrates the domain index from a user-managed domain index to a system-managed domain index.

The CREATE INDEXTYPE storage_table_clause and Oracle Database Data Cartridge Developer’s Guide for information on creating system-managed domain indexes

ENABLE applies only to a function-based index that has been disabled because a user-defined function used by the index was dropped or replaced. This clause enables such an index if these conditions are true:

The function is currently valid.

The signature of the current function matches the signature of the function when the index was created.

The function is currently marked as DETERMINISTIC .

Restriction on Enabling Function-based Indexes You cannot specify any other clauses of ALTER INDEX in the same statement with ENABLE .

DISABLE applies only to a function-based index. This clause lets you disable the use of a function-based index. You might want to do so, for example, while working on the body of the function. Afterward you can either rebuild the index or specify another ALTER INDEX statement with the ENABLE keyword.

Specify UNUSABLE to mark the index or index partition(s) or index subpartition(s) UNUSABLE . An unusable index must be rebuilt, or dropped and re-created, before it can be used. While one partition is marked UNUSABLE , the other partitions of the index are still valid. You can execute statements that require the index if the statements do not access the unusable partition. You can also split or rename the unusable partition before rebuilding it. Refer to CREATE INDEX . UNUSABLE for more information.

Restriction on Marking Indexes Unusable You cannot specify this clause for an index on a temporary table.

Use this clause to specify whether the index is visible or invisible to the optimizer. Refer to «VISIBLE | INVISIBLE» in CREATE INDEX for a full description of this clause.

Use this clause to rename an index. The new_index_name is a single identifier and does not include the schema name.

Restriction on Renaming Indexes For a domain index, neither index nor any partitions of index can be marked IN_PROGRESS or FAILED .

Specify COALESCE to instruct Oracle Database to merge the contents of index blocks where possible to free blocks for reuse.

Restrictions on Coalescing Index Blocks Coalescing of index blocks is subject to the following restrictions:

You cannot specify this clause for an index on a temporary table.

Do not specify this clause for the primary key index of an index-organized table. Instead use the COALESCE clause of ALTER TABLE .

Oracle Database Administrator’s Guide for more information on space management and coalescing indexes

COALESCE Clause for information on coalescing the space of an index-organized table

shrink_clause for an alternative method of compacting index segments

MONITORING USAGE | NOMONITORING USAGE

Use this clause to determine whether Oracle Database should monitor index use.

Specify MONITORING USAGE to begin monitoring the index. Oracle Database first clears existing information on index use, and then monitors the index for use until a subsequent ALTER INDEX . NOMONITORING USAGE statement is executed.

To terminate monitoring of the index, specify NOMONITORING USAGE .

To see whether the index has been used since this ALTER INDEX . NOMONITORING USAGE statement was issued, query the USED column of the V$OBJECT_USAGE dynamic performance view.

Oracle Database Reference for information on the data dictionary and dynamic performance views

UPDATE BLOCK REFERENCES Clause

The UPDATE BLOCK REFERENCES clause is valid only for normal and domain indexes on index-organized tables. Specify this clause to update all the stale guess data block addresses stored as part of the index row with the correct database address for the corresponding block identified by the primary key.

For a domain index, Oracle Database executes the ODCIIndexAlter routine with the alter_option parameter set to AlterIndexUpdBlockRefs . This routine enables the cartridge code to update the stale guess data block addresses in the index.

Restriction on UPDATE BLOCK REFERENCES You cannot combine this clause with any other clause of ALTER INDEX .

The partitioning clauses of the ALTER INDEX statement are valid only for partitioned indexes.

The storage of partitioned database entities in tablespaces of different block sizes is subject to several restrictions. Please refer to Oracle Database VLDB and Partitioning Guide for a discussion of these restrictions.

Restrictions on Modifying Index Partitions Modifying index partitions is subject to the following restrictions:

You cannot specify any of these clauses for an index on a temporary table.

You can combine several operations on the base index into one ALTER INDEX statement (except RENAME and REBUILD ), but you cannot combine partition operations with other partition operations or with operations on the base index.

Specify new values for the default attributes of a partitioned index.

Restriction on Modifying Partition Default Attributes The only attribute you can specify for a hash-partitioned global index or for an index on a hash-partitioned table is TABLESPACE .

TABLESPACE Specify the default tablespace for new partitions of an index or subpartitions of an index partition.

logging_clause Specify the default logging attribute of a partitioned index or an index partition.

Refer to logging_clause for a full description of this clause.

FOR PARTITION Use the FOR PARTITION clause to specify the default attributes for the subpartitions of a partition of a local index on a composite-partitioned table.

Restriction on FOR PARTITION You cannot specify FOR PARTITION for a list partition.

Use this clause to add a partition to a global hash-partitioned index. Oracle Database adds hash partitions and populates them with index entries rehashed from an existing hash partition of the index, as determined by the hash function. If you omit the partition name, then Oracle Database assigns a name of the form SYS_P n . If you omit the TABLESPACE clause, then Oracle Database places the partition in the tablespace specified for the index. If no tablespace is specified for the index, then Oracle Database places the partition in the default tablespace of the user, if one has been specified, or in the system default tablespace.

Use the modify_index_partition clause to modify the real physical attributes, logging attribute, or storage characteristics of index partition partition or its subpartitions. For a hash-partitioned global index, the only subclause of this clause you can specify is UNUSABLE .

COALESCE Specify this clause to merge the contents of index partition blocks where possible to free blocks for reuse.

UPDATE BLOCK REFERENCES The UPDATE BLOCK REFERENCES clause is valid only for normal indexes on index-organized tables. Use this clause to update all stale guess data block addresses stored in the secondary index partition.

Restrictions on UPDATE BLOCK REFERENCES This clause is subject to the following restrictions:

You cannot specify the physical_attributes_clause for an index on a hash-partitioned table.

You cannot specify UPDATE BLOCK REFERENCES with any other clause in ALTER INDEX .

If the index is a local index on a composite-partitioned table, then the changes you specify here will override any attributes specified earlier for the subpartitions of index, as well as establish default values of attributes for future subpartitions of that partition. To change the default attributes of the partition without overriding the attributes of subpartitions, use ALTER TABLE . MODIFY DEFAULT ATTRIBUTES OF PARTITION .

UNUSABLE Clause This clause has the same function for index partitions that it has for the index as a whole. Refer to «UNUSABLE».

key_compression This clause is relevant for composite-partitioned indexes. Use this clause to change the compression attribute for the partition and every subpartition in that partition. Oracle Database marks each index subpartition in the partition UNUSABLE and you must then rebuild these subpartitions. Key compression must already have been specified for the table before you can specify key compression for a partition. You can specify this clause only at the partition level. You cannot change the compression attribute for an individual subpartition.

You can use this clause for noncomposite index partitions. However, it is more efficient to use the rebuild_clause for noncomposite partitions, which lets you rebuild and set the compression attribute in one step.

Use the rename_index_partition clauses to rename index partition or subpartition to new_name .

Restrictions on Renaming Index Partitions Renaming index partitions is subject to the following restrictions:

You cannot rename the subpartition of a list partition.

For a partition of a domain index, index cannot be marked IN_PROGRESS or FAILED , none of the partitions can be marked IN_PROGRESS , and the partition you are renaming cannot be marked FAILED .

Use the drop_index_partition clause to remove a partition and the data in it from a partitioned global index. When you drop a partition of a global index, Oracle Database marks the next index partition UNUSABLE . You cannot drop the highest partition of a global index.

Use the split_index_partition clause to split a partition of a global range-partitioned index into two partitions, adding a new partition to the index. This clause is not valid for hash-partitioned global indexes. Instead, use the add_hash_index_partition clause.

Splitting a partition marked UNUSABLE results in two partitions, both marked UNUSABLE . You must rebuild the partitions before you can use them.

Splitting a usable partition results in two partitions populated with index data. Both new partitions are usable.

AT Clause Specify the new noninclusive upper bound for split_partition_1 . The value_list must evaluate to less than the presplit partition bound for partition_name_old and greater than the partition bound for the next lowest partition (if there is one).

INTO Clause Specify (optionally) the name and physical attributes of each of the two partitions resulting from the split.

This clause is valid only for hash-partitioned global indexes. Oracle Database reduces by one the number of index partitions. Oracle Database selects the partition to coalesce based on the requirements of the hash function. Use this clause if you want to distribute index entries of a selected partition into one of the remaining partitions and then remove the selected partition.

Use the modify_index_subpartition clause to mark UNUSABLE or allocate or deallocate storage for a subpartition of a local index on a composite-partitioned table. All other attributes of such a subpartition are inherited from partition-level default attributes.

Storing Index Blocks in Reverse Order: Example The following statement rebuilds index ord_customer_ix (created in «Creating an Index: Example») so that the bytes of the index block are stored in reverse order:

Rebuilding an Index in Parallel: Example The following statement causes the index to be rebuilt from the existing index by using parallel execution processes to scan the old and to build the new index:

Modifying Real Index Attributes: Example The following statement alters the oe.cust_lname_ix index so that future data blocks within this index use 5 initial transaction entries and an incremental extent of 100 kilobytes:

If the oe.cust_lname_ix index were partitioned, then this statement would also alter the default attributes of future partitions of the index. Partitions added in the future would then use 5 initial transaction entries and an incremental extent of 100K.

Enabling Parallel Queries: Example The following statement sets the parallel attributes for index upper_ix (created in «Creating a Function-Based Index: Example») so that scans on the index will be parallelized:

Renaming an Index: Example The following statement renames an index:

Marking an Index Unusable: Examples The following statements use the cost_ix index, which was created in «Creating a Range-Partitioned Global Index: Example». Partition p1 of that index was dropped in «Dropping an Index Partition: Example». The first statement marks index partition p2 as UNUSABLE :

The next statement marks the entire index cost_ix as UNUSABLE :

Rebuilding Unusable Index Partitions: Example The following statements rebuild partitions p2 and p3 of the cost_ix index, making the index once more usable: The rebuilding of partition p3 will not be logged:

Changing MAXEXTENTS: Example The following statement changes the maximum number of extents for partition p3 and changes the logging attribute:

Renaming an Index Partition: Example The following statement renames an index partition of the cost_ix index (created in «Creating a Range-Partitioned Global Index: Example»):

Splitting a Partition: Example The following statement splits partition p2 of index cost_ix (created in «Creating a Range-Partitioned Global Index: Example») into p2a and p2b :

Dropping an Index Partition: Example The following statement drops index partition p1 from the cost_ix index:

Modifying Default Attributes: Example The following statement alters the default attributes of local partitioned index prod_idx , which was created in «Creating an Index on a Hash-Partitioned Table: Example». Partitions added in the future will use 5 initial transaction entries and an incremental extent of 100K:

Использование индексов

Индекс создается командой create index и непосредственно недоступен пользователю. Индексы используются оптимизатором запросов для доступа к данным в базовых таблицах и представлениях.

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

Кластеризованный индекс — это индекс, у которого на листовом уровне находятся сами табличные данные. Поскольку индекс содержит физически упорядоченные данные, то становится ясно, почему для каждой отдельной таблицы можно создать только один кластеризованный индекс.


Именно в силу упорядоченности данных индекс предоставляет более эффективный доступ по сравнению со сканированием таблицы к требуемым данным.

Поскольку речь идет о поиске по ключу, а кластеризованный индекс у нас только один (и, соответственно, только один ключ), то мы можем создать дополнительно некластеризованные индексы. Хорошей новостью является то, что таких индексов мы можем создать сколько угодно. Реально, конечно, их количество ограничено, но возможность создать несколько сотен индексов на одну таблицу может удовлетворить любым потребностям.

Индексы, как правило, имеют структуру B-Tree — древовидная иерарархическая структура — которая позволяет, наряду со сканированием индекса (index scan), использовать прямой доступ к данным — поиск по индексу (index seek). Эта структура используется как для кластеризованных, так и некластеризованных индексов. Различием между ними, повторю, является то, что на листовом уровне дерева у кластеризованного индекса находятся сами табличные данные, а у некластеризованного — указатели на данные в таблице.

Если сказанное выше вам не вполне понятно, могу порекомендовать хорошую статью Гейла Шоу (Gail Shaw. Introduction to Indexes).

Кластерный индекс создается автоматически на первичном ключе (который и является ключом индекса), если вы не укажите обратного.

Возьмем для примера таблицу utV (база данных «Окраска»), содержащую всего три столбца — v_id (идентификатор баллончика — первичный ключ), v_name (название баллончика) и v_color (цвет краски в баллончике). Как уже говорилось, на первичном ключе автоматически создается кластеризованный индекс, есть он и у нашей таблицы.

Рис.1 Кластеризованный индекс

Выполним три следующих запроса и посмотрим на планы их выполнения.

select v_id from utv;
select * from utv;
select v_name from utv;

Рис.2 Сканирование кластеризованного индекса

Как вы можете убедиться, все эти запросы имеют одинаковый план выполнения, а именно – сканирование кластеризованного индекса, вне зависимости от того, данные всех столбцов нам нужны, или нет. Тут нет ничего удивительного, т.к. кроме кластеризованного индекса у нас ничего нет, а данные нужны из всех строк.

Давайте теперь заменим кластеризованный индекс некластеризованным, удалив сначала кластеризованный первичный ключ, и создав затем некластеризованный. Предварительно нам потребуется удалить внешний ключ из таблицы utB, который ссылается на первичный ключ таблицы utV:

alter table utB
drop constraint FK_utB_utV; —удаляем внешний ключ
alter table utV
drop constraint PK_utV; — удаляем первичный ключ (кластеризованный индекс)
go
alter table utV
add constraint PK_utVn primary key nonclustered (v_id asc);

Выполним теперь те же запросы, чтобы увидеть разницу:

Рис.3 Использование некластеризованного индекса

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

Рассмотрим теперь запросы на получение конкретной строки:

select v_ > select v_name from utv where v_ >

Для выполнения первого запроса оптимизатором теперь выбирается поиск по индексу (index seek) – наиболее эффективная операция, поскольку это прямой доступ к данным с использованием структуры B-Tree. План для второго запроса помимо поиска по индексу содержит еще две операции. Это связано с тем, что мы в запросе хотим получить имя баллончика, а не его ИД, а в индексе содержится только v_ >

Рис. 4 Поиск по индексу

Можно избежать лишней операции – поиска закладки, если включить в индекс требуемые запросом данные. Для этого мы удалим индекс PK_utVn и создадим вместо него новый.

alter table utV
drop constraint PK_utVn; — удаляем индекс
/* создаем уникальный индекс (не первичный ключ) с включенным столбцом */
create unique nonclustered index IX_utVi on utV(v_id asc) include(v_name);

Посмотрим план выполнения второго запроса.

Рис. 5 Поиск по индексу с включенным столбцом

Как видим, теперь план не отличается от плана выполнения первого запроса.

Следует отметить, что последний индекс не является составным, т.е. индексом, построенным по двум столбцам – <v_id, v_name>. Составной индекс для данного запроса использовался бы аналогичным образом, но есть одно важное отличие. При изменении данных, в частности, значений v_name составной индекс пришлось бы перестраивать, а индекс с включенным столбцом – нет, поскольку по включенному столбцу не выполняется физическое упорядочивание. Таким образом, накладные расходы на поддержку индексов в случае индекса с включенными столбцами будут ниже. Преимущества же составного индекса мы рассмотрим позже.

Рассмотрим, наконец, самый плохой вариант – отсутствие индексов.

drop index IX_utVi on utV; — удаляем индекс
go
select v_ > select v_name from utv where v_ >

Рис.6 Сканирование таблицы при отсутствии индексов

Как и ожидалось, оба запроса выполняются сканированием таблицы, хотя должна быть получена максимум одна строка. Причем поиск будет продолжен даже после нахождения искомой строки, поскольку система не имеет информации о том, что значение v_id уникально.

Для сравнения планов выполнения давайте вернем индекс по столбцу v_id

alter table utV
add constraint PK_utVn primary key nonclustered (v_id asc);
и выполним следующие запросы:
select v_ > select v_ ;

Рис.7 Выборка по столбцу без индекса

Эти запросы возвращают одно и то же, но в первом из них поисковым аргументом является столбец, имеющий индекс, а во втором – нет. Как и следовало ожидать, для первого запроса используется план с поиском по индексу, а для второго – сканирования таблицы. Не обращайте внимания на то, что стоимости планов выполнения запроса (cost) оцениваются оптимизатором одинаково. Причина в незначительном количестве данных, которые что в одном, что в другом случае, целиком будут находиться в оперативной памяти, и количество дисковых операций, которые оптимизируются сервером, будет эквивалентно. Это хороший пример того, что при оптимизации запросов нужно полагаться не на оценку стоимости, а читать план. В данном случае потенциальной потери производительности можно избежать, создав индекс на столбце v_name.

Давайте так и поступим, и выполним предыдущие запросы.

create index IX_utVname on utV(v_name);

Рис. 8 Игнорирование неуникального индекса

Неожиданно? Мы ожидали, что будет использован поиск по индексу, а затем поиск закладки для нахождения значения v_id. Однако оптимизатор не использовал индекс на столбце v_name. Почему?

Причина, как я думаю, заключается в том, что индекс на столбце v_name не является уникальным. Т.е. оптимизатор полагает, что значений, отвечающих предикату v_name= ‘Balloon # 15’ может быть несколько. Тогда для каждого такого значения потребуется поиск закладки. Поскольку данных в таблице немного, оптимизатор решает не оценивать план с использованием индекса на основе имеющейся статистики о распределении значений в столбце v_name, а пойти по простому пути, сэкономив на оценке плана. Давайте проверим это предположение, создав уникальный индекс, полагая, что одинаковых названий нет и быть не должно.

drop index IX_utVname on utV;
create unique index IX_utVname on utV(v_name);

Рис.9 Использование уникального индекса

Теперь результат согласуется с нашими ожиданиями.

Индексы Oracle

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

Применение индексов представляет собой компромисс между ускорением получения результатов запросов и замедлением обновлений и вставок данных. Первая часть этого компромисса — ускорение запросов — довольно очевидна: если поиск выполняется по отсортированному индексу вместо полного сканирования всей таблицы, то запрос проходит намного быстрее. Но всякий раз, когда вы обновляете, вставляете или удаляете строку таблицы с индексами, индексы также должны быть обновлены соответствующим образом. То есть такие операции на таблицах с индексами обходятся дороже. Вдобавок стоит помнить, что огромные таблицы будут иметь огромные индексы, и для их хранения потребуется диск большего объема.

Вообще говоря, если таблицы в основном используются для чтения (выборки) информации, как в хранилищах данных, то лучше иметь много индексов. Если же база данных относится к типу OLTP, с большим количеством вставок, обновлений и удалений, то лучше обойтись меньшим числом индексов.

Если только вам не нужно обращаться к большинству строк таблицы, индексированные запросы обеспечивают более быстрое получение результатов, чем запросы, не использующие индексы. Не существует ограничений на количество индексов, которые могут относиться к одной таблице Oracle, но, как упоминалось ранее, от их количества зависит производительность. Индекс полностью прозрачен для пользователя — т.е. оператор SQL пользователя не должен изменяться в результате создания индексов. Однако разработчикам приложений для построения эффективных запросов следует хорошо представлять себе, что такое индексы и как они работают.

Виды индексов Oracle Database

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

  • Уникальные и неуникальные индексы. Уникальные индексы основаны на уникальном столбце — обычно вроде номера карточки социального страхования сотрудника. Хотя уникальные индексы можно создавать явно, Oracle не рекомендует это делать. Вместо этого следует использовать уникальные ограничения. Когда накладывается ограничение уникальности на столбец таблицы, Oracle автоматически создает уникальные индексы по этим столбцам.
  • Первичные и вторичные индексы. Первичные индексы — это уникальные индексы в таблице, которые всегда должны иметь какое-то значение и не могут быть равны null. Вторичные индексы — это прочие индексы таблицы, которые могут и не быть уникальными.
  • Составные индексы. Составные индексы — это индексы, содержащие два или более столбца из одной и той же таблицы. Они также известны как сцепленные индексы (concatenated index). Составные индексы особенно полезны для обеспечения уникальности сочетания столбцов таблицы в тех случаях, когда нет уникального столбца, однозначно идентифицирующего строку.

Индексы и ключи

Часто можно встретить взаимозаменяемое употребление терминов “индекс” и “ключ”. Тем не менее, эти две сущности на самом деле отличаются друг от друга. Индекс — это физическая структура, хранящаяся в базе данных. Индекс можно создавать, изменять и уничтожать; в основном он служит для ускорения доступа к данным таблицы. С другой стороны, ключи — полностью логическая концепция. Ключи, с другой стороны, являются чисто логическим концепциями. Они представляют ограничения целостности, создаваемые для реализации бизнес-правил. Путаница между индексами и ключами обычно возникает потому, что база данных часто использует индекс для обеспечения ограничения целостности. Просто помните, что эти две вещи — не одно и то же.

Руководство по созданию индексов

Хотя хорошо известно, что индексы повышают производительность базы данных,следует знать, как их заставить работать должным образом. Добавление ненужных или неподходящих индексов к таблице может даже привести к снижению производительности. Ниже предоставлены некоторые рекомендации по созданию эффективных индексов в базе данных Oracle.

  • Индексация имеет смысл, если нужно обеспечить доступ одновременно не более чем к 4–5% данных таблицы. Альтернативой использованию индекса для доступа к данным строки является полное последовательное чтение таблицы от начала до конца, что называется полным сканированием таблицы. Полное сканирование таблицы больше подходит для запросов, которые требуют извлечения большего процента данных таблицы. Помните, что применение индексов для извлечения строк требует двух операций чтения: индекса и затем таблицы.
  • Избегайте создания индексов для сравнительно небольших таблиц. Для таких таблиц больше подходит полное сканирование. В случае маленьких таблиц нет необходимости в хранении данных и таблиц, и индексов.
  • Создавайте первичные ключи для всех таблиц. При назначении столбца в качестве первичного ключа Oracle автоматически создает индекс по этому столбцу.
  • Индексируйте столбцы, участвующие в многотабличных операциях соединения.
  • Индексируйте столбцы, которые часто используются в конструкциях WHERE.
  • Индексируйте столбцы, участвующие в операциях ORDERBY и GROUP BY или других операциях, таких как UNION и DISTINCT, включающих сортировку. Поскольку индексы уже отсортированы, объем работы по выполнению необходимой сортировки данных для упомянутых операций будет существенно сокращен.
  • Столбцы, состоящие из длинно-символьных строк, обычно плохие кандидаты на индексацию.
  • Столбцы, которые часто обновляются, в идеале не должны быть индексированы из-за связанных с этим накладных расходов.
  • Индексируйте таблицы только с высокой селективностью. То есть индексируйте таблицы, в которых мало строк имеют одинаковые значения.
  • Сохраняйте количество индексов небольшим.
  • Составные индексы могут понадобиться там, где одностолбцовые значения сами по себе не уникальны. В составных индексах первым столбцом ключа должен быть столбец с максимальной селективностью.

Всегда помните золотое правило индексации таблиц: индекс таблицы должен быть основан на типах запросов, которые будут выполняться над столбцами этой таблицы. На таблице можно создавать более одного индекса; например, можно создать индекс на столбце X, или столбце Y, или обоих сразу, а также один составной индекс на обоих столбцах. Принимая правильное решение относительно того, какие индексы следует создавать, подумайте о наиболее часто используемых типах запросов данных таблицы.

Схемы индексации Oracle

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

В реализации индексов на основе B-деревьев используется концепция сбалансированного (на что указывает буква “B” (balanced)) дерева поиска в качестве основы структуры индекса. В Oracle имеется собственный вариант B-дерева, именуемый “B*tree”. Это обычные индексы, создаваемые по умолчанию, когда вы применяете оператор CREATE INDEX. Термин “индекс B*tree” обычно не используется, когда речь идет об обычных индексах Oracle — они называются просто “индексами”.

Индексы на основе B-деревьев структурированы в форме обратного дерева, где блоки верхнего уровня называются блоками ветвей (branch blocks), а блоки нижнего уровня — листовыми блоками (leaf blocks). В иерархии узлов все узлы кроме вершины, или корневого узла, имеют родительский узел и могут иметь ноль или более дочерних узлов. Если глубина древовидной структуры, т.е. количество уровней, одинакова от каждого листового блока до корневого узла, то такое дерево называется сбалансированным, или B-деревом.

B-деревья автоматически поддерживают необходимый уровень индекса по размеру таблицы. B-деревья также гарантируют, что индексные блоки всегда будут заполнены не меньше, чем наполовину, и менее, чем на 100%. B-деревья допускают операции выборки, вставки и удаления с очень небольшим количеством операций ввода-вывода на один оператор. Большинство B-деревьев имеет всего три и менее уровней. При использовании B-дерева нужно читать только блоки B-дерева, так что количество операций ввода-вывода будет ограничено числом уровней B-дерева (скажем, тремя) плюс две операции ввода-вывода на выполнение обновления или удаления (одна для чтения и одна для записи). Для выполнения поиска по B-дереву понадобится всего три или менее обращений к диску.

Реализация B-дерева от Oracle — B*tree — всегда сохраняет дерево сбалансированным. Листовые блоки содержат по два элемента: индексированные значения столбца и соответствующий идентификатор ROWID для строки, которая содержит это значение столбца. ROWID — уникальный указатель Oracle, идентифицирующий физическое местоположение строки и обеспечивающий самый быстрый способ доступа к строке в базе данных Oracle. Сканирование индекса быстро дает ROWID строки, и отсюда можно быстро получить к ней доступ непосредственно. Если запрос нуждается лишь в значении индексированного столбца, то конечно, последний шаг исключается, поскольку извлекать дополнительные данные, кроме прочитанных из индекса, не потребуется.

Оценка размера индекса

Как и в случае таблиц, для оценки размера нового индекса можно использовать пакет DBMS_SPACE. Процедуре CREATE_INDEX_COST этого пакета потребуется передать оператор DDL, создающий индекс, в качестве атрибута, как показано в листинге ниже.

Обратите внимание на отличие между атрибутами, касающимися размера, в процедуре CREATE_INDEX_COST:

  • used_bytes показывает количество байт, которыми представлены данные индекса;
  • alloc_bytes показывает количество байт, которое займет индекс в табличном пространстве после его создания.

Совет. Для того чтобы пакет DBMS_SPACE мог адекватно оценить размеры индексов, таблица, на которой планируется создание нового индекса, должна существовать, а база данных должна иметь актуальную статистику по этой таблице.

Создание индекса

Индекс создается с помощью оператора CREATE INDEX, как показано ниже:

При создании индекса для большой таблицы, уже заполненной данными, статистику оптимизатора можно собрать во время создания таблицы, специфицировав опцию COMPUTE STATISTICS, как показано в следующем примере:

Если не специфицировать настройки хранения, база данных использует опции хранения по умолчанию табличного пространства, которое было указано при создании индекса.

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

Приведенные до сих пор примеры демонстрировали создание индексов на одиночных столбцах. Также можно создать составной индекс на таблице, специфицируя не-сколько столбцов в операторе CREATE INDEX, как показано в следующем примере:

Все примеры создания индекса до сих пор демонстрировали явное создание индекса на столбце таблицы. Тем не менее, есть и другой способ создания индекса на таблице,который заключается в простой спецификации ограничений целостности UNIQUE или PRIMARY KEY на этой таблице. Если поступить так, Oracle автоматически создает уникальный индекс по уникальному или первичному ключу. База данных создаст индекс автоматически, когда будет включено ограничение, и по умолчанию он получит имя соответствующего ограничения. Ниже приведено два примера, демонстрирующие ситуации, когда база данных создает автоматический индекс на столбцах таблицы.

В первом случае задается уникальное ограничение на двух столбцах: dept_name и location.

База данных автоматически создает уникальный индекс по этим двум столбцам,чтобы обеспечить соблюдение ограничения уникальности по имени dept_name_ukey.

Во втором примере показано, как при создании таблицы специфицировать ограничение первичного ключа на столбце.

Приведенный выше оператор CREATE TABLE включает ограничение первичного ключа, которое автоматически создает уникальный индекс на столбце empno.

Можно также указать, что база данных должна использовать существующий индекс для обеспечения нового ограничения, как показано в следующем примере:

В этом примере новый первичный ключ использует существующий индекс ind1, без создания нового индекса. Интересно то, что оператор CREATE INDEX можно специфицировать при создании ограничения уникальности или первичного ключа. В следующем примере создается первичный ключ на столбце emp_id:

Применение оператора CREATE INDEX в этом примере обеспечивает более тонкий контроль над созданием индекса для указанного ограничения первичного ключа.

Специальные типы индексов

Нормальный или типовой индекс, который создается в базе данных, называется индексом кучи (heap index), или неупорядоченным индексом. Oracle также предоставляет несколько специальных типов индексов для специфических нужд. Сейчас рассмотрим основные типы индексов.

Битовые индексы

Битовые индексы (bitmap indexes) используют битовые карты для указания значения индексированного столбца. Это идеальный индекс для столбца с низкой кардинальностью при большом размере таблицы. Эти индексы обычно не годятся для таблиц с интенсивным обновлением, но хорошо подходят для приложений хранилищ данных.

Битовые индексы состоят из битового потока (единиц и нулей) для каждого столбца индекса. Битовые индексы очень компактны по сравнению с нормальными индексами на основе B-деревьев. В табл. 7.2 дано сравнение индексов B-деревьев с битовыми индексами.

Индексы B-деревьев Битовые индексы
Хороши для данных с высокой кардинальностью Хороши для данных с низкой кардинальностью
Хороши для баз данных OLTP Хороши для приложений хранилищ данных
Занимают много места Используют относительно мало места
Легко обновляются Трудно обновляются

Для создания битового индекса используется оператор CREATE INDEX с добавочным ключевым словом BITMAP:

Иногда можно наблюдать значительное повышение производительности при замене обычных индексов B*tree на битовые в некоторых очень крупных таблицах. Однако каждый элемент битового индекса покрывает огромное количество строк в таблице, так что когда данные обновляются, вставляются или удаляются из таблицы, то необходимые обновления битового индекса очень велики, и сам индекс может существенно увеличиться в размере. Единственный способ обойти это увеличение размера индекса с последующим падением производительности заключается в регулярной его перестройке. Вы можете сделать вывод, что битовый индекс — не слишком разумная альтернатива для таблиц, подвергающихся большому количеству вставок, удалений и обновлений.


Индексы с реверсированным ключом

Индексы с реверсированным ключом — это, по сути, то же самое, что и индексы B-деревьев, за исключением того, что байты данных ключевого столбца при индексации меняют порядок на противоположный. Порядок столбцов остается нетронутым;меняется только порядок байтов. Самое большое преимущество применения индексов с реверсированным ключом состоит в том, что они исключают неприятные последствия упорядоченной вставки значений в индекс. Вот как создается индекс с реверсированным ключом:

При использовании индекса с реверсированным ключом база данных не сохраняет ключи индекса друг за другом в лексикографическом порядке. Таким образом, когда в запросе присутствует предикат неравенства, ответ получается медленнее, поскольку база данных вынуждена выполнять полное сканирование таблицы. При индексе с реверсированным ключом база данных не может запустить запрос по диапазону ключа индекса.

Индексы со сжатым ключом

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

Приведенный выше оператор сжимает все дублированные вхождения индексированного ключа в листовом блоке индекса (на уровне 1).

Индексы на основе функций

Индексы на основе функций предварительно вычисляют значения функций по заданному столбцу и сохраняют результат в индексе. Когда конструкция where содержит вызовы функций, то основанные на функциях индексы являются идеальным способом индексирования столбца.

Ниже показано, как создать индекс на основе функции LOWER:

Этот оператор CREATE INDEX создаст индекс по столбцу l_name, хранящему фамилии сотрудников в верхнем регистре. Однако этот индекс будет основан на функции,поскольку база данных создает его по столбцу l_name, применив к нему предварительно функцию LOWER для преобразования его значения в нижний регистр.

Секционированные индексы

Секционированные индексы используются для индексации секционированных таблиц. Oracle предлагает два типа индексов для таких таблиц: локальные и глобальные.

Существенное различие между ними заключается в том, что локальные индексы основаны на разделах таблицы, по которой они созданы. Если таблица секционирована на 12 разделов по диапазонам дат, то индексы также будут распределены по тем же 12 разделам. Другими словами, между разделами индексов и разделами таблиц существует соответствие “один к одному”. Такого соответствия нет между глобальными индексами и разделами таблицы, потому что глобальные индексы секционируются независимо от базовых таблиц.

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

Глобальные индексы

Глобальные индексы на секционированных таблицах могут быть как секционированными, так и несекционированными. Глобальные несекционированные индексы подобны обычным индексам Oracle для несекционированных таблиц. Для создания таких индексов применяется обычный синтаксис CREATE INDEX.

Ниже приведен пример глобального индекса на таблице ticket_sales:

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

Давайте в качестве примера воспользуемся таблицей ticket_sales, чтобы разобраться, почему это так. Предположим, что вы ежеквартально уничтожаете самый старый раздел, чтобы освободить место для нового раздела, в который поступят данные за новый квартал. Когда уничтожается раздел, относящийся к таблице ticket_sales,глобальные индексы могут стать недействительными, потому что часть данных, на которые они указывают, перестают существовать. Чтобы предотвратить такое объявление недействительным индекса из-за уничтожения раздела, необходимо использовать опцию UPDATE GLOBAL INDEXES вместе с оператором DROP PARTITION:

На заметку! Если не включить оператор UPDATE GLOBAL INDEXES, то все глобальные индексы станут недействительными. Опцию UPDATA GLOBAL INDEXES можно также использовать при добавлении, объединении, обмене, слиянии, перемещении, разделении или усечении секционированных таблиц. Разумеется, с помощью ALTER INDEX. REBUILD можно перестраивать любого индекса, который становится недействительным, но эта опция также требует дополнительных затрат времени и обслуживания.

При небольшом количестве листовых блоков индекса, что приводит к высокой конкуренции, Oracle рекомендует использовать глобальные индексы с хеш-секционированием. Синтаксис для создания хеш-секционированного глобального индекса подобен тому, что применяется для хеш-секционированной таблицы. Например, следующий оператор создает хеш-секционированный глобальный индекс:

Локальные индексы

Локально секционированные индексы, в отличие от глобально секционированных индексов, имеют отношение “один к одному” с разделами таблицы. Локально секционированные индексы можно создавать в соответствии с разделами и даже подразделами. База данных конструирует индекс таким образом, чтобы он был секционирован так же,как и его таблица. При каждой модификации раздела таблицы база автоматически сопровождает это соответствующей модификацией раздела индекса. Это, наверное, самое большое преимущество использования локально секционированных индексов — Oracle автоматически перестраивает их всегда, когда уничтожается раздел или над ним выполняется какая-то другая операция DDL.

Ниже приведен простой пример создания локально секционированного индекса на секционированной таблице:

Совет. С помощью нового инструмента SQL Access Advisor можно получать рекомендации относительно того, какие индексы нужно создать. SQL Access Advisor также сообщит о том, какие индексы не используются и потому являются кандидатами на удаление.

Невидимые индексы

По умолчанию оптимизатор “видит” все индексы. Тем не менее, можно создать невидимый индекс, который оптимизатор не обнаруживает и не принимает во внимание при создании плана выполнения оператора. Невидимый индекс можно применять в качестве временного индекса для определенных операций или его тестирования перед тем, как сделать его “официальным”. Вдобавок, иногда объявление индекса невидимым можно использовать в качестве альтернативы уничтожению индекса или объявлению его недоступным. Сделать индекс невидимым можно временно, чтобы протестировать эффект от его уничтожения.

База данных поддерживает невидимый индекс точно так же, как и нормальный (видимый) индекс. После объявления индекса невидимым, его и все прочие невидимые индексы можно сделать вновь видимым для оптимизатора, установив значение параметра optimizer_use_invisible_indexes равным TRUE на уровне сеанса или всей системы. Значением этого параметра по умолчанию является FALSE, а это означает, что оптимизатор по умолчанию не может использовать невидимые индексы.

Создание невидимого индекса

Чтобы сделать индекс невидимым, к оператору CREATE INDEX нужно добавить конструкцию INVISIBLE, как показано ниже:

Приведенный выше оператор создает невидимый индекс test_idx по столбцу tname таблицы test.

Превращение индекса в невидимый

В дополнение к созданию невидимого индекса, с помощью команды ALTER INDEX можно превратить существующий индекс в невидимый:

Чтобы сделать невидимый индекс вновь видимым, используйте следующий оператор:

Приведенные ниже запрос к представлению DBA_INDEXES показывает состояние видимости индекса:

Мониторинг использования индекса

Oracle предлагает инструменты EXPLAIN PLAN и SQL Trace, которые помогают увидеть путь, проходимый запросом перед его выполнением. Вывод команды EXPLAIN PLAN и результаты SQL Trace позволяют увидеть путь выполнения запроса и определить, использует ли он индексы.

Oracle также предлагает более простой способ слежения за индексами в базе данных. Если вы сомневаетесь в использовании определенного индекса, можете попросить Oracle выполнить мониторинг его применения. Таким образом, если индекс окажется избыточным, его можно уничтожить и сэкономить место в хранилище, а также снизить накладные расходы на операции DML.

Опишем, что потребуется сделать для отслеживания индекса в базе данных. Предположим, что вы пытаетесь узнать, используется ли индекс p_key_sales в определенных запросах к таблице sales. Обеспечьте репрезентативный промежуток времени для оценки использования индекса. Для базы данных OLTP этот промежуток может быть относительно коротким. Для хранилища данных может понадобиться запустить тестовый мониторинг на несколько дней, чтобы точно проверить, как используется индекс.

Чтобы запустить мониторинг использования индекса, войдите в базу как владелец индекса p_key_sales и запустите следующую команду:

Теперь запустите какие-нибудь запросы к таблице sales. Завершите мониторинг,применив следующую команду:

После этого можно запросить представление словаря данных V$OBJECT_USAGE для определения того, использовался ли индекс p_key_sales. Следующий результат подтверждает использование индекса:

В приведенном выводе Oracle выводит значение YES в столбце USED, указывая на то,что интересующий индекс использовался базой данных. Если индекс был проигнорирован во время мониторинга, столбец содержал бы значение NO. Причина, по которой нельзя узнать количество случаев использования индекса, связана с тем, что база данных выполняет мониторинг его использования только на фазе разбора (parsing); если бы разбор производился при каждом выполнении, пострадала бы производительность.

Обслуживание индексов

Данные индекса постоянно изменяются из-за DML-действий, связанных с его таблицей. Индексы часто становятся слишком большими, если происходит много удалений строк, потому что пространство, занятое удаленными значениями, автоматически повторно индексом не используется. За счет периодического применения команды REBUILD можно реорганизовать индексы и сделать их более компактными, а потому и более эффективными. Команда REBUILD также служит для изменения параметров хранения, которые устанавливаются во время начального создания индекса. Вот пример:

Перестройка индексов лучше уничтожения и воссоздания неудачного индекса, потому что при этой операции пользователи продолжают иметь доступ к индексу в процессе его перестройки. Однако индексы в процессе перестройки накладывают много ограничений на действия пользователя. Еще более эффективный способ перестройки индексов состоит в том, чтобы сделать это в оперативном (online) режиме, как показано в следующем примере. Во время оперативной перестройки индекса разрешено применение всех операций DML, но не операций DDL.

Оперативную перестройку индекса можно ускорить за счет добавления к показанному выше оператору ALTER INDEX конструкции ONLINE NOLOGGING. После добавления этой конструкции база данных не будет генерировать данные повторного выполнения для операции перестройки индекса.

IT Записки

Индексы в SQL Server.

Индексы — это первое, что необходимо хорошо понимать в работе SQL Server, но странным образом базовые вопросы не слишком часто задаются на форумах и получают не так уж много ответов.
Роб Шелдон отвечает на эти, вызывающие смущение в профессиональных кругах, вопросы об индексах в SQL Server: одни из них мы просто стесняемся задать, а прежде чем задать другие сначала подумаем дважды.

Используемая терминология:

index индекс
heap куча
table таблица
view представление
B-tree сбалансированное дерево
clustered index кластеризованный индекс
nonclustered index некластеризованный индекс
composite index составной индекс
covering index покрывающий индекс
primary key constraint ограничение на первичный ключ
unique constraint ограничение на уникальность значений
query запрос
query engine подсистема запросов
database база данных
database engine подсистема хранения данных
fill factor коэффициент заполнения индекса
surrogate primary key суррогатный первичный ключ
query optimizer оптимизатор запросов
index selectivity избирательность индекса
filtered index фильтруемый индекс
execution plan план выполнения

Основы индексов в SQL Server.

Одним из важнейших путей достижения высокой производительности SQL Server является использование индексов. Индекс ускоряет процесс запроса, предоставляя быстрый доступ к строкам данных в таблице, аналогично тому, как указатель в книге помогает вам быстро найти необходимую информацию. В этой статье я приведу краткий обзор индексов в SQL Server и объясню как они организованы в базе данных и как они помогают ускорению выполнения запросов к базе данных.

Структура индекса

Индексы создаются для столбцов таблиц и представлений. Индексы предоставляют путь для быстрого поиска данных на основе значений в этих столбцах. Например, если вы создадите индекс по первичному ключу, а затем будете искать строку с данными, используя значения первичного ключа, то SQL Serverсначала найдет значение индекса, а затем использует индекс для быстрого нахождения всей строки с данными. Без индекса будет выполнен полный просмотр (сканирование) всех строк таблицы, что может оказать значительное влияние на производительность.
Вы можете создать индекс на большинстве столбцов таблицы или представления. Исключением, преимущественно, являются столбцы с типами данных для хранения больших объектов (LOB), таких какimage, text или varchar(max). Вы также можете создать индексы на столбцах, предназначенных для хранения данных в формате XML, но эти индексы устроены немного иначе, чем стандартные и их рассмотрение выходит за рамки данной статьи. Также в статье не рассматриваются columnstore индексы. Вместо этого я фокусируюсь на тех индексах, которые наиболее часто применяются в базах данных SQL Server.
Индекс состоит из набора страниц, узлов индекса, которые организованы в виде древовидной структуры — сбалансированного дерева. Эта структура является иерархической по своей природе и начинается с корневого узла на вершине иерархии и конечных узлов, листьев, в нижней части, как показано на рисунке:

Когда вы формируете запрос на индексированный столбец, подсистема запросов начинает идти сверху от корневого узла и постепенно двигается вниз через промежуточные узлы, при этом каждый слой промежуточного уровня содержит более детальную информацию о данных. Подсистема запросов продолжает двигаться по узлам индекса до тех пор, пока не достигнет нижнего уровня с листьями индекса. К примеру, если вы ищете значение 123 в индексированном столбе, то подсистема запросов сначала на корневом уровне определит страницу на первом промежуточном (intermediate) уровне. В данном случае первой страница указывает на значение от 1 до 100, а вторая от 101 до 200, таким образом подсистема запросов обратится ко второй странице этого промежуточного уровня. Далее будет выяснено, что следует обратиться к третьей странице следующего промежуточного уровня. Отсюда подсистема запросов прочитает на нижнем уровне значение самого индекса. Листья индекса могут содержать как сами данные таблицы, так и просто указатель на строки с данными в таблице, в зависимости от типа индекса: кластеризованный индекс или некластеризованный.

Кластеризованный индекс

Кластеризованный индекс хранит реальные строки данных в листьях индекса. Возвращаясь к предыдущему примеру, это означает что строка данных, связанная со значение ключа, равного 123 будет храниться в самом индексе. Важной характеристикой кластеризованного индекса является то, что все значения отсортированы в определенном порядке либо возрастания, либо убывания. Таким образом, таблица или представление может иметь только один кластеризованный индекс. В дополнение следует отметить, что данные в таблице хранятся в отсортированном виде только в случае если создан кластеризованный индекс у этой таблицы.
Таблица не имеющая кластеризованного индекса называется кучей.

Некластеризованный индекс

В отличие от кластеризованного индекса, листья некластеризованного индекса содержат только те столбцы (ключевые), по которым определен данный индекс, а также содержит указатель на строки с реальными данными в таблице. Это означает, что системе подзапросов необходима дополнительная операция для обнаружения и получения требуемых данных. Содержание указателя на данные зависит от способа хранения данных: кластеризованная таблица или куча. Если указатель ссылается на кластеризованную таблицу, то он ведет к кластеризованному индексу, используя который можно найти реальные данные. Если указатель ссылается на кучу, то он ведет к конкретному идентификатору строки с данными. Некластеризованные индексы не могут быть отсортированы в отличие от кластеризованных, однако вы можете создать более одного некластеризованного индекса на таблице или представлении, вплоть до 999. Это не означает, что вы должны создавать как можно больше индексов. Индексы могут как улучшить, так и ухудшить производительность системы. В дополнение к возможности создать несколько некластеризованных индексов, вы можете также включить дополнительные столбцы (included column) в свой индекс: на листьях индекса будет храниться не только значение самих индексированных столбцов, но и значения этих не индексированных дополнительных столбцов. Этот подход позволит вам обойти некоторые ограничения, наложенные на индекс. К примеру, вы можете включить неидексируемый столбец или обойти ограничение на длину индекса (900 байт в большинстве случаев).

Типы индексов

В дополнение к тому, что индекс может быть либо кластеризованным, либо некластеризованным, возможно его дополнительно сконфигурировать как составной индекс, уникальный индекс или покрывающий индекс.

Составной индекс

Такой индекс может содержать более одного столбца. Вы можете включить до 16 столбцов в индекс, но их общая длина ограничена 900 байтами. Как кластеризованный, так и некластеризованный индексы могут быть составными.

Уникальный индекс

Такой индекс обеспечивает уникальность каждого значения в индексируемом столбце. Если индекс составной, то уникальность распространяется на все столбцы индекса, но не на каждый отдельный столбец. К примеру, если вы создадите уникальных индекс на столбцах ИМЯ и ФАМИЛИЯ, то полное имя должно быть уникально, но отдельно возможны дубли в имени или фамилии.
Уникальный индекс автоматически создается когда вы определяете ограничения столбца: первичный ключ или ограничение на уникальность значений:

  • Первичный ключ
    Когда вы определяете ограничение первичного ключа на один или несколько столбцов, тогда SQL Server автоматически создаёт уникальный кластеризованный индекс, если кластеризованный индекс не был создан ранее (в этом случае создается уникальный некластеризованный индекс по первичному ключу)
  • Уникальность значений
    Когда вы определяете ограничение на уникальность значений, тогда SQL Server автоматически создает уникальный некластеризованный индекс. Вы можете указать, чтобы был создан уникальный кластеризованный индекс, если кластеризованного индекса до сих пор не было создано на таблице
Покрывающий индекс

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

Проектирование индексов

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

База данных

Как было отмечено ранее индексы могут улучить производительность системы, т.к. они обеспечивают подсистему запросов быстрым путем для нахождения данных. Однако, вы должны также принять во внимание то, как часто вы собираетесь вставлять, обновлять или удалять данные. Когда вы изменяете данные, то индексы должны также быть изменены, чтобы отразить соответствующие действия над данными, что может значительно снизить производительность системы. Рассмотрим следующие рекомендации при планировании стратегии индексирования:

  • Для таблиц которые часто обновляются используйте как можно меньше индексов.
  • Если таблица содержит большое количество данных, но их изменения незначительны, тогда используйте столько индексов, сколько необходимо для улучшение производительности ваших запросов. Однако хорошо подумайте перед использованием индексов на небольших таблицах, т.к. возможно использование поиска по индексу может занять больше времени, нежели простое сканирование всех строк.
  • Для кластеризованных индексов старайтесь использовать настолько короткие поля насколько это возможно. Наилучшим образом будет применение кластеризованного индекса на столбцах с уникальными значениями и не позволяющими использовать NULL. Вот почему первичный ключ часто используется как кластеризованный индекс.
  • Уникальность значений в столбце влияет на производительность индекса. В общем случае, чем больше у вас дубликатов в столбце, тем хуже работает индекс. С другой стороны, чем больше уникальных значения, тем выше работоспособность индекса. Когда возможно используйте уникальный индекс.
  • Для составного индекса возьмите во внимание порядок столбцов в индексе. Столбцы, которые используются в выражениях WHERE (к примеру, WHERE FirstName = ‘Charlie’) должны быть в индексе первыми. Последующие столбцы должны быть перечислены с учетом уникальности их значений (столбцы с самым высоким количеством уникальных значений идут первыми).
  • Также можно указать индекс на вычисляемых столбцах, если они соответствуют некоторым требованиям. К примеру, выражение которые используются для получения значения столбца, должны быть детерминистическими (всегда возвращать один и тот же результат для заданного набора входных параметров).
Запросы к базе данных

Другое соображение которое следует учитывать при проектировании индексов это какие запросы выполняются к базе данных. Как было указано ранее, вы должны учитывать как часто изменяются данные. Дополнительно следует использовать следующие принципы:

  • Старайтесь вставлять или модифицировать в одном запросе как можно больше строк, а не делать это в несколько одиночных запросов.
  • Создайте некластеризованный индекс на столбцах которые часто используются в ваших запросах в качестве условий поиска в WHERE и соединения в JOIN.
  • Рассмотрите возможность индексирования столбцов, использующихся в запросах поиска строк на точное соответствие значений.

Почему таблица не может иметь два кластеризованных индекса?

Хотите короткий ответ? Кластеризованный индекс – это и есть таблица. Когда вы создаете кластеризованный индекс у таблицы, подсистема хранения данных сортирует все строки в таблице в порядке возрастания или убывания, согласно определению индекса. Кластеризованный индекс это не отдельная сущность как другие индексы, а механизм сортировки данных в таблице и облегчения быстрого доступа к строкам с данными.
Представим, что у вас есть таблица, содержащая историю операций по продажам. Таблица Sales включает в себя такую информация как идентификатор заказа, позицию товара в заказе, номер товара, количество товара, номер и дату заказа и т.д. Вы создаёте кластеризованный индекс по столбцам OrderIDи LineID, с сортировкой в порядке возрастания, как показано в следующем T-SQL коде:

Когда вы запустите этот скрипт все строки в таблице будут физически отсортированы сначала по столбцу OrderID, а затем по LineID, но сами данные останутся в единственном логическом блоке, в таблице. По этой причине вы не можете создать два кластеризованных индекса. Может быть только одна таблица с одними данными и эта таблица может быть отсортирована только один раз в определенном порядке.

Если кластеризованная таблица даёт множество преимуществ, то зачем использовать кучу?

Вы правы. Кластеризованые таблицы отличны и большинство ваших запросов будут лучше выполнятся к таблицам, имеющим кластеризованный индекс. Но в некоторых случаях вы возможно захотите оставить таблицы в их естественном первозданном состоянии, т.е. в виде кучи, и создать лишь некластеризованные индексы для поддержания работоспособности ваших запросов.
Куча, как вы помните, хранит данные в случайном порядке. Обычно подсистема хранения данных добавляет в таблицу данные в той последовательности в которой они вставляются, однако подсистема также любит перемещать строки с целью более эффективного хранения. В результате у вас нет ни единого шанса предсказать в каком порядке будут храниться данные.
Если подсистема запросов должна найти данные без преимуществ некластеризованного индекса, то она сделает полное сканирование таблицы для нахождения нужных ей строк. На очень маленьких таблицах это обычно не проблема, но как только куча растет в своих размерах производительность быстро падает. Конечно, некластеризованный индекс может помочь, используя указатель на файл, страницу и строку где хранятся необходимые данные – обычно это намного лучшая альтернатива сканированию таблицы. Но даже в этом случае трудно сравнивать с преимуществами кластеризованного индекса при рассмотрении производительности запросов.
Однако куча может помочь улучшить производительность в определенных ситуациях. Рассмотрим таблицу с большим количеством вставок, но редкими обновлениями или удалением данных. К примеру, таблица, хранящая лог, преимущественно используется для вставки значений до тех пор пока не будет архивирована. В куче вы не увидите разбиением страниц и фрагментацию данных, как это случается с кластеризованным индексом, потому что строки просто добавляются в конец кучи. Слишком большое разделение страниц может иметь значительное влияние на производительность и в не самом хорошем смысле. В общем, куча позволяет производить вставку данных относительно безболезненно и вам не надо будет бороться с накладными расходами на хранение и обслуживание, как это бывает в случае кластеризованного индекса.
Но отсутствие обновления и удаления данных не должны рассматриваться как единственная причина. Способ выборки данных также является важным фактором. К примеру, вы не должны использовать кучу, если часто выполняете запросы диапазонов данных или запрашиваемые данные часто должны быть сортированы или сгруппированы.
Всё это означает, что вы должны рассматривать возможность использования кучи только когда работаете с особо-маленькими таблицами или всё ваше взаимодействие с таблицей ограничено вставкой данных и ваши запросы чрезвычайно просты (и вы все-равно используете некластеризованные индексы). В противном случае держитесь хорошо спроектированного кластеризованного индекса, к примеру определенного на простом возрастающем ключевом поле, как широко применяемый столбец с IDENTITY.

Как изменить установленное по умолчанию значение коэффициента заполнения индекса?

Изменение установленного по умолчанию коэффициента заполнения индекса это одно дело. Понимание того как установленный по умолчанию коэффициент работает это другое. Но сначала пару шагов назад. Коэффициент заполнения индекса определяет количество пространства на странице для хранения индекса на нижнем уровне (уровень листьев) перед тем как начать заполнять новую страницу. К примеру, если коэффициент выставлен в значение 90, то при росте индекс займет на странице 90%, а затем перейдет на следующую страницу.
По умолчанию, значение коэффициента заполнения индекса в SQL Server равно 0, что равнозначно значению 100. В результате все новые индексы автоматически наследуют эту настройки, если вы специально в коде не укажете отличное от стандартного для системы значения или измените поведение по умолчанию. Вы можете воспользоваться SQL Server Management Studio для корректировки установленного по умолчанию значения или запустить системную сохраненную процедуру sp_configure. К примеру, следующий набор T-SQL команд устанавливает значение коэффициента равное 90 (предварительно необходимо переключится в режим продвинутых настроек):

После изменения значения коэффициента заполнения индекса необходимо перезагрузить сервис SQL Server. Теперь вы можете проверить установленное значение, запустив процедуру sp_configure без указанного второго аргумента:

Данная команда должна вернуть значение равное 90. В результате все вновь создаваемые индексы будут использовать это значение. Вы можете проверить это, создав индекс и запросить значение коэффициента заполнения:

В данном примере мы создали некластеризованный индекс в таблице Person в базе данныхAdventureWorks2012. После создания индекса мы можем получить значение коэффициента заполнения из системной таблиц sys.indexes. Запрос должен вернуть 90.
Однако, представим, что мы удалили индекс и снова создали его, но теперь указали конкретное значение коэффициента заполнения:


В этот раз мы добавили инструкцию WITH и опцию fillfactor для нашей операции создания индексаCREATE INDEX и указали значение 80. Оператор SELECT теперь возвращает соответствующее значение.
До сих пор всё было довольно-таки прямолинейно. Где вы реально можете погореть во всём этом процессе, так это когда вы создаёте индекс, использующий значение коэффициента по умолчанию, подразумевая, что вы знаете это значение. К примеру, кто-то неумело ковыряется в настройках сервера и он настолько упорот, что ставит значение коэффициента заполнения индекса равное 20. Тем временем вы продолжаете создавать индексы, предполагая значение по умолчанию равное 0. К сожалению, у вас нет способа узнать значение коэффициента до тех пор как вы не создадите индекс, а затем проверите значение, как мы делали в наших примерах. В противном случае, вам придётся ждать момента когда производительность запросов настолько упадёт, что вы начнёте что-то подозревать.
Другая проблема о которой вам стоит помнить это перестроение индексов. Как и при создании индекса вы можете конкретизировать значение коэффициента заполнения индекса, когда его перестраиваете. Однако, в отличие от команды создания индекса, перестройка не использует серверные настройки по умолчанию, несмотря на то что так может показаться. Даже больше, если вы конкретно не укажете значение коэффициента заполнения индекса, то SQL Server будет использовать то значение коэффициента, с которым этот индекс существовал до его перестройки. К примеру, следующая операцияALTER INDEX перестраивает только что созданный нами индекс:

Когда мы проверим значение коэффициента заполнения мы получим значение равное 80, потому что именно его мы указали при последнем создании индекса. Значение по умолчанию не учитывается.
Как вы видите изменить значение коэффициента заполнения индекса не такое уж сложно дело. Намного сложнее знать текущее значение и понимать когда оно применяется. Если вы всегда конкретно указывается коэффициент при создании и перестройки индексов, то вы всегда знаете конкретный результат. Разве что вам приходится заботиться о том, чтобы кто-то другой снова не напортачил в настройках сервера, вызвав перестройку всех индексов со смехотворно низким значением коэффициента заполнения индекса.

Можно ли создать кластеризованный индекс на столбце, содержащем дубликаты?

И да, и нет. Да вы можете создать кластеризованный индекс на ключевом столбце, содержащем дубликаты значений. Нет, значение ключевого столбца не смогут остаться в состоянии не уникальности. Позвольте объяснить. Если вы создаёте неуникальный кластерный индекс (non-unique clustered index) на столбце, то подсистема хранения данных добавляет к дублирующему значению целочисленное значение (uniquifier), чтобы удостовериться в уникальности и, соответственно, обеспечить возможность идентифицировать каждую строку в кластеризованной таблице.
К примеру, вы можете решить создать в таблице с данными о клиентах кластеризованный индекс по столбцу LastName, хранящим фамилию. Столбец содержит такие значения как Franklin, Hancock, Washington и Smith. Затем вы вставляете значения Adams, Hancock, Smith и снова Smith. Но значение ключевого столбца обязательно должны быть уникальны, поэтому подсистема хранения данных изменит значение дубликатов таким образом, что они будут выглядеть примерно так: Adams, Franklin, Hancock, Hancock1234, Washington, Smith, Smith4567 и Smith5678.
На первый взгляд такой подход кажется нормальным, но целочисленное значение увеличивает размер ключа, что может стать проблемой при большом количестве дубликатов, а эти значения станут основой некластеризованного индекса или ссылкой внешнего ключа. По этим причинам вы всегда должны стараться создавать уникальный кластеризованный (unique clustered indexes) при любой возможности. Если это невозможно, то по крайней мере постарайтесь использовать столбцы с очень высоким содержание уникальных значений.

Как хранится таблица, если не был создан кластеризованный индекс?

SQL Server поддерживает два типа таблиц: кластеризованные таблицы, имеющие кластеризованный индекс и таблицы-кучи или просто кучи. В отличие от кластеризованных таблиц данные в куче не сортированы никоим образом. По сути это и есть нагромождение (куча) данных. Если вы добавите строку к такой таблице, то подсистема хранения данных просто добавит её к концу страницы. Когда страница заполнится данными, то они будут добавлены на новую страницу. В большинстве случаев, вы захотите создать кластеризованный индекс на таблице, чтобы получить преимущества от возможности сортировки и ускорения запросов (попробуйте представить себе найти телефонный номер в адресной книге, не отсортированной по какому-либо принципу). Однако, если вы решите не создавать кластеризованный индекс, то вы по-прежнему можете создать у кучи некластеризованный индекс. В этом случае каждая строка индекса будет иметь указатель на строку кучи. Указатель включает в себя идентификатор файла, номер страницы и номер строки с данными.

Какая взаимосвязь между ограничениями на уникальность значения и первичным ключом с индексами таблицы?

Первичный ключ и и ограничение уникальности обеспечивают, что значения в столбце будут уникальны. Вы можете создать только один первичный ключ у таблицы и он не может содержать значения NULL. Вы можете создать у таблицы несколько ограничений на уникальность значения и каждый из них может иметь единственную запись с NULL.
Когда вы создаете первичный ключ, подсистема хранения данных так же создает уникальный кластеризованный индекс, в случае если уже кластеризованный индекс не был создан. Однако, вы можете переопределить установленное по умолчанию поведение и тогда будет создан некластеризованный индекс. Если кластеризованный индекс существует когда вы создаёте первичный ключ, то будет создан уникальный некластеризованный индекс.
Когда вы создаете ограничение на уникальность, подсистема хранения данных создает уникальный некластеризованный индекс. Но вы можете указать создание уникального кластеризованного индекса, если он не был создан ранее.
В общем случае, ограничение на уникальность значение и уникальный индекс это одно и то же.

Почему в SQL Server кластеризованные и некластеризованные индексы называются сбалансированным деревом?

Базовые индексы в SQL Server, кластеризованные или некластеризованные, распространяются по наборам страниц – узлам индекса. Эти страницы организованы в виде определенной иерархии с древовидной структурой, называемой сбалансированным деревом. На верхнем уровне находится корневой узел, на нижнем, конечные узлы листьев, с промежуточными узлами между верхним и нижним уровнями, как показано на рисунке:

Корневой узел предоставляет главную точку входа для запросов, пытающихся получить данные через индекс. Начиная с этого узла, подсистема запросов инициирует переход по иерархической структуре вниз к подходящему конечному узлу, содержащему данные.
К примеру, представим, что поступил запрос на выборку строк, содержащих значение ключа равное 82. Подсистема запросов начинает работу с корневого узла, который отсылает к подходящему промежуточному узлу, в нашем случае 1-100. От промежуточного узла 1-100 происходит переход к узлу 51-100, а оттуда к конечному узлу 76-100. Если это кластеризованный индекс, то на листе узла содержится данные строки, ассоциированной с ключом равным 82. Если же это некластеризованный индекс, то лист индекса содержит указатель на кластеризованную таблицу или конкретную строку в куче.

Как вообще индекс может улучшить производительность запросов, если приходится переходить по всем этим индексным узлам?

Во-первых, индексы не всегда улучшают производительность. Слишком много неверно созданных индексов превращают систему в болото и понижают производительность запросов. Правильнее сказать, что если индексы были аккуратно применены, то они могут обеспечить значительный прирост в производительности.
Подумайте об огромной книге, посвященной настройке производительности SQL Server (бумажной, не об электронном варианте). Представьте, что вы хотите найти информацию о конфигурировании Регулятора ресурсов. Вы можете водить пальцем постранично через всю книгу или открыть содержание и узнать точный номер страницы с искомой информацией (при условии, что книга правильно проиндексирована и в содержании верные указатели). Безусловно, это сэкономит вам значительное время, не смотря на то, что вам надо сначала обратиться к совершенно другой структуре (индексу), чтобы получить необходимую вам информацию из первичной структуры (книги).
Как и книжный указатель, указатель в SQL Server позволяет вам выполнять точные запросы к нужным данным вместо полного сканирования всех данных, содержащихся в таблице. Для маленьких таблиц полное сканирование обычно не проблема, но большие таблицы занимают много страниц с данными, что в результате может привезти с значительному времени выполнения запроса, если не существует индекса, позволяющего подсистеме запросов сразу получить правильное месторасположение данных. Представьте, что вы заблудились на многоуровневой дорожной развязке перед крупным мегаполисом без карты и вы поймёте идею.

Если индексы настолько замечательны, то почему бы просто не создать их на каждый столбец?

Ни одно доброе дело не должно оставаться безнаказанным. По крайней мере, именно так и обстоит дело с индексами. Разумеется, индексы отлично себя показывают, пока вы выполняете запросы на выборку данных оператором SELECT, но как только начинается частый вызов операторов INSERT, UPDATE иDELETE, так пейзаж очень быстро меняется.
Когда вы инициируется запрос данных оператором SELECT, подсистема запросов находит индекс, продвигается по его древовидной структуре и обнаруживает искомые данные. Что может быть проще? Но все меняется, если вы инициируете оператор изменения, такой как UPDATE. Да, для первой части оператора подсистема запросов может снова использовать индекс для обнаружения модифицируемой строки – это хорошие новости. И если происходит простое изменение данных в строке, не затрагивающее изменение ключевых столбцов, то процесс изменения пройдет вполне безболезненно. Но что, если изменение приведет к разделению страниц, содержащих данные, или будет изменено значение ключевого столбца, приводящее к переносу его в другой индексный узел – это приведёт к тому, что индексу может потребоваться реорганизация, затрагивающая все связанные индексы и операции, в результате будет повсеместное падение производительности.
Аналогичные процессы происходят при вызове оператора DELETE. Индекс может помочь найти месторасположение удаляемых данных, но само по себе удаление данных может привести к перестановке страниц. Касаемо оператора INSERT, главного врага всех индексов: вы начинаете добавлять большое количество данных, что приводит к изменению индексов и их реорганизации и все страдают.
Так что учитывайте виды запросов к вашей базе данных при размышлениях какой тип индексов и в каком количестве стоит создавать. Больше не значит лучше. Перед тем как добавить новый индекс на таблицу просчитайте стоимость не только базовых запросов, но и объем занимаемого дискового пространства, стоимость поддержания работоспособности и индексов, что может привести к эффекту домино для других операций. Ваша стратегия проектирования индексов один из важнейших аспектов внедрения и должна включать в рассмотрение множество соображений: от размера индекса, количества уникальных значений, до типа поддерживаемых индексом запросов.

Обязательно ли создавать кластеризованный индекс на столбце с первичным ключом?

Вы можете создать кластеризованный индекс на любой столбце, соответствующем необходимым условиям. Это верно, что кластеризованный индекс и ограничение первичного ключа созданы друг для друга и их брак заключен на небесах, так что усвойте факт, что когда вы создаете первичный ключ, тогда же будет автоматически создан кластеризованный индекс, если он не был создан ранее. Тем не менее, вы можете решить, что кластеризованный индекс будет лучше работать в другом месте, и часто ваше решение будет вполне оправданным.
Главная цель кластеризованного индекса это сортировка всех строк к вашей таблице на основе ключевого столбца, указанного при определении индекса. Это обеспечивает быстрый поиск и легкий доступ к данным таблицы.
Первичный ключ таблицы может быть хорошим выбором, потому что он однозначно идентифицирует каждую строку в таблицы без необходимости добавлять дополнительные данные. В некоторых случаях лучшим выбором будет суррогатный первичный ключ, обладающий не только признаком уникальности, но и малым размером, а значения которого увеличиваются последовательно, что делает некластеризованные индексы, основанные на этом значении более эффективными. Оптимизатор запросов также любит такое сочетание кластеризованого индекса и первичного ключа, потому что соединение таблиц происходит быстрее, чем при соединении другим способом, не использующим первичный ключ и ассоциированный с ним кластеризованный индекс. Как я и говорил это брак, заключенный на небесах.
В конце стоит, однако, отметить, что при создании кластеризованного индекса необходимо принять во внимание несколько аспектов: как много некластеризованных индексов будет основываться на нём, как часто будут изменяться значение ключевого столбца индекса и на сколько ни большие. Когда значение в столбцах кластеризованого индекса изменятся или индекс не будет обеспечивать должной производительности, тогда все другие индексы таблицы могут быть задеты. Кластеризованный индекс должен быть основан на наиболее устойчивом столбце, значения которого увеличиваются в определенном порядке, но не изменяются в случайном. Индекс должен поддерживать запросы к наиболее часто используемым данным таблицы, таким образом запросы получают все преимущества того, что данные сортированы и доступны на корневых узлах, листьях индекса. Если первичный ключ соответствует этому сценарию, то используйте его. Если же нет, то выберите другой набор столбцов.

А что если проиндексировать представление, то это по-прежнему будет представление?

Представление – это виртуальная таблица, формирующая данные из одной или нескольких таблиц. По сути, это именованный запрос, который получает данные из нижележащих таблиц, когда вы вызываете запрос к этому представлению. Вы можете улучшить производительность запросов, создав кластеризованных индекс и некластеризованные индексы у этого представления, аналогично как вы создаете индексы у таблицы, но основной нюанс состоит в том, что первоначально создается кластеризованный индекс, а затем вы можете создать некластеризованный.
Когда создается индексированное представление (материализованное представление), тогда само определение представления остается отдельной сущностью. Это, в конце концов, всего лишь жестко прописанный оператор SELECT, хранящийся в базе данных. А вот индекс совсем другая история. Когда вы создаете кластеризованный или некластеризованный индекс у предастваления, то данные физически сохраняются на диск, аналогично обычному индексу. В дополнение, когда в нижележащих таблицах изменяются данные, то индекс представления автоматически изменяется (это означает, что вы можете захотеть избежать индексирования представлений тех таблиц, в которых происходят частые изменения). В любом случае, представление остается представлением — взглядом на таблицы, но именно выполненном в данный момент, с индексами ему соответствующими.
Перед тем как вы сможете создать индекс у представления, оно должно соответствовать нескольким ограничениям. К примеру, представление может ссылаться только на базовые таблицы, но не другие представления и эти таблицы должны находиться в той же самой базе данных. На самом деле там множество других ограничений, так что не забудьте обратиться к документации по SQL Server за всеми грязными подробностями.

Зачем использовать покрывающий индекс взамен составного индекса?

Во-первых, давайте убедимся, что мы понимаем различие между ними. Составной индекс это просто обычный индекс, в который включено больше одного столбца. Несколько ключевых столбцов может использоваться для обеспечения уникальности каждой строки таблицы, также возможен вариант, когда первичный ключ состоит из нескольких столбцов, обеспечивающих его уникальность, или вы пытаетесь оптимизировать выполнение часто вызываемых запросов к нескольким столбцам. В общем, однако, чем больше ключевых столбцов содержит индекс, тем менее эффективна работа этого индекса, а значит составные индексы стоит использовать разумно.
Как было сказано, запрос может извлечь огромную выгоду, если все необходимые данные сразу расположены на листьях индекса, как и сам индекс. Это не проблема для кластеризованного индекса, т.к. все данные уже там (вот почему так важно хорошенько подумать когда вы создаете кластеризованный индекс). Но некластеризованный индекс на листьях содержит только ключевые столбцы. Для доступа ко всем остальным данным оптимизатору запросов необходимы дополнительные шаги, что может вызвать значительные дополнительные накладные расходы для выполнения ваших запросов.
Вот где покрывающий индекс спешит на помощь. Когда вы определяете некластеризованный индекс, то можете указать дополнительные столбцы к вашим ключевым. К примеру, представим, что ваше приложение часто запрашивает данные столбцов OrderID и OrderDate в таблице Sales:

Вы можете создать составной некластеризованный индекс на обоих столбцах, но столбец OrderDate только добавит накладных расходов на обслуживание индекса, но так и не сможет служить особо полезным ключевым столбцом. Лучшее решение будет это создание покрывающего индекса с ключевым столбцом OrderID и дополнительно включенным столбцом OrderDate:

При этом вы избегаете недостатков, возникающих при индексации излишних столбцов, в то же время сохраняете преимущества хранения данных на листьях при выполнении запросов. Включенный столбец не является частью ключа, но данные хранятся именно на конечном узле, листе индекса. Это может улучшить производительность выполнения запроса без каких либо дополнительных расходов. К тому же, на столбцы, включенные в покрывающий индекс, накладывается меньше ограничений, нежели на ключевые столбцы индекса.

Имеет ли значение количество дубликатов в ключевом столбце?

Когда вы создаете индекс, вы обязаны постараться уменьшить количество дубликатов в ваших ключевых столбцах. Или более точно: стараться держать коэффициент повторяющихся значений настолько низким, насколько это возможно.
Если вы работаете с составным индексом, то дублирование относится ко всем ключевым столбцам в целом. Отдельный столбец может содержать множество повторяющихся значений, но повторения среди всех столбцов индекса должно быть минимальным. К примеру, вы создаете составной некластеризованный индекс на столбцах FirstName и LastName, вы можете иметь множество значений равных John и множество Doe, но вы хотите иметь как можно меньше значений John Doe, или лучше только одно значение John Doe.
Коэффициент уникальности значений ключевого столбца называется избирательностью индекса. Чем больше уникальных значений, тем выше избирательность: уникальный индекс обладает наибольшей возможной избирательностью. Подсистема запросов очень любит столбцы с высоким значением избирательности, особенно если эти столбцы участвуют в условиях выборки WHERE ваших наиболее часто выполняемых запросов. Чем выше избирательность индекса, тем быстрее подсистема запросов может уменьшить размер результирующего набора данных. Обратной стороной, разумеется, является то, что столбцы с относительно небольшим количеством уникальных значений редко будут хорошими кандидатами на индексирование.

Можно ли создать некластеризованный индекс только для определенного подмножества данных ключевого столбца?

По умолчанию, некластеризованный индекс содержит по одной строке для каждой строки таблицы. Конечно, вы можете сказать то же самое относительно кластеризованного индекса, принимая в расчет, что такой индекс это и есть таблица. Но что касается некластеризованного индекса, то отношение «один к одному» важный концепт, потому что, начиная с версии SQL Server 2008, у вас есть возможность создать фильтруемый индекс, который ограничивает включенные в него строки. Фильтруемый индекс может улучшить производительность выполнения запросов, т.к. он меньше по размеру и содержит отфильтрованную, более аккуратную, статистику, чем вся табличная — это приводит к созданию улучшенных планов выполнения. Фильтруемый индекс также требует меньше места для хранения и меньших затрат на обслуживание. Индекс обновляется только когда изменяются подходящие под фильтр данные.
В дополнение, фильтруемый индекс легко создать. В операторе CREATE INDEX просто необходимо указать в WHERE условие фильтрации. К примеру, вы можете отфильтровать из индекса все строки, содержащие NULL, как показано в коде:

Мы можем, фактически, отфильтровать любые данные, которые не важны в критических запросах. Но будьте внимательны, т.к. SQL Server накладывает несколько ограничений на фильтруемые индексы, такие, как невозможность создать фильтруемый индекс у представления, так что внимательно читайте документацию.
Также, может случиться, что вы можно достичь подобных результатов созданием индексированного представления. Однако, фильтруемый индекс имеет несколько преимуществ, таких как возможность уменьшить стоимость обслуживания и улучшить качество ваших планов выполнения. Фильтруемые индексы также допускают перестройку в онлайн-режиме. Попробуйте это сделать с индексируемым представлением.

Что такое код alter index

ПРИМЕНЯЕТСЯ К: SQL Server (начиная с 2008) База данных SQL Azure Хранилище данных SQL Azure Parallel Data Warehouse

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

index_name
Имя индекса. Имена индексов должны быть уникальными в пределах таблицы или представления, но необязательно должны быть уникальными в пределах базы данных. Имена индексов должны соответствовать правилам идентификаторы.

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

Указание ALL с этой операцией Отказывает, если в таблице имеется один или несколько
REBUILD WITH ONLINE = ON XML-индекс

Индекс ColumnStore: применяется к: SQL Server 2012 через SQL Server 2020 и База данных SQL Azure.

REBUILD PARTITION = partition_number Несекционированный, пространственный, отключенный индекс или XML-индекс
REORGANIZE Индексы с параметром ALLOW_PAGE_LOCKS, равным OFF
РЕОРГАНИЗАЦИЯ РАЗДЕЛА = partition_number Несекционированный, пространственный, отключенный индекс или XML-индекс
IGNORE_DUP_KEY = ON XML-индекс

Индекс ColumnStore: применяется к: SQL Server 2012 через SQL Server 2020 и База данных SQL Azure.

ONLINE = ON XML-индекс

Индекс ColumnStore: применяется к: SQL Server 2012 через SQL Server 2020 и База данных SQL Azure.

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

Если указан ALL вместе с PARTITION = partition_number, все индексы должны быть выровнены. Следовательно, они секционируются на основе эквивалентных функций секционирования. Использование ALL вместе с PARTITION приводит все индексные секции с одинаковым partition_number будут перестроены или реорганизованы. Дополнительные сведения о секционированных индексах см. в разделе Partitioned Tables and Indexes.

имябазыданных
Имя базы данных.

schema_name
Имя схемы, которой принадлежит таблица или представление.

представления table_or_view_name
Имя таблицы или представления, связанного с индексом. Чтобы отобразить отчет по индексам объекта, используйте sys.indexes представления каталога.

База данных SQL Windows Azure поддерживает трехкомпонентный формат имени database_name.[schema_name].table_or_view_name, где database_name — текущая база данных или база данных tempdb, а имя таблицы или представления table_or_view_name начинается с #.

REBUILD [ WITH ( [ ,. n]) ]
Указывает, что индекс будет перестроен с использованием тех же столбцов, типов индекса, атрибута уникальности и порядка сортировки. Это предложение эквивалентно DBCC DBREINDEX. REBUILD включает отключенный индекс. При перестройке кластеризованного индекса не перестраиваются ассоциированные некластеризованные индексы, если только не указано ключевое слово ALL. Если параметры индекса не заданы, существующий параметр индекса, значений, хранящихся в sys.indexes применяются. Для любого параметра индекса, значение которого не хранится в sys.indexes, применяется значение по умолчанию, указанное в определении аргумента параметра.

Если указано ключевое слово ALL, а базовая таблица реализована в виде кучи, операция перестроения не воздействует на таблицу. Перестраиваются все некластеризованные индексы, ассоциированные с таблицей.

Возможно минимальное протоколирование операции перестроения, если модель восстановления базы данных настроена на массовый или простой режим.

Предупреждение

При перестроении первичного XML-индекса индексированная пользовательская таблица недоступна в течение действия операции с индексами.

Примечание
Применяется к: SQL Server 2012 через SQL Server 2020 и База данных SQL Azure.

Для индексов columnstore, операция перестроения:

Не используется порядок сортировки.

Приобретает монопольную блокировку на таблице или секции на то время, как происходит перестроение. Во время перестройки данные находятся в автономном режиме и недоступны даже при использовании NOLOCK, RCSI или SI.

Повторно сжимает все данные в columnstore. Во время перестроения существуют две копии индекса columnstore. После завершения перестроения SQL Server удаляет исходный индекс columnstore.

Дополнительные сведения о перестроении индексов columnstore см. в разделе дефрагментации индексов Columnstore —

Применяется к: SQL Server 2008 через SQL Server 2020 и База данных SQL Azure.

Указывает, что только одна секция индекса будет перестроена или реорганизована. СЕКЦИИ нельзя использовать, если index_name не секционированного индекса.

PARTITION = ALL, перестроение всех секций.

Создание и перестройка невыровненных индексов для таблицы, количество секций в которой превышает 1000, возможны, но не поддерживаются. Это может привести к снижению производительности или чрезмерному потреблению памяти во время таких операций. Если количество секций превышает 1000, рекомендуется использовать только выровненные индексы.

Предупреждение
Применяется к: SQL Server 2008 через SQL Server 2020 и База данных SQL Azure.

Количество секций секционированного индекса, который необходимо перестроить или реорганизовать. partition_number является константным выражением, которое может ссылаться на переменные. К ним относятся переменные определяемых пользователем типов или функции и определяемые пользователем функции, но не ссылки на инструкции языка Transact-SQL. partition_number должен существовать или выполнение инструкции завершается неудачно.

WITH ( )

Применяется к: SQL Server 2008 через SQL Server 2020 и База данных SQL Azure.

Параметр SORT_IN_TEMPDB, MAXDOP и DATA_COMPRESSION — это параметры, которые могут быть указаны при перестроении одиночной секции (СЕКЦИИ = n). XML-индексы не могут быть указаны в операции перестроения одиночной секции.

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

РЕОРГАНИЗАЦИЯ индекса rowstore
Для индексов rowstore REORGANIZE указывает для реорганизации конечный уровень индекса. Операция REORGANIZE не:

Всегда выполняется в интерактивном режиме. Это означает, что долгосрочные блокировки таблицы не удерживаются и запросы или обновления базовой таблицы могут продолжаться во время выполнения транзакции ALTER INDEX REORGANIZE.

Не допускается для отключенного индекса

Не допускается, если ALLOW_PAGE_LOCKS установлен в OFF

Не откатывается выполняется в транзакции, и выполняется откат транзакции.

РЕОРГАНИЗАЦИЯ С ( LOB_COMPACTION = < ON | OFF > )
Применяется к индексам rowstore.

Указывает для сжатия всех страниц, содержащих данные из этих типов данных больших объектов (LOB): изображения, text, ntext, varchar(max), nvarchar(max), varbinary(max) и xml. Сжатие этих данных можно уменьшить размер данных на диске.

Кластеризованного индекса подвергает сжатию все столбцы LOB, содержащиеся в таблице.

Некластеризованного индекса подвергает сжатию все столбцы LOB, являющиеся неключевыми (включенными) столбцами в индексе.

РЕОРГАНИЗОВАТЬ все выполняет LOB_COMPACTION всех индексов. Для каждого индекса это сжимает все столбцы LOB в кластеризованным индексом, базовой таблицей или включенные столбцы в некластеризованный индекс.

Все страницы, содержащие данные большого объекта, не сжимаются.

Параметр OFF не влияет на кучу.

РЕОРГАНИЗАЦИЯ индекса columnstore
РЕОРГАНИЗАЦИЯ выполняются в сети.

Для индексов columnstore РЕОРГАНИЗАЦИЯ сжимает каждый ЗАКРЫТЫХ дельта-группа строк columnstore как сжатая группа строк.

РЕОРГАНИЗАЦИЯ не требуется для перемещения ЗАКРЫТЫХ разностных групп строк в сжатых групп строк. Фоновый процесс перемещения кортежей (TM) периодически активирующийся для сжатия ЗАКРЫТЫХ разностных групп строк. Если рекомендуется использовать REORGANIZE успевают кортежей. РЕОРГАНИЗАЦИЯ можно сжимать rowgroups более агрессивно.

Чтобы сжать все rowgroups открытым и ЗАКРЫТО, появившееся РЕОРГАНИЗОВАТЬ с (COMPRESS_ALL_ROW_GROUPS) в этом разделе.

РЕОРГАНИЗАЦИЯ индексов columnstore в SQL Server (начиная с 2020) и базы данных SQL Azure, выполняет следующие дополнительные дефрагментации оптимизации сети:

Физически удаляет строки из группы строк при были логически удалены 10% или более строк. Удаленные байты освобождают место на физическом носителе. Например если сжатая строка группы 1 миллион строк 100 тысяч строк, удаленных, SQL Server будет удалить удаленные строки и повторное сжатие группы строк со строками 900 КБ. Сохраняет в хранилище, удалив удаленных строк.

Объединяет один или несколько сжатых rowgroups увеличить строк на rowgroup максимум 1,024,576 строк. Например при массовом импорте 5 пакетов 102 400 строк вы получите 5 сжатых групп строк. При запуске REORGANIZE этих групп строк будет объединяются в 1 сжатой группы строк, размер 512,000 строк. Это предполагается, что было не словарь размер или нехватка памяти.

Для групп строк, в котором 10% или более строк были логически удалены SQL Server попытается объединить этой группы строк с одной или нескольких групп строк. Например группа строк 1 сжимается с 500 000 строк и группы строк 21 сжимается с более чем 1 048 576 строк. 21 группа строк имеет 60% удаляемых строк, который оставляет 409,830 строк. SQL Server поддерживает объединение этих двух групп строк для сжатия новых строк, у которого 909,830 строк.

РЕОРГАНИЗОВАТЬ СТРАНИЦЫ С ИСПОЛЬЗОВАНИЕМ (COMPRESS_ALL_ROW_GROUPS = OFF > )
В SQL Server (начиная с 2020) и базы данных SQL Azure COMPRESS_ALL_ROW_GROUPS предоставляет способ заставить OPEN или CLOSED разностных групп строк в columnstore. В этом случае не понадобится перестроить индекс columnstore, чтобы очистить разностных групп строк. Это, вместе с других удаление и объединение дефрагментации функций делает он более не является необходимым для перестроения индекса в большинстве случаев.

ON заставляет все группы строк в columnstore, независимо от размера и состояние («ЗАКРЫТ» или «ОТКРЫТЬ»).

ОТКЛЮЧЕНИЕ заставляет все ЗАКРЫТЫЕ rowgroups в columnstore.

SET ( [ ,. n] )
Указывает параметры индекса без перестройки или реорганизации индекса. SET нельзя указать для отключенного индекса.

Применяется к: SQL Server 2008 через SQL Server 2020 и База данных SQL Azure.

Определяет разреженность индекса. Значение по умолчанию — OFF.

ON
Процент свободного места, определяемый параметром FILLFACTOR, применяется к страницам индекса промежуточного уровня. Если FILLFACTOR не указан одновременно PAD_INDEX установлен в значение ON, значение коэффициента заполнения, хранящиеся в sys.indexes используется.


ОТКЛЮЧЕНИЕ или fillfactor не указан
Страницы промежуточного уровня заполняются почти полностью. При этом остается достаточно места по крайней мере для одной строки максимального размера, которого может достигать индекс, в зависимости от набора ключей в промежуточных страницах.

Дополнительные сведения см. в разделе CREATE INDEX (Transact-SQL).

Аргумент FILLFACTOR = fillfactor

Применяется к: SQL Server 2008 через SQL Server 2020 и База данных SQL Azure.

Определяет величину в процентах, показывающую насколько должен компонент Компонент Database Engine заполнять конечный уровень каждой страницы индекса во время его создания и изменения. Аргумент FILLFACTOR должно быть целым числом от 1 до 100. Значение по умолчанию равно 0. Значения коэффициентов заполнения 0 и 100 идентичны.

Явный параметр FILLFACTOR применяется, только если индекс создается впервые или перестраивается. Компонент Компонент Database Engine не сохраняет динамически указанный процентный объем свободного места на страницах. Дополнительные сведения см. в разделе CREATE INDEX (Transact-SQL).

Для просмотра коэффициента заполнения, используйте sys.indexes.

Создание или замена кластеризованного индекса со значением FILLFACTOR влияет на пространство памяти, занимаемое данными, так как компонент Компонент Database Engine перераспределяет данные при создании кластеризованного индекса.

Важно
Применяется к: SQL Server 2008 через SQL Server 2020 и База данных SQL Azure.

Указывает, следует ли сохранять результаты сортировки в tempdb. Значение по умолчанию — OFF.

ON
Промежуточные результаты сортировки, используемые для построения индекса, хранятся в tempdb. Если tempdb находится на разных наборах дисков пользовательской базы данных, это может уменьшить время, необходимое для создания индекса. Однако это увеличивает использование места на диске, которое используется при индексировании.

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

Если выполнение сортировки не требуется или если сортировка может быть выполнена в памяти, параметр SORT_IN_TEMPDB пропускается.

IGNORE_DUP_KEY ** = **
Определяет ответ на ошибку, случающуюся, когда операция вставки пытается вставить в уникальный индекс повторяющиеся значения ключа. Параметр IGNORE_DUP_KEY применяется только к операциям вставки, производимым после создания или перестроения индекса. Значение по умолчанию — OFF.

ON
Если в уникальный индекс вставляются повторяющиеся значения ключа, выводится предупреждающее сообщение. С ошибкой завершаются только строки, нарушающие ограничение уникальности.

OFF
Если в уникальный индекс вставляются повторяющиеся значения ключа, выводится сообщение об ошибке. Будет выполнен откат всей операции INSERT.

IGNORE_DUP_KEY не может быть присвоено значение ON для индексов, созданных на представлении, неуникальных индексов, XML-индексы, Пространственные индексы и Фильтруемые индексы.

Для просмотра значения IGNORE_DUP_KEY используйте sys.indexes.

Для обратной совместимости синтаксиса аргумент WITH IGNORE_DUP_KEY эквивалентен аргументу WITH IGNORE_DUP_KEY = ON.

STATISTICS_NORECOMPUTE ** = **
Указывает, выполнялся ли перерасчет статистики распределения. Значение по умолчанию — OFF.

ON
Устаревшие статистики не пересчитываются автоматически.

OFF
Автоматическое обновление статистических данных включено.

Чтобы восстановить автоматическое обновление статистики, следует установить STATISTICS_NORECOMPUTE в значение OFF или выполнить UPDATE STATISTICS без предложения NORECOMPUTE.

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

STATISTICS_INCREMENTAL = OFF >
Когда ON, являются статистики создаются как статистики отдельно по секциям. Когда OFF, дерево статистик удаляется и SQL Server повторно вычисляет статистики. Значение по умолчанию — OFF.

Если статистики по секциям не поддерживаются, параметр пропускается и выводится предупреждение. Добавочные статистики не поддерживаются для следующих типов статистических данных.

Статистики, созданные с индексами, не выровненными по секциям для базовой таблицы.

Статистики, созданные в доступных для чтения базах данных-получателях AlwaysOn.

Статистики, созданные в базах данных, доступных только для чтения.

Статистики, созданные по фильтрованным индексам.

Статистика, созданная по представлениям.

Статистики, созданные по внутренним таблицам.

Статистики, созданные с пространственными индексами или XML-индексами.

Важно
Применяется к: SQL Server 2014 через SQL Server 2020 и База данных SQL Azure.

Для XML-индекса или пространственного индекса поддерживается только значение ONLINE = OFF; при ONLINE = ON возникает ошибка.

Операции с индексами в сети доступны не во всех выпусках Microsoft SQL Server. Список функций, поддерживаемых различными выпусками SQL Server, в разделе выпусков и компонентов поддерживается для SQL Server 2020.

ON
Долгосрочные блокировки таблицы не поддерживаются во время операций с индексами. Во время главной фазы операций с индексами только блокировка с намерением совмещаемого доступа (IS) удерживается в исходной таблице. Это позволяет продолжить выполнение запросов или обновлений для базовых таблиц и индексов. В начале операции совмещаемая блокировка (S) исходного объекта поддерживается в течение очень короткого времени. Если создается некластеризованный индекс, то по завершении операции на короткое время создается блокировка типа S (совмещаемая) для источника. Блокировка типа SCH-M (изменения схемы) запрашивается, если кластеризованный индекс создается или удаляется в режиме в сети либо, происходит перестроение кластеризованного или некластеризованного индекса. При создании индекса для временной локальной таблицы параметр ONLINE не может принимать значение ON.

OFF
Блокировки таблиц применяются во время выполнения операций с индексами. Операция с индексами в режиме «вне сети», которая создает, перестраивает или удаляет кластеризованный, пространственный или XML-индекс либо перестраивает или удаляет некластеризованный индекс, получает блокировку изменения схемы (Sch-M) для этой таблицы. Это предотвращает доступ к базовой таблице всех пользователей во время операции. Операция с индексами вне сети, создающая некластеризованный индекс, получает совмещаемую блокировку (S) в таблице. Это запрещает проводить обновления базовой таблицы, но разрешает проводить операции чтения, например инструкции SELECT.

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

индексы локальных временных таблиц;

подмножество секционированного индекса (секционированный индекс можно целиком перестроить в сети).

База данных SQL до версии 12 и SQL Server до SQL Server 2012, не допускают ONLINE вариант для построения кластеризованного индекса или перестройте операции, если базовая таблица содержит varchar(max) или varbinary(max) столбцов.

ALLOW_ROW_LOCKS = < ON |

Примечание
Применяется к: SQL Server 2008 через SQL Server 2020 и База данных SQL Azure.

Указывает, разрешена ли блокировка строк. Значение по умолчанию — ON.

ON
Блокировки строк допустимы при доступе к индексу. Компонент Компонент Database Engine определяет, когда используются блокировки строки.

OFF
Блокировки строк не используются.

ALLOW_PAGE_LOCKS = < ON |

Применяется к: SQL Server 2008 через SQL Server 2020 и База данных SQL Azure.

Указывает, разрешена ли блокировка страниц. Значение по умолчанию — ON.

ON
Блокировки страниц допустимы при доступе к индексу. Компонент Компонент Database Engine определяет, когда используются блокировки страниц.

OFF
Блокировки страниц не используются.

Индекс не может быть реорганизован, если ALLOW_PAGE_LOCKS установлен в состояние OFF.

Примечание
Применяется к: SQL Server 2008 через SQL Server 2020 и База данных SQL Azure.

Переопределяет максимальная степень параллелизма параметр конфигурации в течение операции с индексами. Дополнительные сведения см. в разделе Configure the max degree of parallelism Server Configuration Option. MAXDOP можно использовать для ограничения числа процессоров, используемых при параллельном выполнении планов. Максимальное число процессоров — 64.

Хотя параметр MAXDOP синтаксически поддерживается для всех индексов XML, для пространственного или первичного XML-индекса инструкция ALTER INDEX в настоящее время использует только один процессор.

max_degree_of_parallelism может быть:

1
Подавляет формирование параллельных планов.

>1
Ограничивает указанным значением максимальное число процессоров, используемых для параллельных операций с индексами.

0 (по умолчанию)
В зависимости от текущей рабочей нагрузки системы использует реальное или меньшее число процессоров.

Важно

Параллельные операции с индексами доступны не во всех выпусках Microsoft SQL Server. Список функций, поддерживаемых различными выпусками SQL Server, в разделе выпусков и компонентов поддерживается для SQL Server 2020.

COMPRESSION_DELAY = < |duration [Minutes] >
Этот компонент будет доступен после SQL Server 2020 CTP-версии 3.3

В таблице на диске задержка задает минимальное число минут, которые должны оставаться дельта-группа строк в состояние CLOSED в разностная группа строк до SQL Server можно сжать в сжатой группы строк. Так как не отслеживать вставки и обновления таблиц на диске время в отдельных строках, SQL Server применяет задержка разностных групп строк в ЗАКРЫТОМ состоянии.
Значение по умолчанию — 0 минут.

Значение по умолчанию — 0 минут.

Рекомендации о том, как использовать COMPRESSION_DELAY см. в разделе индексы Columnstore для оперативной аналитики в режиме реального времени.

DATA_COMPRESSION
Задает режим сжатия данных для указанного индекса, номера секции или диапазона секций. Существуют следующие параметры выбора.

NONE
Индекс или заданные секции не сжимаются. Это не относится к индексам columnstore.

ROW
Для индекса или заданных секций производится сжатие строк. Это не относится к индексам columnstore.

PAGE
Для индекса или заданных секций производится сжатие страниц. Это не относится к индексам columnstore.

Примечание
Применяется к: SQL Server 2014 через SQL Server 2020 и База данных SQL Azure.

Применяется только к индексам columnstore, включая некластеризованные и кластеризованные индексы columnstore. COLUMNSTORE указывает, что должны быть распакованы индекс или конкретные секции, которые были упакованы с помощью параметра COLUMNSTORE_ARCHIVE. При восстановлении данных сжатие будет продолжаться с применением сжатия columnstore, предусмотренного для всех индексов columnstore.

Применяется к: SQL Server 2014 через SQL Server 2020 и База данных SQL Azure.

Применяется только к индексам columnstore, включая некластеризованные и кластеризованные индексы columnstore. Параметр COLUMNSTORE_ARCHIVE обеспечивает дальнейшее сжатие указанной секции до еще меньшего размера. Это может использоваться для архивации или в других ситуациях, где требуется уменьшение объема пространства и допускается увеличение затрат времени на сохранение и выборку

Дополнительные сведения о сжатии см. в разделе сжатие данных.

В РАЗДЕЛАХ ( <

| <>> > [,. n] )

Применяется к: SQL Server 2008 через SQL Server 2020 и База данных SQL Azure.

Указывает секции, к которым применяется параметр DATA_COMPRESSION. Если индекс не секционирован, аргумент ON PARTITIONS создаст ошибку. Если не указано предложение ON PARTITIONS, то параметр DATA_COMPRESSION применяется ко всем секциям секционированного индекса.

можно указать одним из следующих способов.

Указать номер секции, например ON PARTITIONS (2).

Указать номера нескольких секций через запятые, например ON PARTITIONS (1, 5).

Указать диапазоны и отдельные секции: ON PARTITIONS (2, 4, 6 TO 8).

<>>можно указать как номера секций, разделенными ключевым словом, например: ON PARTITIONS (6-8).


Чтобы для разных секций задать разные типы сжатия данных, укажите параметр DATA_COMPRESSION несколько раз, например следующим образом.

ON
Долгосрочные блокировки таблицы не поддерживаются во время операций с индексами. Во время главной фазы операций с индексами только блокировка с намерением совмещаемого доступа (IS) удерживается в исходной таблице. Необходимо наличие S-блокировки таблицы в начале перестройки индекса и блокировки Sch-M на таблице в конце перестроения индекса в режиме «в сети». Обе блокировки являются короткими блокировками метаданных, но при этом блокировка изменения схемы (Sch-M) должна ожидать завершения всех блокирующих транзакций. Во время ожидания Sch-M блокирует все другие транзакции, ожидающие за этой блокировкой доступа к одной таблице.

Перестроение индекса в сети можно задать low_priority_lock_wait параметров, описанных далее в этом разделе.

OFF
Блокировки таблиц применяются во время выполнения операций с индексами. Это предотвращает доступ к базовой таблице всех пользователей во время операции.

Примечание
Применяется к: SQL Server 2014 через SQL Server 2020 и База данных SQL Azure.

Перестроение индекса в режиме «в сети» должно ожидать операции блокировки в этой таблице. WAIT_AT_LOW_PRIORITY указывает, что операция перестроения индекса в сети будет ожидать блокировки с низким приоритетом, позволяя другим операциям продолжить, время ожидания операции оперативного построения индекса. Пропуск ОЖИДАНИЯ с НИЗКИМ ПРИОРИТЕТОМ параметр эквивалентен WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE) .

MAX_DURATION = время [МИНУТ ]

Применяется к: SQL Server 2014 через SQL Server 2020 и База данных SQL Azure.

Время ожидания (целочисленное значение, указанное в минутах) в течение которого блокировки для операции перестроения индекса в режиме «в сети» будут ожидать с низким приоритетом при выполнении команды DDL. Если операция будет заблокирована для MAX_DURATION время, один из ABORT_AFTER_WAIT действия выполняются. MAX_DURATION время всегда указывается в минутах и слово МИНУТ можно опустить.

ABORT_AFTER_WAIT = [NONE | SELF | BLOCKERS > ]

Применяется к: SQL Server 2014 через SQL Server 2020 и База данных SQL Azure.

NONE
Продолжить ожидание блокировки с обычным приоритетом.

SELF
Прекратить операцию DDL по перестроению индекса в режиме «в сети», выполняемую в данный момент без выполнения какого-либо действия.

BLOCKERS
Остановить все пользовательские транзакции, в данный момент блокирующие операцию DDL по перестроению индекса в режиме «в сети», чтобы можно было продолжить данную операцию. БЛОКАТОРАМИ параметр требует имени входа разрешение ALTER ANY CONNECTION разрешение.

Инструкция ALTER INDEX не может использоваться для повторного секционирования индекса или его перемещения в другую файловую группу. Эта инструкция не может использоваться для изменения определения индекса, в том числе добавления или удаления столбцов или изменения порядка столбцов. Для выполнения этих операций следует использовать инструкцию CREATE INDEX с предложением DROP_EXISTING.

Если параметр не указан явно, то применяется текущий параметр. Например, если параметр FILLFACTOR не указан в предложении REBUILD, то коэффициент заполнения, сохраненный в системном каталоге, будет использоваться в процессе перестроения. Для просмотра текущего параметра индекса, используйте sys.indexes.

Значения для параметров ONLINE, MAXDOP и SORT_IN_TEMPDB не хранятся в системном каталоге. Если значение некоторого параметра не указано в инструкции индекса, то используется значение по умолчанию.

В компьютерах с несколькими процессорами инструкция ALTER INDEX REBUILD, как и другие запросы, использует больше процессоров для операций просмотра и сортировки, связанных с изменением индекса. При выполнении инструкции ALTER INDEX REORGANIZE, с предложением LOB_COMPACTION или без максимальная степень параллелизма значение представляет собой однопотоковую операцию. Дополнительные сведения см. в статье Настройка параллельных операций с индексами.

Индекс нельзя реорганизовать или перестроить, если файловая группа, в которой он находится, размещена вне сети или предназначена только для чтения. Если указывается ключевое слово ALL, а один или несколько индексов находятся в файловой группе, которая размещена вне сети или предназначена только для чтения, то выполнить инструкцию не удастся.

При перестроении старый индекс удаляется, и создается новый. Таким образом, устраняется фрагментация, восстанавливается место на диске путем сжатия страниц с учетом указанного или существующего коэффициента заполнения, переупорядочиваются индексные строки в последовательных страницах. Если указывается ключевое слово ALL, то все индексы для таблицы удаляются и перестраиваются в одной транзакции. Ограничения FOREIGN KEY не обязательно отменять заранее. Если перестраиваются индексы с 128 или большим числом экстентов, то компонент Компонент Database Engine откладывает процедуры освобождения страниц и связанные с ними блокировки до фиксации транзакции.

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

Начиная с выпуска SQL Server 2012 статистические данные не создаются путем сканирования всех строк таблицы при создании или перестроении секционированного индекса. Вместо этого оптимизатор запросов использует для создания статистики алгоритм выборки по умолчанию. Для получения статистики по секционированным индексам путем сканирования всех строк таблицы используйте инструкции CREATE STATISTICS или UPDATE STATISTICS с предложением FULLSCAN.

В предыдущих версиях SQL Server иногда можно было перестроить некластеризованный индекс, чтобы исправить несоответствия, вызванные отказами оборудования. В SQL Server 2008 и более поздних версиях по-прежнему можно скорректировать такие несоответствия между индексом и кластеризованным индексом, перестроив некластеризованный индекс в режиме «вне сети». Однако нельзя устранить несоответствия некластеризованного индекса, перестроив индекс в режиме в сети, потому что механизм перестроения в этом режиме будет использовать существующий некластеризованный индекс в качестве основы для перестроения и тем самым закрепит несоответствие. При автономном перестроении индекса иногда может принудительно запускаться проверка кластеризованного индекса (или кучи), и в результате устраняются несоответствия. Чтобы обеспечить перестроение из кластеризованного индекса, удалите и повторно создайте некластеризованный индекс. В предыдущих версиях рекомендованным методом устранения несоответствий было восстановление неправильных данных из резервных копий, однако исправить несоответствия индекса можно, перестроив некластеризованный индекс в режиме «вне сети». Дополнительные сведения см. в разделе DBCC CHECKDB (Transact-SQL).

Перестроение кластеризованного индекса columnstore, SQL Server:

Приобретает монопольную блокировку на таблице или секции на то время, как происходит перестроение. Во время перестроения данные находятся в режиме «вне сети» и недоступны.

Дефрагментирует таблицу columnstore, физически удаляя строки, которые были логически удалены из таблиц; удаленные байты освобождают место на физическом носителе.

Считывает все данные из исходного индекса columnstore, включая deltastore. Объединяет данные в новые группы строк и сжимает columnstore в группы строк.

Требует места на физическом носителе для хранения двух копий индекса columnstore, пока происходит его перестроение. После завершения перестроения SQL Server удаляет исходный кластеризованный индекс columnstore.

Для реорганизации индекса требуется минимальный объем системных ресурсов. При реорганизации концевой уровень кластеризованных и некластеризованных индексов на таблицах и представлениях дефрагментируется путем физической реорганизации страниц конечного уровня, в результате чего они выстраиваются в соответствии с логическим порядком конечных узлов (слева направо). Кроме того, реорганизация сжимает страницы индекса. Их сжатие производится в соответствии с текущим значением коэффициента заполнения. Для просмотра коэффициента заполнения, используйте sys.indexes.

Если указывается ключевое слово ALL, то реляционные индексы, как кластеризованные, так и некластеризованные, и XML-индексы для таблицы реорганизуются. Существуют некоторые ограничения при указании ключевого слова ALL, см. определение ALL в разделе «Аргументы».

Отключение индексов предотвращает доступ пользователя к индексам в случае использования кластеризованных индексов к данным базовой таблицы. Определение индекса остается в системном каталоге. Отключение некластеризованных индексов или кластеризованных индексов в представлении физически удаляет данные индекса. При отключении кластеризованного индекса блокируется доступ к данным, но данные остаются необслуживаемыми в сбалансированном дереве до тех пор, пока индекс не будет удален или перестроен. Для просмотра состояния включенного или отключенного индекса запрос is_disabled столбца в sys.indexes представления каталога.

Если таблица входит в публикацию репликации транзакций, то нельзя отключить никакие индексы, связанные с первичными ключевыми столбцами. Эти индексы необходимы для репликации. Чтобы отключить индексы, сначала необходимо удалить таблицу из публикации. Дополнительные сведения см. в разделе публикации данных и объектов базы данных.

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

Можно установить параметры ALLOW_ROW_LOCKS, ALLOW_PAGE_LOCKS, IGNORE_DUP_KEY и STATISTICS_NORECOMPUTE для конкретного индекса без перестройки или реорганизации этого индекса. Измененные значения немедленно применяются к индексу. Чтобы просмотреть эти параметры, используйте sys.indexes. Дополнительные сведения см. в разделе Установка параметров индекса.

Параметры блокировок строк и страниц

Когда присвоены значения ALLOW_ROW_LOCKS = ON и ALLOW_PAGE_LOCK = ON, при доступе к индексу допустимы блокировки на уровне строк, уровне страниц и уровне таблиц. Компонент Компонент Database Engine выбирает соответствующую блокировку и может повышать уровень с блокировки строки или страницы до блокировки таблицы.

Если присвоены значения ALLOW_ROW_LOCKS = OFF и ALLOW_PAGE_LOCK = OFF, при доступе к индексу допустима только блокировка на уровне таблиц.

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

Примечание
ALLOW_ROW_LOCKS = ON или OFF Для кучи и любых соответствующих некластеризованных индексов.
ALLOW_PAGE_LOCKS = ON Для кучи и любых соответствующих некластеризованных индексов.
ALLOW_PAGE_LOCKS = OFF Полностью для некластеризованных индексов. Это означает, что все блокировки страниц запрещаются для некластеризованных индексов. В куче запрещены только общая блокировка (S), блокировка обновления (U) и монопольная блокировка (X) для страниц. Компонент Компонент Database Engine может запросить намеренную блокировку страницы (IS, IU или IX) для внутренних целей.

Если при перестройке индекса параметр ONLINE установлен в значение ON, то базовые объекты, таблицы и связанные с ними индексы доступны для запросов и изменения данных. Можно также перестроить в режиме «в сети» часть индекса, находящегося в одной секции. Монопольные блокировки таблиц удерживаются лишь на очень короткое время в процессе изменения.

Реорганизация индекса всегда выполняется в режиме в сети. Процесс не удерживает блокировку в течение долгого времени и поэтому не блокирует выполняемые запросы и обновления.

Параллельные операции с индексами в режиме «в сети» для одной таблицы или секции можно выполнять лишь при выполнении следующих действий:

создание нескольких некластеризованных индексов;

реорганизация различных индексов в одной таблице;

реорганизация различных индексов при перестройке неперекрывающихся индексов в одной таблице.

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

Дополнительные сведения см. в статье Perform Index Operations Online.

WAIT_AT_LOW_PRIORITY

Для выполнения инструкции DDL для перестроения индекса в режиме «в сети» все активные блокирующие транзакции, выполняемые для конкретной таблицы, должны быть завершены. Если выполняется перестроение индекса в режиме «в сети», то все новые транзакции, готовые к выполнению на данной таблице, блокируются. Хотя продолжительность блокировки для перестроения индекса в режиме «в сети» очень коротка, ожидание завершения всех открытых транзакций на данной таблице и блокировка новых запускаемых транзакций может значительно отразиться на пропускной способности и времени выполнения операции, а также значительно ограничить доступ к базовой таблице. WAIT_AT_LOW_PRIORITY параметр позволяет администратору базы данных управлять S-lock и Sch-M блокировками, необходимыми для перестроения индекса в сети, их можно выбрать один из трех значений. Во всех 3 случаях, если во время ожидания ( (MAX_DURATION = n [minutes]) ) нет блокирующих действий, то перестроение индекса в режиме «в сети» выполняется немедленно и без ожидания завершения инструкции DDL.

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

Ограничение PRIMARY KEY в пользовательской таблице не может быть изменено, пока для столбца этой таблицы определен пространственный индекс. Для изменения ограничения PRIMARY KEY сначала необходимо удалить все пространственные индексы таблицы. После изменения ограничения PRIMARY KEY все пространственные индексы можно создать повторно.

В отдельной операции перестроения секции невозможно указать пространственные индексы. Однако пространственные индексы можно указать при полном перестроении секции.

Чтобы изменить параметры, характерные для пространственного индекса (такие как BOUNDING_BOX или GR >

Дополнительную информацию о сжатии данных см. в разделе Сжатие данных.

Чтобы оценить, как изменение сжатия PAGE и ROW повлияет на таблицы, индекса или секции, используйте sp_estimate_data_compression_savings хранимой процедуры.

На секционированные индексы налагаются следующие ограничения.

Если у таблицы есть невыровненные индексы , то изменить настройку сжатия отдельной секции с помощью инструкции ALTER INDEX ALL невозможно.

Инструкции ALTER INDEX <> > . Инструкция REBUILD PARTITION . производит перестроение указанной секции индекса.

Инструкции ALTER INDEX <> > . Инструкция REBUILD WITH . производит перестроение всех секций индекса.

При выполнении инструкции ALTER INDEX ALL. в таблице обновляются только статистика связаны с индексами. Автоматические или созданные вручную статические данные таблицы (вместо индекса) не обновляются.

Для выполнения ALTER INDEX необходимо иметь как минимум разрешение ALTER для таблицы или представления.

База данных SQL Azure не использует параметры файловой группы и filestream.

ColumnStore индексы будут недоступны до выпуска SQL Server 2012.

В этих примерах применяется к индексам columnstore.

РЕОРГАНИЗАЦИЯ Демонстрация

Этот пример демонстрирует, как работает команда ALTER INDEX REORGANIZE. Он создает таблицу, несколько групп строк и демонстрирует, как РЕОРГАНИЗОВАТЬ объединяет группы строк.

Используйте параметр TABLOCK для вставки строк в параллельном режиме. Начиная с SQL Server 2020, операция INSERT INTO могут выполняться параллельно при использовании TABLOCK.

Выполните следующую команду для просмотра ОТКРЫТЫХ разностных групп строк. Количество групп строк зависит от степени параллелизма.

Выполните следующую команду, чтобы принудительно все ЗАКРЫТЫЕ и ОТКРЫТЫЕ rowgroups в columnstore.

Запустите эту команду, и вы увидите, что меньше rowgroups объединяются в один сжатой группы строк.

A. Сжатие ЗАКРЫТЫХ разностных групп строк в columnstore

В этом примере используется REORGANIZE параметр сжимает каждый ЗАКРЫТЫХ дельта-группа строк в columnstore как сжатая группа строк. Это не обязательно, но полезно, когда задача переноса кортежей не сжимает группы строк Closed достаточно быстро.

Б. Сжатие всех ОТКРЫТЫХ и ЗАКРЫТИЯ разностных групп строк в columnstore

Не применяется к: SQL Server 2012 и 2014 г.

Начиная с SQL Server 2020, можно запустить с РЕОРГАНИЗОВАТЬ (COMPRESS_ALL_ROW_GROUPS = ON) чтобы сжать каждый открытым и ЗАКРЫТО дельта-группа строк в columnstore в виде сжатой группы строк. Это очищает deltastores и заставляет все строки, чтобы получить сжаты в columnstore. Это полезно особенно после выполнения множества операций вставки, так как эти операции хранения строк в один или несколько deltastore.

РЕОРГАНИЗАЦИЯ объединяет rowgroups для заполнения rowgroups максимальным количеством строк Таким образом при сжатии всех открытым и ЗАКРЫТЫЕ rowgroups не приходится с большим количеством сжатый rowgroups только несколько строк, в которой их. Вы хотите rowgroups быть заполнена настолько, можно уменьшить размер сжатого и повысить производительность запросов.

В. Дефрагментация индекса columnstore

Не применяется к: SQL Server 2012 и 2014 г.

Начиная с SQL Server 2020, РЕОРГАНИЗАЦИЯ более сжатие разностных групп строк в columnstore. Он также выполняет оперативной дефрагментации. Во-первых он уменьшает размер хранилища столбцов, физически удалить удаленные строки 10% или более строк в группе строк после удаления. Затем она объединяет rowgroups вместе, чтобы сформировать больших групп строк, следует максимально 1,024,576 строк на группы строк. Все группы строк, измененных повторно получить сжаты.

Начиная с SQL Server 2020, перестроение индекса columnstore больше не требуется в большинстве случаев после РЕОРГАНИЗАЦИИ физически удаляет удаленных строк и объединяет группы строк. Параметр COMPRESS_ALL_ROW_GROUPS заставляет все OPEN или CLOSED разностных групп строк в columnstore, который ранее может быть выполнено только при повторной сборки. РЕОРГАНИЗАЦИЯ находится в оперативном режиме и происходит в фоновом режиме, поэтому запросы могут выполняться так, как выполняется.

Г. Перестроить кластеризованный индекс в автономном режиме

Область применения: SQL Server 2012, SQL Server 2014 г.

Начиная с SQL Server 2020 и в База данных SQL Azure, мы рекомендуем использовать вместо инструкции ALTER INDEX REBUILD инструкции ALTER INDEX REORGANIZE.

Примечание

В SQL Server 2012 и 2014 РЕОРГАНИЗАЦИИ используется только для сжатия строк Closed в columnstore. Для перестроения индекса является единственным способом для выполнения операции дефрагментации и заставить все разностных групп строк в columnstore.

В этом примере показано, как перестроить кластеризованный индекс и заставить все разностных групп строк в columnstore. В этом первом шаге подготавливается таблица FactInternetSales2 с кластеризованным индексом columnstore и происходит вставка данных из первых четырех столбцов.

Результаты показывают, имеется одна группа строк OPEN, это означает SQL Server будет ожидать несколько строк, добавляемый перед закроет группу строк и перемещает данные в ColumnStore. Эта следующая инструкция перестраивает кластеризованный индекс, благодаря чему все строки в columnstore.

Результаты инструкции SELECT показывают, что группа строк имеет атрибут COMPRESSED, а это означает, что сегменты столбца этой группы строк теперь упакованы и хранятся в columnstore.

Д. Перестроение секции кластеризованный индекс в автономном режиме


Используйте это для: SQL Server 2012, SQL Server 2014 г.

Для перестроения секции большого кластеризованный индекс, используйте инструкцию ALTER INDEX REBUILD с параметром секции. В этом примере перестраивается секции 12. Начиная с SQL Server 2020, рекомендуется, заменив ПЕРЕСТРОЕНИЯ REORGANIZE.

Е. Изменить columstore кластеризованный индекс, чтобы использовать архивного сжатия

Не применяется к: SQL Server 2012

Можно уменьшить размер кластеризованный индекс еще дальше, используя параметр сжатия данных COLUMNSTORE_ARCHIVE. Это удобно для старых данных, который требуется сохранить в более дешевое хранение. Корпорация Майкрософт рекомендует только с помощью данных, не имеет доступа к часто поскольку decomrpess выполняется медленнее, чем с помощью обычного сжатия COLUMNSTORE.

В следующем примере перестраивается кластеризованный индекс columnstore в целях применения архивного сжатия, затем показано, как удалить архивное сжатие. Конечным результатом становится использование только сжатия columnstore.

A. Перестроение индекса

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

Б. Перестроение всех индексов по таблице и указание параметров

В нижеследующем примере указывается ключевое слово ALL . Это приводит к перестроению всех индексов, связанных с таблицей Production.Product базы данных AdventureWorks2012. Указываются три параметра.

Примечание
Применяется к: SQL Server 2008 через SQL Server 2020 и База данных SQL Azure.

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

Применяется к: SQL Server 2014 через SQL Server 2020 и База данных SQL Azure.

Г. Реорганизация индекса со сжатием данных LOB

В следующем примере показано, как реорганизовать единственный кластеризованный индекс в базе данных AdventureWorks2012. Поскольку индекс содержит тип данных LOB на конечном уровне, инструкция также подвергает сжатию все страницы, в которых содержатся данные больших объектов. Следует отметить, что указывать параметр WITH (LOB_COMPACTION) не требуется, так как значение по умолчанию — ON.

Д. Установка параметров для индекса

В следующем примере задается несколько параметров индекса AK_SalesOrderHeader_SalesOrderNumber в базе данных AdventureWorks2012.

Применяется к: SQL Server 2008 через SQL Server 2020 и База данных SQL Azure.

Е. Отключение индекса

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

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

В следующем примере отключается ограничение PRIMARY KEY путем отключения индекса ПЕРВИЧНОГО ключа в AdventureWorks2012 базы данных. Ограничение FOREIGN KEY в базовой таблице автоматически отключается, и выводится предупредительное сообщение.

Результирующий набор возвращает это предупреждающее сообщение.

Warning: Foreign key ‘FK_EmployeeDepartmentHistory_Department_DepartmentID’

on table ‘EmployeeDepartmentHistory’ referencing table ‘Department’

was disabled as a result of disabling the index ‘PK_Department_DepartmentID’.

З. Включение ограничений

В следующем примере активируются ограничения PRIMARY KEY и FOREIGN KEY, снятые в примере Е.

Ограничение PRIMARY KEY активируется путем перестройки индекса PRIMARY KEY.

Затем активируется ограничение FOREIGN KEY.

И. Перестроение секционированного индекса

В следующем примере перестраивается единственная секция с номером 5 секционированного индекса IX_TransactionHistory_TransactionDate в базе данных AdventureWorks2012. Секция 5 перестраивается в сети, 10 минут времени ожидания для блокировки с низким приоритетом применяется отдельно к каждой полученной блокировке операции перестроения индекса. Если в течение этого времени не удается получить блокировку для завершения перестроения индекса, инструкция по перестроению прерывается.

Применяется к: SQL Server 2014 через SQL Server 2020 и База данных SQL Azure.

К. Изменение настроек сжатия индекса

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

Дополнительные примеры сжатия данных, в разделе сжатие данных.

Oracle CREATE ALTER INDEX

Платформа Oracle позволяет с помощью инструкции CREATE INDEX создавать индексы по таблицам, секционированным таблицам, кластерам и индекс-таблицам (index-organized tables), а также скалярным атрибутам объектов объектных таблиц (typed table) и столбцам вложенных таблиц. Платформа Oracle также позволяет использовать несколько типов индексов, в том числе обычные иерархические (B-tree) индексы, индексы на основе битовых карт (BITMAP) (используются для столбцов, в которых каждое значение повторяется 100 и более раз), секционированные индексы, индексы, связанные с функцией (основанные на выражении, а не на значении в столбце), и предметные индексы (domain index).

Имена индексов Oracle должны быть уникальны в пределах схемы, а не только в пределах таблицы, с которой они связаны.

Платформа Oracle также поддерживает инструкцию ALTER INDEX. Она используется для изменения или перестройки существующего индекса без его удаления и повторного создания.

Синтаксис инструкции CREATE INDEX в Oracle следующий.

CREATE [UNIQUE | BITMAP] INDEX имя_индекса

тип_индекса [PARALLEL [int] | NOPARALLEL] [PARAMETERS С значения’)>] | CLUSTER имя_кластера |

FROM имя_таблицы WHERE условие [LOCAL секционирование]> [

GLOBAL секционирование>] [параметры_физических_атрибутов] []

Синтаксис инструкции ALTER INDEX следующий.

ALTER INDEX имя_индекса

<| UNUSABLE | RENAME TO новое_имя_индекса COALESCE] [NO]MONITORING USAGE | UPDATE BLOCK REFERENCES |

PARAMETERS (‘параметры_00С1’) | параметры_изменения_секционирования_индекса | параметры_перестройки |

[DEALLOCATE UNUSED [KEEP int [К | M | G | T]]

[ALLOCATE EXTENT ([SIZE int [К | M | G | T]] [DATAFILE ‘имя_файла’]

Где предложения, не входящие в стандарт ANSI, таковы:

BITMAP

Вместо индексирования каждой строки для каждого значения индекса создается битовая карта. Битовые карты лучше всего использовать для таблиц с небольшим числом конкурентных запросов, например таблиц с высокой интенсивностью чтения. Индексы на основе битовых карт несовместимы с индексами с глобальным секционированием, предложением INDEXTYPE и индекс-таблицами (index-organized table) без связи с таблицей соответствия (mapped table).

ASC | DESC

Определяет расположение значений индекса в восходящем (ASQ или нисходящем (DESQ порядке. Если предложение опущено, по умолчанию принимается ASC. Однако помните, что Oracle считает индексы с предложением DESC индексами, основанными на функции, так что между индексами с предложением ASC и индексами с предложением DESC есть некоторые функциональные различия. Предложения ASC и DESC нельзя использовать совместно с предложением INDEXTYPE. Предложение DESC игнорируется при использовании индексов на основе битовых карг (BITMAP).

INDEXTYPE IS munjuidenca [PARAMETERS (‘значения’)]

Создается индекс определенного пользователем типа тип_индекса. Предметные индексы (domain index) требуют, чтобы пользовательский тип уже существовал (обращайтесь к разделу «Инструкция CREATE/ALTER TYPE»). Если для пользовательского типа требуются аргументы, их можно передать с помощью предложения PARAMETERS. При желании можно параллелизировать создание типизированного индекса с помощью предложения PARALLEL, которое подробно рассматривается ниже.

CLUSTER имя_кластера

Объявляется кластерный индекс с указанием существующего имени_клаетера. В Oracle кластерный индекс физически совмещает две таблицы, которые часто опрашиваются по одинаковым столбцам, обычно столбцам первичного и внешнего ключей. (Кластеры создаются специфической для Oracle командой CREATE CLUSTER.) Таблицы и столбцы в кластерном индексе не нужно объявлять, поскольку таблицы и индексированные столбцы уже объявлялись в ранее выполненной команде CREATE CLUSTER.

Дополнительная информация по теме

Особенности использования инструкций CREATE ALTER INDEX в DB2 примеры написания предложений

Особенности использования инструкций CREATE INDEX и ALTER INDEX в базах на платформах MySQL

Еще некоторые особенности использования инструкций CREATE INDEX, ALTER INDEX в базах Oracle

Особенности индексирования при использования инструкций CREATE INDEX, ALTER INDEX в базах Oracle

T-SQL Кучи, кластеризованные индексы и некластеризованные индексы

Кучи, кластеризованные индексы и некластеризованные индексы

Рассмотрим теорию индексов.

• SQL Server хранит данные на страницах размером 8 килобайт – 8,060 байт

• Страницы принадлежащие объекту(например таблице) связаны в двунаправленный список

• Первые 8 страниц «объекта» хранятся в смешанных участках. После этого данные хранятся только в унифицированных участках.

• 8 страниц группируются в «участки». Смешаные участки хранят данные из разных «объектов». Унифицированные участки хранят данные одного «объекта»

• SQL Server использует страницы именуемыми — «карты размещения индексов» (Index Allocation Map) или кратко — IAM для определения страниц принадлежащих «объекту»

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

Кучи подходят для хранения небольшого количества данных.

Существуют два типа индексов: кластеризованные и некластеризованные.

Кластеризованный индекс хранит в своих узлах-листьях реальные строки данных.

Некластеризованный индекс является вспомогательной структурой, которая указывает данные в таблице

Кластеризованные индексы

  • В SQL Server индексы организованы в виде сбалансированных деревьев. Каждая страница в сбалансированном дереве индекса называется узлом индекса.

Верхний узел сбалансированного дерева называется корневым. Узлы нижнего уровня индекса называются конечными. Все уровни индекса между корневыми и конечными узлами называются промежуточными. В кластеризованном индексе конечные узлы содержат страницы данных базовой таблицы. На страницах индекса корневого и промежуточного узлов находятся строки индекса. Каждая строка индекса содержит ключевое значение и указатель либо на страницу промежуточного уровня сбалансированного дерева, либо на строку данных на конечном уровне индекса. Страницы на каждом уровне связаны в двунаправленный список.На схеме — кластеризованный индекс выглядит в виде B-дерева, где хранятся реальные строки данных таблицы в отсортированном порядке в узлах-листьях.

Т.Е. данные будут храниться так:

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

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

Недостатком является то, что доступ к таблице всегда происходит через индекс, что приводит к дополнительной нагрузке на SQL Server. Доступ к данным начинается с корневого узла и проходит через индекс, пока не будет достигнут узел-лист, содержащий нужные данные. При больших объемах данных создается много узлов-листьев, соответственно количество уровней индекса, необходимых для поддержки столь большого числа узлов-листьев — увеличивается, и приводит к увеличению количества операций ввода-вывода для перемещения от корневого узла к узлу-листу.

Т.к. кластеризованный индекс хранит реальные данные, нельзя создать более одного кластеризованного индекса в таблице.

Некластеризованные индексы

Некластеризованный индекс не содержит реальных данных таблицы в узлах-листьях. Узлы-листья содержат один из двух типов информации о местоположении строк данных.

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

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

Возможно создать до 249 некластеризованных индексов на одну таблицу.

Понравилась статья? Поделиться с друзьями:
Кодинг, CSS и SQL