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


Содержание

Создаем индекс в таблице базы данных 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 можно посмотреть здесь

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

CREATE INDEX — данный запрос используется для создания индексов в таблице.

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

Пользователь не может увидеть индексы, они просто используются для ускорения поиска/запроса.

Замечание: Обновления таблицы с индексами занимает большее время, чем обновления таблицы без индексов (потому что индексы тоже нуждаются в обновлении). Итак, рекомендуется создавать только один индекс на таблицу (одна колонка) в которой будет часто производится поиск.

Синтаксис SQL CREATE INDEX

Создания индекса в таблице. Повторяющиеся значения допускаются:

Синтаксис SQL CREATE UNIQUE INDEX

Создания индекса в таблице. Повторяющиеся значения не допускаются:

Замечание: Синтаксис для создания индексов не одинаковый в базах данных. Поэтому: Проверьте синтаксис перед созданием индексов в базе данных.

Пример CREATE INDEX

SQL запрос создает индекс с именем «PIndex» в колонке «LastName» таблице «Persons»:

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

CREATE INDEX

Описание

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

Важно: Столбцы типов BLOB и массивы не могут быть индексированы.

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

ASC и DESC определяют порядок, в котором индекс сортирован. Для более быстрого ответа на запросы, используйте индексы, порядок которых соответствует предложению ORDER BY запроса. И ASC, и DESC индекс может быть создан на одном и том же столбце или наборе столбцов, чтобы обратится к данным в различном порядке.

Совет: Для улучшения эффективности индекса, use SET STATISTICS to recompute index selectivity, or rebuild the index by making it inactive, then active with sequential calls to ALTER INDEX.

Синтаксис

Аргумент Описание
UNIQUE Предотвращает вставку или модификацию данных, приводящих к дублированию значений в индексированных столбцах.
ASC[ENDING] Сортирует столбцы в возрастающем порядке, это порядок по умолчанию если ничего не определено.
DESC[ENDING] Сортирует столбцы в убывающем порядке.
index Уникальное имя для индекса.
table Имя таблицы для которой индекс определен.
col Столбец в индексируемой таблице.

Примеры

Следующая инструкция создает уникальный индекс:

Следующая инструкция создает убывающий индекс:

Следующая инструкция создает индекс, состоящий из двух столбцов:

SQL/CREATE INDEX

CREATE INDEX — оператор языка SQL, который используется для создания индексов.

Перед оператором INDEX указывается тип индекса, после — его название. Все эти данные не обязательны, то есть тогда тип индекса будет по умолчанию, а его именем будет название таблицы (название индекса по умолчанию выбирается по усмотрению конкретной СУБД).

Синтаксис SQL CREATE INDEX

Создание индекса типа по-умолчанию в таблице. Повторяющиеся значения допускаются:

Создаётся индекс index_name для столбца column_name таблицы table_name .

Синтаксис SQL CREATE UNIQUE INDEX Создания уникального индекса в таблице. Повторяющиеся значения не допускаются (тип индекса — уникальный):

Создание индексов вместе с созданием таблицы:

Создаётся таблица some_table_name с колонками id , name , password и индексами index_name , index_id и безымянным индексом для столбца password .

Примеры CREATE INDEX

SQL запрос создает индекс с именем «PIndex» для колонки «LastName» таблицы «Persons»:

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

SQL CREATE INDEX Инструкция

Инструкция по созданию индекса SQL

Инструкция CREATE INDEX используется для создания индексов в таблицах.

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

Note: Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). So, only create indexes on columns that will be frequently searched against.

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

Создает индекс для таблицы. Разрешены повторяющиеся значения:

Создание уникального синтаксиса индекса

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

Note: The syntax for creating indexes varies among different databases. Therefore: Check the syntax for creating indexes in your database.

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

Приведенная ниже инструкция SQL создает индекс с именем «идкс_ластнаме» в столбце «Фамилия» в таблице «лица»:

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

Удалить инструкцию index


Инструкция DROP INDEX используется для удаления индекса в таблице.

SQL – Индексы

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

Индекс помогает ускорить для запросов SELECT и предложения WHERE, но это замедляет ввод данных, с заявлениями UPDATE и INSERT. Индексы могут быть созданы или удалены без влияния на данные.

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

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

Команда CREATE INDEX

Основной синтаксис CREATE INDEX выглядит следующим образом:

Одноколоночные индексы

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

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

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

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

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

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

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

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

Неявные индексы – это индексы, которые автоматически создаются на сервере базы данных при создании объекта. Индексы автоматически создаются для первичного ключа и ограничения уникальности.

Команда DROP INDEX

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

Базовый синтаксис выглядит следующим образом:

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

Когда следует избегать индексов?

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

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

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

Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.

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

Индекс создается командой 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. После добавления этой конструкции база данных не будет генерировать данные повторного выполнения для операции перестройки индекса.

Индексы PostgreSQL: полное руководство

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

  • операции select , update и delete , в которых присутствует условие where ;
  • операция join и подобные ей (если индекс создан для полей, по которым происходит объединение);
  • некоторые случаи, где присутствует ORDER BY .

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

В самом базовом случае команда создания индекса выглядит следующим образом:

Чтобы создать уникальный индекс, который не позволяет создавать несколько записей с одинаковым значением в поле, нужно добавить модификатор UNIQUE . Уникальность поддерживают только B-tree индексы.

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

Есть пара моментов, о которых стоит помнить:

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

Для удаления индекса необходимо воспользоваться запросом вида:

Пересоздание индексов (реиндекс)

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

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

PostgreSQL позволяет создавать индексы нескольких типов: B-tree, GIN, GiST и HASH. Каждый из них использует свой алгоритм работы и подходит для определенных запросов. По умолчанию создаются индексы типа B-tree, как поддерживающий наиболее часто используемые операции. Чтобы создать индекс другого типа надо указать его явно, добавив модификатор USING type .

Рассмотрим подробнее каждый тип.

B-tree

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

  • операторы сравнения > , , = , >= , , BETWEEN и IN ;
  • условия пустоты IS NULL и IS NOT NULL ;
  • операторы поиска подстроки LIKE и

, если искомая строка закреплена в начале шаблона (например str_1 LIKE ‘search%’ );
регистронезависимые операторы поиска подстроки ILIKE и

* . Но только в том случае, если искомая строка начинается с символа, который одинаков и в верхнем и в нижнем регистре (например числа)`.

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

Вот список типов полей и индексируемых операторов для них. Значение операторов для каждого конкретного типа можно найти в официальной документации.

    типы box, circle и polygon — операторы && , &> , & , & , >> , , , , @> , @ , |&> , |>> ,

= ;

  • типы inet и cidr — операторы && , >> , >>= , > , >= , <> , , , , , = ;
  • тип point — операторы >> , >^ , , , , , ,

    = ;

  • тип tsquery — операторы , @> ;
  • тип tsvector — оператор @@ ;
  • все типы range — операторы && , &> , & , >> , , , -|- , = , @> , @> .
  • GIN индексы применимы к составным типам, работа с которыми осуществляется с помощью ключей. Это массивы, jsonb и tsvector. Как и GiST индексы, они могут реализовать один из нескольких алгоритмов. И стандартный набор можно так же расширить, установив модели.

    Ниже приведен список типов полей и индексируемых операторов для них. Значение операторов для каждого конкретного типа можно найти в официальной документации.

    • массивы — операторы && , , = , @> ;
    • тип jsonb — операторы ? , ?& , ?| , @> ;
    • тип tsvector — операторы @@ и @@@ .

    Так же GIN индекс может быть создан только для определенных полей jsonb поля.

    Hash индексы могут использоваться только если проиндексированное поле участвует в сравнении (только оператор = ). Этот индекс так же не используется в условиях IS NULL и IS NOT NULL . По ряду причин этот тип индексов не рекомендуется к использованию.

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

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

    Максимальное количество полей в индексе — 32. Это значение может быть изменено, если вы самостоятельно компилируете PostgreSQL из исходников. Но на практике индексы, состоящие больше чем из трех полей, практически не применяются.

    Составными могут быть индексы только B-tree, GiST и GIN типов. Составные индексы могут использоваться, когда в условии участвуют все поля, входящие в индекс. Так же составной индекс может быть использован, если условие касается только одного или нескольких проиндексированных полей. Для каждого типа есть свои особенности.

    B-tree

    B-tree индекс может быть использован, когда условие включает в себя любые входящие в него поля. Но наиболее полезен он в том случае, если в условии задействовано ведущее (первое в списке) поле.

    GiST индекс может использоваться в тех же условиях, что и B-tree. Помимо этого эффективность индекса сильно снижается, если условие по ведущему полю отсекает мало значение. Нужно стараться так составлять индекс, чтобы наиболее строгое ограничение применялось к ведущему полю.

    Составной GIN индекс одинаково эффективен при использовании любого входящего в него поля не зависимо от его расположения. Это отличает его от B-tree и GiST индексов, где условие по ведущему полю сильно желательно.

    Частичные индексы

    Иногда может возникать ситуация, что индекс требуются не для всех записей в таблице. Например у нас есть таблица users , у которой есть поле is_active типа boolean . Записей со значением поля true намного меньше, чем с false . Но логично, что основная часть запросов идет к активным пользователям. В этом случае будет разумно построить индекс только для них. PostgreSQL предоставляет такую возможность.

    Для создания частичного индекса надо добавить модификатор WHERE в запрос на создание.

    Вот запрос на создание частичного индекса для поля users.rating с условием is_active = true :

    Составные и уникальные индексы тоже могут быть частичными.

    Функциональные индексы

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

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

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

    Коментарии

    Thank you for comment!
    Ваше сообщение будет доступно после проверки.

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

    Use the CREATE INDEX statement to create an index on:

    One or more columns of a table, a partitioned table, an index-organized table, or a cluster

    One or more scalar typed object attributes of a table or a cluster

    A nested table storage table for indexing a nested table column

    An index is a schema object that contains an entry for each value that appears in the indexed column(s) of the table or cluster and provides direct, fast access to rows. The maximum size of a single index entry is dependent on the block size of the database.

    Oracle Database supports several types of index:

    Normal indexes. (By default, Oracle Database creates B-tree indexes.)

    Bitmap indexes , which store rowids associated with a key value as a bitmap.

    Partitioned indexes , which consist of partitions containing an entry for each value that appears in the indexed column(s) of the table.

    Function-based indexes , which are based on expressions. They enable you to construct queries that evaluate the value returned by an expression, which in turn may include built-in or user-defined functions.

    Domain indexes , which are instances of an application-specific index of type indextype .

    Oracle Database Concepts for a discussion of indexes


    Oracle Database Reference for more information about the limits related to index size

    To create an index in your own schema, one of the following conditions must be true:

    The table or cluster to be indexed must be in your own schema.

    You must have the INDEX object privilege on the table to be indexed.

    You must have the CREATE ANY INDEX system privilege.

    To create an index in another schema, you must have the CREATE ANY INDEX system privilege. Also, the owner of the schema to contain the index must have either the UNLIMITED TABLESPACE system privilege or space quota on the tablespaces to contain the index or index partitions.

    To create a function-based index, in addition to the prerequisites for creating a conventional index, if the index is based on user-defined functions, then those functions must be marked DETERMINISTIC . A function-based index is executed with the credentials of the index owner, so the index owner must have the EXECUTE object privilege on the function.

    To create a domain index in your own schema, in addition to the prerequisites for creating a conventional index, you must also have the EXECUTE object privilege on the indextype. If you are creating a domain index in another user’s schema, then the index owner also must have the EXECUTE object privilege on the indextype and its underlying implementation type. Before creating a domain index, you should first define the indextype.

    ( The XMLIndex_parameters_clause is documented in Oracle XML DB Developer’s Guide .)

    (The XMLIndex_parameters_clause is documented in Oracle XML DB Developer’s Guide .)

    ( read_only_clause and indexing_clause : not supported in table_index_clause , partitioning_storage_clause::= )

    ( TABLESPACE SET , table_compression , inmemory_clause , and ilm_clause not supported with CREATE INDEX , index_compression::= , LOB_partitioning_storage::= )

    ( TABLESPACE SET : not supported with CREATE INDEX )

    Specify UNIQUE to indicate that the value of the column (or columns) upon which the index is based must be unique.

    Restrictions on Unique Indexes

    Unique indexes are subject to the following restrictions:

    You cannot specify both UNIQUE and BITMAP .

    You cannot specify UNIQUE for a domain index.

    «Unique Constraints» for information on the conditions that satisfy a unique constraint

    Specify BITMAP to indicate that index is to be created with a bitmap for each distinct key, rather than indexing each row separately. Bitmap indexes store the rowids associated with a key value as a bitmap. Each bit in the bitmap corresponds to a possible rowid. If the bit is set, then it means that the row with the corresponding rowid contains the key value. The internal representation of bitmaps is best suited for applications with low levels of concurrent transactions, such as data warehousing.

    Oracle does not index table rows in which all key columns are null except in the case of bitmap indexes. Therefore, if you want an index on all rows of a table, then you must either specify NOT NULL constraints for the index key columns or create a bitmap index.

    Restrictions on Bitmap Indexes

    Bitmap indexes are subject to the following restrictions:

    You cannot specify BITMAP when creating a global partitioned index.

    You cannot create a bitmap secondary index on an index-organized table unless the index-organized table has a mapping table associated with it.

    You cannot specify both UNIQUE and BITMAP .

    You cannot specify BITMAP for a domain index.

    A bitmap index can have a maximum of 30 columns.

    Oracle Database Concepts and Oracle Database SQL Tuning Guide for more information about using bitmap indexes

    CREATE TABLE for information on mapping tables

    Specify the schema to contain the index. If you omit schema , then Oracle Database creates the index in your own schema.

    Specify the name of the index to be created. The name must satisfy the requirements listed in Database Object Naming Rules.

    Use the cluster_index_clause to > schema , then Oracle Database assumes the cluster is in your current schema. You cannot create a cluster index for a hash cluster.

    Specify the table on which you are defining the index. If you do not qualify table with schema , then Oracle Database assumes the table is contained in your own schema.

    You create an index on a nested table column by creating the index on the nested table storage table. Include the NESTED_TABLE_ID pseudocolumn of the storage table to create a UNIQUE index, which effectively ensures that the rows of a nested table value are distinct.

    You can perform DDL operations (such as ALTER TABLE , DROP TABLE , CREATE INDEX ) on a temporary table only when no session is bound to it. A session becomes bound to a temporary table by performing an INSERT operation on the table. A session becomes unbound to the temporary table by issuing a TRUNCATE statement or at session termination, or, for a transaction-specific temporary table, by issuing a COMMIT or ROLLBACK statement.

    Restrictions on the table_index_clause

    This clause is subject to the following restrictions:

    If index is locally partitioned, then table must be partitioned.

    If table is index-organized, then this statement creates a secondary index. The index contains the index key and the logical row >REVERSE for this secondary index, and the combined size of the index key and the logical rowid should be less than the block size.

    If table is a temporary table, then index will also be temporary with the same scope (session or transaction) as table . The following restrictions apply to indexes on temporary tables:

    The only part of index_properties you can specify is index_attributes .

    Within index_attributes , you cannot specify the physical_attributes_clause , the parallel_clause , the logging_clause , or TABLESPACE .

    You cannot create a domain index or a partitioned index on a temporary table.

    You cannot create an index on an external table.

    CREATE TABLE and Oracle Database Concepts for more information on temporary tables

    Specify a correlation name (alias) for the table upon which you are building the index.

    This alias is required if the index_expr references any object type attributes or object type methods. See «Creating a Function-based Index on a Type Method: Example» and «Indexing on Substitutable Columns: Examples» .

    For index_expr , specify the column or column expression upon which the index is based.

    You can create multiple indexes on the same set of columns, column expressions, or both if the following conditions are met:

    The indexes are of different types, use different partitioning, or have different uniqueness properties.

    Only one of the indexes is VISIBLE at any given time.

    Oracle Database Administrator’s Guide for more information on creating multiple indexes

    Specify the name of one or more columns in the table. A bitmap index can have a maximum of 30 columns. Other indexes can have as many as 32 columns. These columns define the index key .

    If a unique index is local nonprefixed (see local_partitioned_index ), then the index key must contain the partitioning key.

    Oracle Database VLDB and Partitioning Guide for information on prefixed and nonprefixed indexes

    You can create an index on a scalar object attribute column or on the system-defined NESTED_TABLE_ID column of the nested table storage table. If you specify an object attribute column, then the column name must be qualified with the table name. If you specify a nested table column attribute, then it must be qualified with the outermost table name, the containing column name, and all intermediate attribute names leading to the nested table column attribute.

    When you create an index on a column or expression with a declared or derived named collation other than BINARY , or a declared or derived pseudo-collation USING_NLS_SORT_CI or USING_NLS_SORT_AI , the database creates a functional index on the function NLSSORT . See Oracle Database Globalization Support Guide for more information.

    Creating an Index on an Extended Data Type Column

    If column is an extended data type column, then you may receive a «maximum key length exceeded» error when attempting to create the index. The maximum key length for an index varies depending on the database block size and some additional index metadata stored in a block. For example, for databases that use the Oracle standard 8K block size, the maximum key length is approximately 6400 bytes.

    To work around this situation, you must shorten the length of the values you want to index, using one of the following methods:

    Create a function-based index to shorten the values stored in the extended data type column as part of the expression used for the index definition.

    Create a virtual column to shorten the values stored in the extended data type column as part of the expression used for the virtual column definition and build a normal index on the virtual column. Using a virtual column also enables you to leverage functionality for regular columns, such as collecting statistics and using constraint and triggers.

    For both methods you can use either the SUBSTR or STANDARD_HASH function to shorten the values of the extended data type column to build an index. These methods have the following advantages and disadvantages:

    Use the SUBSTR function to return a substring, or prefix, of column that is an acceptable length for the index key. This type of index can be used for equality, IN-list, and range predicates on the original column without the need to specify the SUBSTR column as part of the predicate. Refer to SUBSTR for more information.

    Using the STANDARD_HASH function is likely to create an index that is more compact than the substring-based index and may result in fewer unnecessary index accesses. This type of index can be used for equality and IN-list predicates on the original column without the need to specify the SUBSTR column as part of the predicate. Refer to STANDARD_HASH for more information.

    The following example shows how to create a function-based index on an extended data type column:

    For n, specify a prefix length that is large enough to differentiate between values in column .

    The following example shows how to create a virtual column for an extended data type column, and then create an index on the virtual column:

    «Extended Data Types» for more information on extended data types

    Restrictions on Index Columns

    The following restrictions apply to index columns:

    You cannot create an index on columns or attributes whose type is user-defined, LONG , LONG RAW , LOB, or REF , except that Oracle Database supports an index on REF type columns or attributes that have been defined with a SCOPE clause.

    Only normal (B-tree) indexes can be created on encrypted columns, and they can only be used for equality searches.

    Specify an expression built from columns of table , constants, SQL functions, and user-defined functions. When you specify column_expression , you create a function-based index .

    Name resolution of the function is based on the schema of the index creator. User-defined functions used in column_expression are fully name resolved during the CREATE INDEX operation.

    After creating a function-based index, collect statistics on both the index and its base table using the DBMS_STATS package. Such statistics will enable Oracle Database to correctly decide when to use the index.

    Function-based unique indexes can be useful in defining a conditional unique constraint on a column or combination of columns. Refer to «Using a Function-based Index to Define Conditional Uniqueness: Example» for an example.

    Oracle Database PL/SQL Packages and Types Reference for more information on the DBMS_STATS package

    Notes on Function-based Indexes

    The following notes apply to function-based indexes:

    When you subsequently query a table that uses a function-based index, Oracle Database will not use the index unless the query filters out nulls. However, Oracle Database will use a function-based index in a query even if the columns specified in the WHERE clause are in a different order than their order in the column_expression that defined the function-based index.


    If the function on which the index is based becomes inval >DISABLED . Queries on a DISABLED index fail if the optimizer chooses to use the index. DML operations on a DISABLED index fail unless the index is also marked UNUSABLE and the parameter SKIP_UNUSABLE_INDEXES is set to true . Refer to ALTER SESSION for more information on this parameter.

    If a public synonym for a function, package, or type is used in column_expression , and later an actual object with the same name is created in the table owner’s schema, then Oracle Database disables the function-based index. When you subsequently enable the function-based index using ALTER INDEX . ENABLE or ALTER INDEX . REBUILD , the function, package, or type used in the column_expression continues to resolve to the function, package, or type to which the public synonym originally pointed. It will not resolve to the new function, package, or type.

    If the definition of a function-based index generates internal conversion to character data, then use caution when changing NLS parameter settings. Function-based indexes use the current database settings for NLS parameters. If you reset these parameters at the session level, then queries using the function-based index may return incorrect results. Two exceptions are the collation parameters ( NLS_SORT and NLS_COMP ). Oracle Database handles the conversions correctly even if these have been reset at the session level.

    Oracle Database cannot convert data in all cases, even when conversion is explicitly requested. For example, an attempt to convert the string ‘105 lbs’ from VARCHAR2 to NUMBER using the TO_NUMBER function fails with an error. Therefore, if column_expression contains a data conversion function such as TO_NUMBER or TO_DATE , and if a subsequent INSERT or UPDATE statement includes data that the conversion function cannot convert, then the index will cause the INSERT or UPDATE statement to fail.

    If column_expression contains a datetime format model, then the function-based index expression defining the column may contain format elements that are different from those specified. For example, define a function-based index using the yyyy datetime format element:

    Query the ALL_IND_EXPRESSIONS view to see that the function-based index expression defining the column uses the syyyy datetime format element:

    Restrictions on Function-based Indexes

    Function-based indexes are subject to the following restrictions:

    The value returned by the function referenced in column_expression is subject to the same restrictions as are the index columns of a B-tree index. Refer to «Restrictions on Index Columns» .

    Any user-defined function referenced in column_expression must be declared as DETERMINISTIC .

    For a function-based globally partitioned index, the column_expression cannot be the partitioning key.

    The column_expression can be any of the forms of expression described in Column Expressions.

    All functions must be specified with parentheses, even if they have no parameters. Otherwise Oracle Database interprets them as column names.

    Any function you specify in column_expression must return a repeatable value. For example, you cannot specify the SYSDATE or USER function or the ROWNUM pseudocolumn.

    Use ASC or DESC to indicate whether the index should be created in ascending or descending order. Indexes on character data are created in ascending or descending order of the character values in the database character set.

    Oracle Database treats descending indexes as if they were function-based indexes. As with other function-based indexes, the database does not use descending indexes until you first analyze the index and the table on which the index is defined. See the column_expression clause of this statement.

    Ascending unique indexes allow multiple NULL values. However, in descending unique indexes, multiple NULL values are treated as duplicate values and therefore are not permitted.

    Restriction on Ascending and Descending Indexes

    You cannot specify either of these clauses for a domain index. You cannot specify DESC for a reverse index. Oracle Database ignores DESC if index is bitmapped or if the COMPATIBLE initialization parameter is set to a value less than 8.1.0.

    Specify the optional index attributes.

    Use the physical_attributes_clause to establish values for physical and storage characteristics for the index.

    If you omit this clause, then Oracle Database sets PCTFREE to 10 and INITRANS to 2.

    Restriction on Index Physical Attributes

    You cannot specify the PCTUSED parameter for an index.

    physical_attributes_clause and storage_clause for a complete description of these clauses

    For tablespace , specify the name of the tablespace to hold the index, index partition, or index subpartition. If you omit this clause, then Oracle Database creates the index in the default tablespace of the owner of the schema containing the index.

    For a local index, you can specify the keyword DEFAULT in place of tablespace . New partitions or subpartitions added to the local index will be created in the same tablespace(s) as the corresponding partitions or subpartitions of the underlying table.

    The index_compression clauses let you enable or disable index compression for the index. Specify the COMPRESS clause of prefix_compression to enable prefix compression for the index, specify the COMPRESS ADVANCED clause of advanced_index_compression to enable advanced index compression for the index, or specify the NOCOMPRESS clause of either prefix_compression or advanced_index_compression to disable compression for the index. The default is NOCOMPRESS .

    If you want to use compression for a partitioned index, then you must create the index with compression enabled at the index level. You can subsequently enable and disable the compression setting for individual partitions of such a partitioned index. You can also enable and disable compression when rebuilding individual partitions. You can modify an existing nonpartitioned index to enable or disable compression only when rebuilding the index.

    Specify COMPRESS to enable prefix compression , also known as key compression, which eliminates repeated occurrence of key column values. Use integer to specify the prefix length (number of prefix columns to compress). You can specify prefix compression for indexes that are nonunique or unique indexes of at least two columns.

    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 the number of key columns.

    Specify this clause to enable advanced index compression . Advanced index compression improves compression ratios significantly while still providing efficient access to indexes. Therefore, advanced index compression works well on all supported indexes, including those indexes that are not good candidates for prefix compression.

    COMPRESS ADVANCED LOW — This level compresses the index less than the HIGH level, but prov >COMPRESS ADVANCED LOW , the database must be at 12 . 1 . 0 or higher compatibility level.

    COMPRESS ADVANCED HIGH — This level compresses the index more than the LOW level, but prov >COMPRESS ADVANCED HIGH , the database must be at 12 . 2 . 0 or higher compatibility level.

    If you omit the LOW and HIGH keywords, then the default is HIGH .

    Restrictions on Index Compression

    The following restrictions apply to index compression:

    You cannot specify prefix compression or advanced index compression for a bitmap index.

    You cannot specify advanced index compression for index-organized tables.

    Oracle Database Administrator’s Guide for more information on prefix compression and advanced index compression

    You can specify this clause only when creating an index on a partitioned table. Specify INDEXING FULL to create a full index . Specify INDEXING PARTIAL to create a partial index . The default is INDEXING FULL .

    A full index includes all partitions in the underlying table, regardless of their indexing properties. A partial index includes only partitions in the underlying table with an indexing property of ON .

    If a partial index is a local partitioned index, then index partitions that correspond with table partitions with an indexing property of ON are marked USABLE . Index partitions that correspond with table partitions with an indexing property of OFF are marked UNUSABLE .

    If the underlying table is a composite-partitioned table, then the preceding conditions for index partitions and table partitions apply instead to index subpartitions and table subpartitions.

    Restrictions on Partial Indexes

    Partial indexes are subject to the following restrictions:

    The underlying table of a partial index cannot be a nonpartitioned table.

    Unique indexes cannot be partial indexes. This applies to indexes created with the CREATE UNIQUE INDEX statement and indexes that are implicitly created when you specify a unique constraint on one or more columns.

    CREATE TABLE indexing_clause for information on the indexing property

    By default, Oracle Database sorts indexes in ascending order when it creates the index. You can specify NOSORT to indicate to the database that the rows are already stored in the database in ascending order, so that Oracle Database does not have to sort the rows when creating the index. If the rows of the indexed column or columns are not stored in ascending order, then the database returns an error. For greatest savings of sort time and space, use this clause immediately after the initial load of rows into a table. If you specify neither of these keywords, then SORT is the default.

    Restrictions on NOSORT

    This parameter is subject to the following restrictions:

    You cannot specify REVERSE with this clause.

    You cannot use this clause to create a cluster index partitioned or bitmap index.

    You cannot specify this clause for a secondary index on an index-organized table.

    Specify REVERSE to store the bytes of the index block in reverse order, excluding the rowid.

    Restrictions on Reverse Indexes

    Reverse indexes are subject to the following restrictions:

    You cannot specify NOSORT with this clause.

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

    Use this clause to specify whether the index is visible or invisible to the optimizer. An invisible index is maintained by DML operations, but it is not be used by the optimizer during queries unless you explicitly set the parameter OPTIMIZER_USE_INVISIBLE_INDEXES to TRUE at the session or system level.

    To determine whether an existing index is visible or invisible to the optimizer, you can query the VISIBILITY column of the USER_ , DBA_ , ALL_INDEXES data dictionary views.

    Oracle Database Administrator’s Guide for more information on this feature

    Specify whether the creation of the index will be logged ( LOGGING ) or not logged ( NOLOGGING ) in the redo log file. This setting also determines whether subsequent Direct Loader (SQL*Loader) and direct-path INSERT operations against the index are logged or not logged. LOGGING is the default.

    If index is nonpartitioned, then this clause specifies the logging attribute of the index.

    If index is partitioned, then this clause determines:

    The default value of all partitions specified in the CREATE statement, unless you specify the logging_clause in the PARTITION description clause

    The default value for the segments associated with the index partitions

    The default value for local index partitions or subpartitions added implicitly during subsequent ALTER TABLE . ADD PARTITION operations

    The logging attribute of the index is independent of that of its base table.

    If you omit this clause, then the logging attribute is that of the tablespace in which it resides.

    logging_clause for a full description of this clause

    Oracle Database VLDB and Partitioning Guide for more information about logging and parallel DML

    Specify ONLINE to indicate that DML operations on the table will be allowed during creation of the index.

    Restrictions on Online Index Building

    Online index building is subject to the following restrictions:

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

    You can specify ONLINE for a bitmap index or a cluster index as long as COMPATIBLE is set to 10 or higher.

    You cannot specify ONLINE for a conventional index on a UROWID column.

    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.

    Oracle Database Concepts for a description of online index building and rebuilding


    Specify the parallel_clause if you want creation of the index to be parallelized.

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

    Index Partitioning Clauses

    Use the global_partitioned_index clause and the local_partitioned_index clauses to partition index .

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

    The global_partitioned_index clause lets you specify that the partitioning of the index is user defined and is not equipartitioned with the underlying table. By default, nonpartitioned indexes are global indexes.

    You can partition a global index by range or by hash. In both cases, you can specify up to 32 columns as partitioning key columns. The partitioning column list must specify a left prefix of the index column list. If the index is defined on columns a , b , and c , then for the columns you can specify (a , b , c) , or ( a , b) , or ( a , c) , but you cannot specify ( b , c) or (c) or ( b , a ). If you specify a partition name, then it must conform to the rules for naming schema objects and their parts as described in «Database Object Naming Rules» . If you omit the partition names, then Oracle Database assigns names of the form SYS_P n .

    GLOBAL PARTITION BY RANGE

    Use this clause to create a range-partitioned global index. Oracle Database will partition the global index on the ranges of values from the table columns you specify in the column list.

    GLOBAL PARTITION BY HASH

    Use this clause to create a hash-partitioned global index. Oracle Database assigns rows to the partitions using a hash function on values in the partitioning key columns.

    The CREATE TABLE clause hash_partitions for information on the two methods of hash partitioning and «Creating a Hash-Partitioned Global Index: Example»

    Restrictions on Global Partitioned Indexes

    Global partitioned indexes are subject to the following restrictions:

    The partitioning key column list cannot contain the ROWID pseudocolumn or a column of type ROWID .

    The only property you can specify for hash partitions is tablespace storage. Therefore, you cannot specify LOB or varray storage clauses in the partitioning_storage_clause of individual_hash_partitions .

    You cannot specify the OVERFLOW clause of hash_partitions_by_quantity , as that clause is valid only for index-organized table partitions.

    In the partitioning_storage_clause , you cannot specify table_compression or the inmemory_clause , but you can specify index_compression .

    If your enterprise has or will have databases using different character sets, then use caution when partitioning on character columns. The sort sequence of characters is not identical in all character sets.

    Oracle Database Globalization Support Guide for more information on character set support

    Use this clause to describe the indiv > partition , then Oracle Database generates a name with the form SYS_P n .

    For VALUES LESS THAN ( value_list ), specify the noninclusive upper bound for the current partition in a global index. The value list is a comma-delimited, ordered list of literal values corresponding to the column list in the global_partitioned_index clause. Always specify MAXVALUE as the value of the last partition.

    If the index is partitioned on a DATE column, and if the date format does not specify the first two digits of the year, then you must use the TO_DATE function with a 4-character format mask for the year. The date format is determined implicitly by NLS_TERRITORY or explicitly by NLS_DATE_FORMAT . Refer to Oracle Database Globalization Support Guide for more information on these initialization parameters.

    The local_partitioned_index clauses let you specify that the index is partitioned on the same columns, with the same number of partitions and the same partition bounds as table . For composite-partitioned tables, this clause lets you specify that the index is subpartitioned on the same columns, with the same number of subpartitions and the same subpartition bounds as table . Oracle Database automatically maintains local index partitioning as the underlying table is repartitioned.

    If you specify only the keyword LOCAL and do not specify a subclause, then Oracle Database creates each index partition in the same tablespace as its corresponding table partition and assigns it the same name as its corresponding table partition. If table is a composite-partitioned table, then Oracle Database creates each index subpartition in the same tablespace as its corresponding table subpartition and assigns it the same name as its corresponding table subpartition.

    If you specify a partition name, then it must conform to the rules for naming schema objects and their parts as described in «Database Object Naming Rules» . If you omit a partition name, then Oracle Database generates a name that is consistent with the corresponding table partition. If the name conflicts with an existing index partition name, then the database uses the form SYS_P n .

    This clause lets you specify the names and attributes of index partitions on a range-partitioned table. If you specify this clause, then the number of PARTITION clauses must be equal to the number of table partitions, and in the same order.

    You cannot specify prefix compression for an index partition unless you have specified prefix compression for the index.

    For more information on the USABLE and UNUSABLE clauses, refer to USABLE | UNUSABLE.

    The on_list_partitioned_table clause is >on_range_partitioned_table .

    This clause lets you specify names and tablespace storage for index partitions on a hash-partitioned table.

    If you specify any PARTITION clauses, then the number of these clauses must be equal to the number of table partitions. You can optionally specify tablespace storage for one or more indiv >STORE IN clause, then the database stores each index partition in the same tablespace as the corresponding table partition.

    The STORE IN clause lets you specify one or more tablespaces across which Oracle Database will distribute all the index hash partitions. The number of tablespaces need not equal the number of index partitions. If the number of index partitions is greater than the number of tablespaces, then the database cycles through the names of the tablespaces.

    For more information on the USABLE and UNUSABLE clauses, refer to USABLE | UNUSABLE.

    This clause lets you specify the name and attributes of index partitions on a composite-partitioned table.

    The STORE IN clause is val >STORE IN clause in the index_subpartition_clause .

    For range-range, range-list, and list-list composite-partitioned tables, you can specify default attributes for the range or list subpartitions in the PARTITION clause. You can overr >SUBPARTITION clause of the index_subpartition_clause .

    You cannot specify prefix compression for an index partition unless you have specified prefix compression for the index.

    For more information on the USABLE and UNUSABLE clauses, refer to USABLE | UNUSABLE.

    This clause lets you specify names and tablespace storage for index subpartitions in a composite-partitioned table.

    The STORE IN clause is val >SUBPARTITION clause is valid for all subpartition types.

    If you specify any SUBPARTITION clauses, then the number of those clauses must be equal to the number of table subpartitions. If you specify a subpartition name, then it must conform to the rules for naming schema objects and their parts as described in «Database Object Naming Rules» . If you omit subpartition , then the database generates a name that is consistent with the corresponding table subpartition. If the name conflicts with an existing index subpartition name, then the database uses the form SYS_SUBP n .

    The number of tablespaces need not equal the number of index subpartitions. If the number of index subpartitions is greater than the number of tablespaces, then the database cycles through the names of the tablespaces.

    If you do not specify tablespace storage for subpartitions either in the on_comp_partitioned_table clause or in the index_subpartition_clause , then Oracle Database uses the tablespace specified for index . If you also do not specify tablespace storage for index , then the database stores the subpartition in the same tablespace as the corresponding table subpartition.

    For more information on the USABLE and UNUSABLE clauses, refer to CREATE INDEX . USABLE | UNUSABLE.

    Use the domain_index_clause to indicate that index is a domain index, which is an instance of an application-specific index of type indextype .

    Creating a domain index requires a number of preceding operations. You must first create an implementation type for an indextype. You must also create a functional implementation and then create an operator that uses the function. Next you create an indextype, which associates the implementation type with the operator. Finally, you create the domain index using this clause. Refer to Extended Examples, which contains an example of creating a simple domain index, including all of these operations.

    In the index_expr (in table_index_clause ), specify the table columns or object attributes on which the index is defined. You can define multiple domain indexes on a single column only if the underlying indextypes are different and the indextypes support a disjoint set of user-defined operators.

    Restrictions on Domain Indexes

    Domain indexes are subject to the following restrictions:

    The index_expr (in table_index_clause ) can specify only a single column, and the column cannot be of data type REF , varray, nested table, LONG , or LONG RAW .

    You cannot create a bitmap or unique domain index.

    You cannot create a domain index on a temporary table.

    You can create local domain indexes on only range-, list-, hash-, and interval-partitioned tables, with one exception: You cannot create a local domain index on an automatic list-partitioned table.

    Domain indexes can be created only on table columns declared with collation BINARY , USING_NLS_COMP , USING_NLS_SORT , or USING_NLS_SORT_CS . See Oracle Database Globalization Support Guide for more information.

    For indextype , specify the name of the indextype. This name should be a valid schema object that has already been created.

    If you have installed Oracle Text, then you can use various built-in indextypes to create Oracle Text domain indexes. For more information on Oracle Text and the indexes it uses, refer to Oracle Text Reference .

    Use this clause to specify that the index is a local index on a partitioned table.

    The PARTITIONS clause lets you specify names for the index partitions. The number of partitions you specify must match the number of partitions in the base table. If you omit this clause, then the database creates the partitions with system-generated names of the form SYS_P n .

    The PARAMETERS clause lets you specify the parameter string specific to an individual partition. If you omit this clause, then the parameter string associated with the index is also associated with the partition.

    Use the parallel_clause to parallelize creation of the domain index. For a nonpartitioned domain index, Oracle Database passes the explicit or default degree of parallelism to the ODCIIndexCreate cartridge routine, which in turn establishes parallelism for the index. For local domain indexes, this clause causes the index partitions to be created in parallel.

    Oracle Database Data Cartridge Developer’s Guide for complete information on the Oracle Data Cartridge Interface (ODCI) routines

    In the PARAMETERS clause, specify the parameter string that is passed uninterpreted to the appropriate ODCI indextype routine. The maximum length of the parameter string is 1000 characters.

    When you specify this clause at the top level of the syntax, the parameters become the default parameters for the index partitions. If you specify this clause as part of the local_domain_index_clause , then you override any default parameters with parameters for the individual partition.

    After the domain index is created, Oracle Database invokes the appropriate ODCI routine. If the routine does not return successfully, then the domain index is marked FAILED . The only operations supported on an failed domain index are DROP INDEX and (for non-local indexes) REBUILD INDEX .

    Oracle Database Data Cartridge Developer’s Guide for information on the Oracle Data Cartridge Interface (ODCI) routines

    The XMLIndex_clause lets you define an XMLIndex index, typically on a column contain XML data. An XMLIndex index is a type of domain index designed specifically for the domain of XML data.

    This clause lets you specify information about the path table and about the secondary indexes corresponding to the components of XMLIndex . This clause also lets you specify information about the structured component of the index. The maximum length of the parameter string is 1000 characters.

    When you specify this clause at the top level of the syntax, the parameters become the parameters of the index and the default parameters for the index partitions. If you specify this clause as part of the local_xmlindex_clause clause, then you override any default parameters with parameters for the individual partition.

    Oracle XML DB Developer’s Guide for the syntax and semantics of the XMLIndex_parameters_clause , as well as detailed information about the use of XMLIndex

    Use the bitmap_join_index_clause to define a bitmap join index . A bitmap join index is defined on a single table. For an index key made up of dimension table columns, it stores the fact table row >fact table , and the tables with which this table is joined are commonly referred to as dimension tables . However, a star schema is not a requirement for creating a join index.

    In the ON clause, first specify the fact table, and then inside the parentheses specify the columns of the dimension tables on which the index is defined.

    In the FROM clause, specify the joined tables.

    In the WHERE clause, specify the join condition.

    If the underlying fact table is partitioned, then you must also specify one of the local_partitioned_index clauses (see local_partitioned_index ).

    Restrictions on Bitmap Join Indexes

    In addition to the restrictions on bitmap indexes in general (see BITMAP), the following restrictions apply to bitmap join indexes:

    You cannot create a bitmap join index on a temporary table.

    No table may appear twice in the FROM clause.

    You cannot create a function-based join index.

    The dimension table columns must be either primary key columns or have unique constraints.

    If a dimension table has a composite primary key, then each column in the primary key must be part of the join.


    You cannot specify the local_partitioned_index clause unless the fact table is partitioned.

    A bitmap join index definition can only reference columns with collation BINARY , USING_NLS_COMP , USING_NLS_SORT , or USING_NLS_SORT_CS . For any of these collations, index keys are collated and the join condition is evaluated using the BINARY collation. See Oracle Database Globalization Support Guide for more information.

    Oracle Database Data Warehousing Guide for information on fact and dimension tables and on using bitmap indexes in a data warehousing environment

    You can specify the USABLE and UNUSABLE keywords:

    For an index, in the CREATE INDEX statement

    For an index partition, in the on_range_partitioned_table , on_list_partitioned_table , on_hash_partitioned_table , and on_comp_partitioned_table clauses

    For an index subpartition, in the index_subpartition_clause

    For nonpartitioned indexes, specify UNUSABLE to create an index in an unusable state. An unusable index must be rebuilt, or dropped and re-created, before it can be used. Specify USABLE to create an index in a usable state. USABLE is the default.

    For partitioned indexes, specify USABLE or UNUSABLE as follows:

    If you specify UNUSABLE for the index, then all index partitions are marked UNUSABLE .

    If you specify USABLE for the index, then all index partitions are marked USABLE .

    If you do not specify USABLE or UNUSABLE for the index, then all index partitions are marked USABLE . The exception is a local partial index. If you specify the LOCAL and INDEXING PARTIAL clauses, and do not specify USABLE or UNUSABLE , then each index partition is marked USABLE if the indexing property of its corresponding table partition is ON , or UNUSABLE if the indexing property of its corresponding table partition is OFF .

    You can overr >USABLE or UNUSABLE for a specific index partition.

    If the underlying table is a composite-partitioned table, then the preceding conditions for index partitions and table partitions apply instead to index subpartitions and table subpartitions.

    After you create a partitioned index, you can choose to rebuild specific index partitions or subpartitions to make them USABLE . Doing so can be useful if you want to maintain indexes only on some index partitions or subpartitions—for example, if you want to enable index access for new partitions but not for old partitions.

    When an index, or some partitions or subpartitions of an index, are created UNUSABLE , no segment is allocated for the unusable object. The unusable index or index partition consumes no space in the database.

    If an index, or some partitions or subpartitions of the index, are marked UNUSABLE , then the index will be cons >USABLE . Therefore, the query cannot contain any bind variables.

    Restrictions on USABLE | UNUSABLE

    The following restrictions apply when marking an index USABLE or UNUSABLE :

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

    Unusable indexes or index partitions will still have a segment under the following conditions:

    The index (or index partition) is owned by SYS , SYSTEM , PUBLIC , OUTLN , or XDB

    The index (or index partition) is stored in dictionary-managed tablespaces

    The global partitioned or nonpartitioned index on a partitioned table becomes unusable due to a partition maintenance operation

    This clause lets you control when the database inval >ALTER INDEX statement. Refer to < DEFERRED | IMMEDIATE >INVAL >ALTER INDEX for the full semantics of this clause.

    General Index Examples

    Creating an Index: Example

    The following statement shows how the sample index ord_customer_ix on the customer_id column of the sample table oe.orders was created:

    Compressing an Index: Example

    To create the ord_customer_ix_demo index with the COMPRESS clause, you might issue the following statement:

    The index will compress repeated occurrences of customer_id column values.

    Creating an Index in NOLOGGING Mode: Example

    If the sample table orders had been created using a fast parallel load (so all rows were already sorted), then you could issue the following statement to quickly create an index.

    Creating a Cluster Index: Example

    To create an index for the personnel cluster, which was created in «Creating a Cluster: Example» , issue the following statement:

    No index columns are specified, because cluster indexes are automatically built on all the columns of the cluster key. For cluster indexes, all rows are indexed.

    Creating an Index on an XMLType Table: Example

    The following example creates an index on the area element of the xwarehouses table (created in «XMLType Table Examples» ):

    Such an index would greatly improve the performance of queries that select from the table based on, for example, the square footage of a warehouse, as shown in this statement:

    Function-Based Index Examples

    The following examples show how to create and use function-based indexes.

    Creating a Function-Based Index: Example

    The following statement creates a function-based index on the employees table based on an uppercase evaluation of the last_name column:

    See the «Prerequisites» for the privileges and parameter settings required when creating function-based indexes.

    To increase the likelihood that Oracle Database will use the index rather than performing a full table scan, be sure that the value returned by the function is not null in subsequent queries. For example, this statement will use the index, unless some other condition exists that prevents the optimizer from doing so:

    Without the WHERE clause, Oracle Database may perform a full table scan.

    In the next statements showing index creation and subsequent query, Oracle Database will use index income_ix even though the columns are in reverse order in the query:

    Creating a Function-Based Index on a LOB Column: Example

    The following statement uses the text_length function to create a function-based index on a LOB column in the sample pm schema. See Oracle Database PL/SQL Language Reference for the example that creates this function. The example selects rows from the sample table print_media where that CLOB column has fewer than 1000 characters.

    Creating a Function-based Index on a Type Method: Example

    This example entails an object type rectangle containing two number attributes: length and width . The area() method computes the area of the rectangle.

    Now, if you create a table rect_tab of type rectangle , you can create a function-based index on the area() method as follows:

    You can use this index efficiently to evaluate a query of the form:

    Using a Function-based Index to Define Conditional Uniqueness: Example

    The following statement creates a unique function-based index on the oe.orders table that prevents a customer from taking advantage of promotion ID 2 («blowout sale») more than once:

    The objective is to remove from the index any rows where the promotion_id is not equal to 2. Oracle Database does not store in the index any rows where all the keys are NULL . Therefore, in this example, both customer_id and promotion_id are mapped to NULL unless promotion_ >customer_id value.

    Partitioned Index Examples

    Creating a Range-Partitioned Global Index: Example

    The following statement creates a global prefixed index cost_ix on the sample table sh.sales with three partitions that divide the range of costs into three groups:

    Creating a Hash-Partitioned Global Index: Example

    The following statement creates a hash-partitioned global index cust_last_name_ix on the sample table sh.customers with four partitions:

    Creating an Index on a Hash-Partitioned Table: Example

    The following statement creates a local index on the category_id column of the hash_products partitioned table (which was created in «Hash Partitioning Example» ). The STORE IN clause immediately following LOCAL indicates that hash_products is hash partitioned. Oracle Database will distribute the hash partitions between the tbs1 and tbs2 tablespaces:

    The creator of the index must have quota on the tablespaces specified. See CREATE TABLESPACE for examples that create tablespaces tbs_01 and tbs_02 .

    Creating an Index on a Composite-Partitioned Table: Example

    The following statement creates a local index on the composite_sales table, which was created in «Composite-Partitioned Table Examples» . The STORAGE clause specifies default storage attributes for the index. However, this default is overr >q3_2000 and q4_2000 , because separate TABLESPACE storage is specified.

    The creator of the index must have quota on the tablespaces specified. See CREATE TABLESPACE for examples that create tablespaces tbs_02 and tbs_03 .

    Bitmap Index Examples

    The following creates a bitmap index on the table oe.hash_products , which was created in «Hash Partitioning Example» :

    Because hash_products is a partitioned table, the bitmap join index must be locally partitioned. In this example, the user must have quota on tablespaces specified. See CREATE TABLESPACE for examples that create tablespaces tbs_01 , tbs_02 , tbs_03 , and tbs_04 .

    The next series of statements shows how one might create a bitmap join index on a fact table using a join with a dimension table.

    Indexes on Nested Tables: Example

    The sample table pm.print_media contains a nested table column ad_textdocs_ntab , which is stored in storage table textdocs_nestedtab . The following example creates a unique index on storage table textdocs_nestedtab :

    Including pseudocolumn NESTED_TABLE_ID ensures distinct rows in nested table column ad_textdocs_ntab .

    Indexing on Substitutable Columns: Examples

    You can build an index on attributes of the declared type of a substitutable column. In addition, you can reference the subtype attributes by using the appropriate TREAT function. The following example uses the table books , which is created in «Substitutable Table and Column Examples» . The statement creates an index on the salary attribute of all employee authors in the books table:

    The target type in the argument of the TREAT function must be the type that added the attribute being referenced. In the example, the target of TREAT is employee_t , which is the type that added the salary attribute.

    If this condition is not satisfied, then Oracle Database interprets the TREAT function as any functional expression and creates the index as a function-based index. For example, the following statement creates a function-based index on the salary attribute of part-time employees, assigning nulls to instances of all other types in the type hierarchy.

    You can also build an index on the type-discriminant column underlying a substitutable column by using the SYS_TYPEID function.

    Oracle Database uses the type-discriminant column to evaluate queries that involve the IS OF type condition. The cardinality of the typeid column is normally low, so Oracle recommends that you build a bitmap index in this situation.

    The following statement creates a bitmap index on the typeid of the author column of the books table:

    Oracle Database PL/SQL Language Reference to see the creation of the type hierarchy underlying the books table

    Илон Маск рекомендует:  Жизнь и счастье. Мысли, высказывания которые мне кажутся интересными.
    Понравилась статья? Поделиться с друзьями:
    Кодинг, CSS и SQL