Реверсивный индекс oracle. Мониторинг использования индекса

CREATE INDEX . The following statement creates an index named emp_ename for the ename column of the emp table:

CREATE INDEX emp_ename ON emp(ename) TABLESPACE users STORAGE (INITIAL 20K NEXT 20k PCTINCREASE 75);

Notice that several storage settings and a tablespace are explicitly specified for the index. If you do not specify storage options (such as INITIAL and NEXT) for an index, the default storage options of the default or specified tablespace are automatically used.

Creating a Unique Index Explicitly

Indexes can be unique or non-unique. Unique indexes guarantee that no two rows of a table have duplicate values in the key column (or columns). Non-unique indexes do not impose this restriction on the column values.

Create a new temporary tablespace using the CREATE TABLESPACE or CREATE TEMPORARY TABLESPACE statement.

Use the TEMPORARY TABLESPACE option of the ALTER USER statement to make this your new temporary tablespace.

Create the index using the CREATE INDEX statement.

Drop this tablespace using the DROP TABLESPACE statement. Then use the ALTER USER statement to reset your temporary tablespace to your original temporary tablespace.

Using this procedure can avoid the problem of expanding your usual, and usually shared, temporary tablespace to an unreasonably large size that might affect future performance.

Creating an Index Online

You can create and rebuild indexes online. This enables you to update base tables at the same time you are building or rebuilding indexes on that table. You can perform DML operations while the index build is taking place, but DDL operations are not allowed. Parallel execution is not supported when creating or rebuilding an index online.

The following statements illustrate online index build operations:

CREATE INDEX emp_name ON emp (mgr, emp1, emp2, emp3) ONLINE;

Keep in mind that the time that it takes on online index build to complete is proportional to the size of the table and the number of concurrently executing DML statements. Therefore, it is best to start online index builds when DML activity is low.

Creating a Function-Based Index

Function-based indexes facilitate queries that qualify a value returned by a function or expression. The value of the function or expression is precomputed and stored in the 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 . Also, you just have the EXECUTE object privilege on any user-defined function(s) used in the function-based index if those functions are owned by another user.

Additionally, to use a function-based index:

The table must be analyzed after the index is created.

The query must be guaranteed not to need any NULL values from the indexed expression, since NULL values are not stored in indexes.

CREATE INDEX stores the timestamp of the most recent function used in the function-based index. This timestamp is updated when the index is validated. When performing tablespace point-in-time recovery of a function-based index, if the timestamp on the most recent function used in the index is newer than the timestamp stored in the index, then the index is marked invalid. You must use the ANALYZE INDEX...VALIDATE STRUCTURE statement to validate this index.

To illustrate a function-based index, consider the following statement that defines a function-based index (area_index) defined on the function area(geo) :

CREATE INDEX area_index ON rivers (area(geo));

In the following SQL statement, when area(geo) is referenced in the WHERE clause, the optimizer considers using the index area_index .

SELECT id, geo, area(geo), desc FROM rivers WHERE Area(geo) >5000;

Table owners should have EXECUTE privileges on the functions used in function-based indexes.

Because a function-based index depends upon any function it is using, it can be invalidated when a function changes. If the function is valid, you can use an ALTER INDEX...ENABLE statement to enable a function-based index that has been disabled. The ALTER INDEX...DISABLE statement lets you disable the use of a function-based index. Consider doing this if you are working on the body of the function.

An alternative to creating a function-based index is to add a virtual column to the target table and index the virtual column. See "About Tables" for more information.

Creating a Key-Compressed Index

Creating an index using key compression enables you to eliminate repeated occurrences of key column prefix values.

Key compression breaks an index key into a prefix and a suffix entry. Compression is achieved by sharing the prefix entries among all the suffix entries in an index block. This sharing can lead to huge savings in space, allowing you to store more keys for each index block while improving performance.

Key compression can be useful in the following situations:

You have a non-unique index where ROWID is appended to make the key unique. If you use key compression here, the duplicate key is stored as a prefix entry on the index block without the ROWID . The remaining rows become suffix entries consisting of only the ROWID .

You have a unique multicolumn index.

You enable key compression using the COMPRESS clause. The prefix length (as the number of key columns) can also be specified to identify how the key columns are broken into a prefix and suffix entry. For example, the following statement compresses duplicate occurrences of a key in the index leaf block:

CREATE INDEX emp_ename ON emp(ename) TABLESPACE users COMPRESS 1;

The COMPRESS clause can also be specified during rebuild. For example, during rebuild you can disable compression as follows:

ALTER INDEX emp_ename REBUILD NOCOMPRESS;

Creating an Invisible Index

Beginning with Release 11g , you can create invisible indexes. An invisible index is an index that is ignored by the optimizer unless you explicitly set the OPTIMIZER_USE_INVISIBLE_INDEXES initialization parameter to TRUE at the session or system level. Making an index invisible is an alternative to making it unusable or dropping it. Using invisible indexes, you can do the following:

Test the removal of an index before dropping it.

Use temporary index structures for certain operations or modules of an application without affecting the overall application.

Unlike unusable indexes, an invisible index is maintained during DML statements.

To create an invisible index, use the SQL statement CREATE INDEX with the INVISIBLE clause. The following statement creates an invisible index named emp_ename for the ename column of the emp table:

CREATE INDEX emp_ename ON emp(ename) TABLESPACE users STORAGE (INITIAL 20K NEXT 20k PCTINCREASE 75) INVISIBLE ;

Index (Индексы)

Типы индексов:
Индексы в виде B-дерева (B-tree) – самые распространенные и используются по умолчанию
Кластерные индексы в виде B-дерева – определяются специально для кластера
Индексы хэш-кластера – определяются специально для хэш-кластера
Глобальные и локальные индексы – относятся к секционированным таблицам и индексам
Индексы с инвертированным ключом – полезны в среде Oracle Real Application Cluster
Битовые индексы – компактные, подходят для столбцов с небольшим набором значений
Индексы на базе функций – содержат заранее вычисленные значения функции/выражения
Индексы домена – зависят от приложения или картриджа

B*Tree
Структура индекса выглядит так:

Блоки самого нижнего уровня в индексе, которые называют листовыми вершинами (leaf blocks ), содержат все проиндексированные ключи и идентификаторы строк (rowid на схеме), ссылающиеся на соответствующие строки. Промежуточные блоки над листовыми вершинами называют блоками ветвления (branch blocks ). Они используются для переходов по структуре. Самый верхний блок называется корневым (root block ), он относится к группе branch blocks.

Индексы состоят из одного или более уровней branch blocks и одного уровня leaf blocks.

Index height - высота индекса, кол-во уровней индекса
blevel - высота кол-ва уровней branch blocks

Пример
Создадим новую пустую таблицу и создадим индекс по ней. Индекс будет состоять из одного пустого блока (он будет одновременно и root и leaf блоком). Index height = 1, blevel = 0.

Когда root блок полностью заполнится указателями, произойдёт его разделение на два branch блока, над которыми будет root блок с указателями на эти два блока. Теперь Index height = 3, blevel = 2. Как на картинке ниже:

По мере заполнения разделяются(split) листовые блоки, затем branch блоки и root блок. И так далее.

Интересно отметить, что листовые блоки фактически образут двухсвязный список. Как только найдено "начало" среди листовых вершин, т.е. первое значение, очень легко просматривать значения по порядку (это называют также просмотром диапазона по индексу, index range scan ). Проходить по структуре индекса больше не нужно; мы просто переходим по листовым вершинам.

Одно из свойств В*-дерева состоит в том, что все листовые блоки должны быть на одном уровне, если точнее, разница по высоте между ветвями дерева не может быть больше 1.
Уровень листовых блоков называют также высотой дерева .

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

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

Создать индекс
create index t_idx on t(owner,object_type,object_name);

- Как средство доступа к строкам в таблице. Индекс читается, чтобы добраться до строки в таблице. Так имеет смысл обращаться к очень небольшой части строк таблицы.
- Как средство ответа на запрос. Индекс содержит достаточно информации, чтобы дать полный ответ на запрос — к таблице вообще не придется обращаться. Индекс будет использоваться как уменьшенная версия таблицы.

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

Когда следует использовать Bitmap индексы:
Для данных с небольшим количеством уникальных значений. Это данные, для которых при делении количества уникальных значений в строках на общее количество строк получается небольшое число (близкое к нулю).

Создать bitmap индекс
create bitmap index empno_bmx on t(empno);

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

Создать индекс по ф-ции
create index emp_upper_idx on emp (upper(ename));

Application domain indexes(прикладные индексы)
Прикладные индексы позволяют создавать новые, еще не существующие в базе данных, типы индексов.

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

Избирательность индекса

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

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

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

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

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

CREATE INDEX имя_индекса {ON

{имя_таблицы ({столбец | выражение} [, …]) [{INDEXTYPE IS

тип_индекса | NOPARALLEL] | CLUSTER имя_кластера |

FROM имя_таблицы WHERE условие } [{LOCAL секционирование |

GLOBAL секционирование}] [параметры_физических_атрибутов] [{LOGGING | NOLOGGING}]

[{TABLESPACE имя_табличного_пространства DEFAULT}] [{COMPRESS int | NOCOMPRESS}] [{NOSORT |

SORT}] [{PARALLEL | NOPARALLEL}]

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

ALTER INDEX имя_индекса

{{ENABLE | DISABLE} | UNUSABLE | RENAME TO новое_имя_индекса COALESCE] MONITORING USAGE | UPDATE BLOCK REFERENCES |

PARAMETERS ("параметры_00С1") | параметры_изменения_секционирования_индекса | параметры_перестройки |

)] ] [{PARALLEL | NOPARALLEL}] [{LOGGING | NOLOGGING}]

[параметры_физических_атрибутов]}

Где предложения, не входящие в стандарт ANSI, таковы:

Вместо индексирования каждой строки для каждого значения индекса создается битовая карта. Битовые карты лучше всего использовать для таблиц с небольшим числом конкурентных запросов, например таблиц с высокой интенсивностью чтения. Индексы на основе битовых карт несовместимы с индексами с глобальным секционированием, предложением INDEXTYPE и индекс-таблицами (index-organized table) без связи с таблицей соответствия (mapped table).

ASC | DESC

Определяет расположение значений индекса в восходящем (ASQ или нисходящем (DESQ порядке. Если предложение опущено, по умолчанию принимается ASC. Однако помните, что Oracle считает индексы с предложением DESC индексами, основанными на функции, так что между индексами с предложением ASC и индексами с предложением DESC есть некоторые функциональные различия. Предложения ASC и DESC нельзя использовать совместно с предложением INDEXTYPE. Предложение DESC игнорируется при использовании индексов на основе битовых карг (BITMAP).

INDEXTYPE IS munjuidenca

Создается индекс определенного пользователем типа тип_индекса. Предметные индексы (domain index) требуют, чтобы пользовательский тип уже существовал (обращайтесь к разделу «Инструкция CREATE/ALTER TYPE»). Если для пользовательского типа требуются аргументы, их можно передать с помощью предложения PARAMETERS. При желании можно параллелизировать создание типизированного индекса с помощью предложения PARALLEL, которое подробно рассматривается ниже.

CLUSTER имя_кластера

Объявляется кластерный индекс с указанием существующего имени_клаетера. В Oracle кластерный индекс физически совмещает две таблицы, которые часто опрашиваются по одинаковым столбцам, обычно столбцам первичного и внешнего ключей. (Кластеры создаются специфической для Oracle командой CREATE CLUSTER.) Таблицы и столбцы в кластерном индексе не нужно объявлять, поскольку таблицы и индексированные столбцы уже объявлялись в ранее выполненной команде CREATE CLUSTER.

В Oracle имеется несколько типов индексов:

· древовидные индексы (В-деревья).

· хешированные индексы (hash ).

· индексы на основе битовых карт или битовые индексы (bitmap ).

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

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

Оператор создания индекса использует следующий синтаксис:

СREATE INDEX имя_индекса

ON имя_таблицы (имя_столбца, [¼])

Для удаления индекса используется команда

DROP INDEX <ИМЯ> (удалить)

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

ALTER INDEX<ИМЯ> REBUILD (перестроить индекс)

ALTER INDEX<ИМЯ> UNUSABLE (отключить индекс на время,

чтобы снова включить обратно при помощи REBUILD)

B-деревья

Видимо, наиболее популярным подходом к организации индексов в базах данных является использование техники B-деревьев. B-дерево содержит по одному индексному элементу для каждой строки таблицы, в которой имеется непустое (NOT NULL) индексное значение. С точки зрения внешнего логического представления B-дерево - это сбалансированное сильно ветвистое дерево во внешней памяти (рис.5.3).

Рис. 5.3 - Древовидный индекс по текстовому столбцу

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

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

При этом выдерживаются следующие свойства:

ключ(1) <= ключ(2) <= ... <= ключ(n);

в странице дерева Nm находятся ключи k со значениями ключ(m) <= k <= ключ(m+1).

Листовая страница обычно содержит значение индекса и идентификаторы строк (ROWID) и имеет следующую структуру:

Листовая страница обладает следующими свойствами:

· ключ(1) < ключ(2) < ... < ключ(t);

· сп(r) - упорядоченный список идентификаторов кортежей (tid), включающих значение ключ(r);

· листовые страницы связаны одно- или двунаправленным списком.

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

Основной "изюминкой" B-деревьев является автоматическое поддержание свойства сбалансированности. Рассмотрим, как это делается при выполнении операций занесения и удаления записей.

При занесение новой записи выполняется:

· Поиск листовой страницы. Фактически, производится обычный поиск по ключу. Если в B-дереве не содержится ключ с заданным значением, то будет получен номер страницы, в которой ему надлежит содержаться, и соответствующие координаты внутри страницы.

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

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

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

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

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

При удалении записи выполняются следующие действия:

· Поиск записи по ключу. Если запись не найдена, то удалять ничего не нужно.

· Реальное удаление записи в буфере, в который прочитана соответствующая листовая страница.

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

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

· Чтобы устранить возможность доступа от корня к освобожденной странице, нужно удалить соответствующее значение ключа и ссылку на освобожденную страницу из внутренней страницы - ее предка. При этом может возникнуть потребность в слиянии этой страницы с ее левым или правыми братьями и т.д.

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

Как видно, при выполнении операций вставки и удаления свойство сбалансированности B-дерева сохраняется, а внешняя память расходуется достаточно экономно.

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

· упреждающие расщепления, т.е. расщепления страницы не при ее переполнении, а несколько раньше, когда степень заполненности страницы достигает некоторого уровня;

· переливания, т.е. поддержание равновесного заполнения соседних страниц;

· слияния 3-в-2, т.е. порождение двух листовых страниц на основе содержимого трех соседних.

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

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

На В-деревьях для извлечения данных по запросу может использоваться механизм быстрого полного просмотра (fast full scan ). Этот механизм дает существенные преимущества, если все запрошенные из конкретной таблицы данные могут быть получены только из индекса. При быстром полном просмотре эффективный многоблочный ввод/вывод, обычно применяемый для полных просмотров таблиц, используется для прочтения всех листовых блоков В-дерева. Поскольку число листовых блоков индекса, скорее всего, намного меньше, чем блоков данных в таблице, для выполнения запроса требуется просмотреть меньшее число блоков. Поэтому просмотр индекса совершится значительно быстрее, чем полный просмотр таблицы, хотя иногда неравномерное распределение ключей снижает эффективность быстрого полного просмотра, поскольку требуется просмотреть большее число листовых блоков (содержащих малое или вообще нулевое число элементов). При этом следует учитывать наличие или отсутствие в таблице пустых значений, которые, как было сказано выше, в индекс не заносятся.

В-деревья можно использовать для поиска данных, как по условиям равенства, так и по условиям неравенства. Это единственный тип индексов, который можно использовать для предикатов неравенства: LIKE, BETWEEN, “>”, “>=”, “<”, “<=”. Исключение представляет случай использования предиката LIKE при сравнении с шаблоном вида ‘%выражение’ или ‘_выражение ’. В-деревья хранят только непустые значения ключей, так что можно построить разреженное В-дерево.

У индексов есть две задачи: соблюдать выполнение первичных ключей и уникальных ограничений, и увеличивать производительность. Стратегия по созданию индексов сильно влияет на производительность приложения. Нет четкого ограничения кто ответствене за создание индексов. Когда бизнес-аналитики составляют бизнес-требования к системе которые будут выполнены как создание ограничений – они влияют на индексы. Администратор будет наблюдать за выполнением запросов и давать рекомендации по созданию индексов. Разработчик именно тот кто лучше всех понимает что происходит в коде и природе данных – тоже влияет на стратегию создания индексов.

Почему индексы необходимы

Индексы это часть механизма ограничений (constraint). Если столбец (или группа столбцов) помечены как первичной ключ таблица, то каждый раз когда вставляется строка в таблицу, Oracle необходимо проверить что не существует строки с такими значениями. Если у таблицы нет индекса дял столбцов – единственный способ проверить это это вычитать всю таблицу. Это может быть приемлимо если в таблице всего несколько строк, но дял таблиц, содержащих тысячи миллионов (или миллиардов) строк это займёт очень много времени и неприемлимо. Индекс позволяет практически мгновенно получить доступ к значениям ключа и проверка на существование происходит моментально. Когда определяется первичный ключ Oracle создаст индекс для столбца(ов) ключа если ещё не существует такого индекса.

Ограничение по уникальности (unique constraint) тоже требует создание индекса. Это ограничение отличается от первичного ключа тем что значение в столбцах ограничения по уникальности могут быть NULL в отличие от первичного ключа, но это не влияет на создание и исопльзование индекса. Внешний ключ (foreign key) соблюдается с помощью индексов, но обязательным является индекс только на родительской таблице. Внешний ключ дочерней таблицы зависит от столбца первичного ключа или уникального ключа родительской таблицы. Когда строка добавляется в дочернюю таблицу, Oracle будет использовать индекс родительской таблицы для проверки существует ли такое значение в родительной таблице или нет, перед тем как позволить записать данные. Как бы то ни было желательно всегда создавать индексы для столбцов дочерней таблицы используемых как внешние ключи из соображений производительности: DELETE для родительской таблицы будет гораздо б ыстрее если Oracle сможет использовать индекс для проверки существуют ли ещё строки в дочерней таблице с этим значением или нет.

Индексы критически важны для производительности. Когда выполняется команда SELECT с директивой WHERE, Oracle необходимо определить строки в таблице которые необходимо выбрать. Если не создано индексов для столбцов используемых в директиве WHERE, то единственным способом сделать это – это вычитать всю таблицу (full table scan).Full table scan проверяют все строки по очереди для поиска нужных значений. Если в таблицы хранятся миллиарды строк, это может занять несколько часов. Если существует индекс для использованного в WHERE столбца, Oracle может искать используя индекс. Индекс это отсортированный список ключей значений структурирвоанных таким образом чтобы операция поиска была очень быстрой. Каждая запись это сслыка на строку в таблице. Поиск строк используя индекс гораздо быстрее чем чтение всей таблицы если размер таблицы больше определённого размера и пропорция между данными которые нужны для запроса и всеми данными в таблице ниже определённого значения. Для маленьких таблиц, или где секция WHERE всё равно выберет большую часть строк из таблицы, полное чтение таблицы будет быстрее: вы можете (обычно) доверять Oracle при выборе решения использовать ли индекс. Это решение осуществляется на основании статистической информации собираемой о таблице и строках в ней.

Второй случай когда индексы могут увеличить производительность это сортировка. Команда SELECT c директивой ORDER BY, GROUP BY или ключевым словом UNION (и несколько других) обязана отсортировать строки в определённом порядке – если не создан индекс, который может вернуть строки без необходимости в сортировке (строки уже отсортированы).

И третий случай это объекдинение таблиц, но опять же у Oracle есть выбор: в зависимости от размера таблиц и наличия свободной памяти, может быть быстрее вычитать таблицы в память и объединять их чем использовать индексы. Метод nested loop join читает строки одной таблицы и использует индекс другой таблицы для поиска совпадений (это обычно нагружает диск). Hash join считывает таблицу в память, преобразует в хеш таблицу и использует специальный алгоритм для поиска совпадений — такая операция требует больше оперативной памяти и процессорного времени. Sort merge join сортиует таблицы по значениям столбца для объединения и затем объединяет их вместе – это компромисс между использованием диска, памятии процессора. Если нет индексов –Oracle сильно ограничен в способах объединения.

Indexes assist SELECT statements, and also any UPDATE, DELETE, or MERGE statements that use a WHERE clause-but they will slow down INSERT statements.

Oracle поддерживает несколько типов индексов с различными вариациями. Два типа, которые мы рассмотрим это B* Tree индекс, который является типом по умолчанию и bitmap индекс. Основное правило – индексы увеличивают производительность для чтения данных но замедляют при DML операциях. Это происходит потому что индексы нужно обновлять и поддерживать. Каждый раз когда строка записывается в таблицу, новый ключ должен быть вставлен в каждый индекс таблицы, что усиливает нагрузку на БД. Поэтому OLTP системы обычно используют минимальное количество индексов (возможно только необходимые для ограничений) а для OLAP систем создаётся столько индексов сколько нужно для быстроты выполнения.

B* Tree индексы (B*=balanced)

Индекс это древовидная (tree) структура. «Корень» (root) дерева содержит указатели на множество узлов второго уровня, которые в свою очередь могут хранить указатели на узлы третьего уровня и так далее. Глубина дерева определяется длинной ключа и количеством строк в таблице.

The B*Tree structure is very efficient. If the depth is greater than three or four, then either the index keys are very long or the table has billions of rows. If neither if these is the case, then the index is in need of a rebuild.

В листьях (узлы нижнего уровня) индекса хранятся значения столбца строк по порядку и указатель на строку. Также листья хранят ссылки на соседние листья. Таким образом чтобы выбрать строку если условие WHERE использует строгое равенство — Oracle исдёт по дереву в лист содержащий искомое значение и затем использует указатель для считывания строки.Если же используется нестрогое равенство (например LIKE, BETWEEN и т.д.) то вначале находится первая строка удовлетворяющая условию а затем считываются строки по порядку и переход между листьями осуществляется напрямую, без нового обхода по дереву.

Указатель на строку – это rowid. Rowid — это псевдостолбец закрытого формата, который имеет каждая строка в каждой таблице. Внутри значения зашифрован указатель на физический адрес строки. Так как rowid не является частью стандарта SQL то он не видим при написании обычных запросов. Но вы можете выбирать эти значения и использовать их при необходимости. Это отображено на рисунке 7-3.

Rowid для каждой строки полностью уникальный. Каждая строка во всей БД имеет свой уникальный rowid. Расшифровав rowid получаем физический адрес строки, и Oracle может рассчитать в каком файле и где внутри файла находится искомая строка.

B* Tree индексы очень эффективны для вычитки строк число которых невелико относительно всех строк таблицы и таблица достаточно большая. Рассмотрим запрос

select count(*) from employees where last_name between ‘A%’ and ‘Z%’;

При использовании такого условия в WHERE запрос вернёт все строки таблицы. Использование индекса при таком запросе будет значительно медленее чем чтение всей таблицы. И вообще – вся таблица это то что нужно в этом запросе. Другим примером будет настолько маленькая таблица где одна операция чтения считывает её полностью; тогда нет смысла считывать вначале индекс. Обычно говорят что запросы, результат которых предполагает вычитку более чем 2-4% данных в таблице обычно работают быстрее используя полное чтение таблицы. Особым случаем является значение NULL в столбце указанном в секции WHERE. Значение NULL не хранится в B* Tree индексах и запросы типа

select * from employees where last_name is null;

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

Мощность (кратность – количество уникальных значений) столбца велика и

Столбец используется в директивах WHERE и операциях объединения

Bitmap индексы

Во многих приложения природа данных и запросы таковы что использование B* Tree индексов не сильно помогает. Расммотрим пример. Есть таблица продаж, в которой набор данных о продажах в супермаркетах за год, которые нужно проанализировать в нескольких измерениях. На рисунке 7-4 показана простая диаграмма сущность-связь для четырёх измерений.

Мощность каждого измерения очень низкая. Преположим

Всего два измерения (DATE и PRODUCT) предполагают селективность лучше чем упомянутые 2-4%, т.е. делают использование индексов оправданным. Но если запросы используют предикаты группы (к примеру месяц в году, или группа товаров в которую входит десять товаров) то и эти измерения не подходят к требованиям. Отсюда следует простой факт: B* Tree индексы часто бесполезны в хранилищах данных. Типичным запросов может быть сравнение продаж между двумя магазинами приходящим покупателям определённой группы товаров за месяц. Можно создать B* Tree индесы для этих столбцов но Oracle проигнорирует их так как они недостаточно селективны. Для таких ситуация созданы bitmap индексы. Bitmap индексы хранят все rowid строк как битовую маску для каждого уникального значения ключа. Битовые маски индекса для измерения CHANNEL может быть к примеру

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

Битовые маски индекса столбца SHOP могут быть

Это значит что первые две продажи были в Лондоне, затем одна в Оксфорде, затем четвертая в Рединге и так далее.

Теперь если приходит запрос

select count(*) from sqles where channel=’WALK-IN’ and shop=’OXFORD’

Oracle может выбрать две битовые маски и объединить их с помощью операции И

Результат логического И показывает что только седьмая и шестнадцатая строки удовлетворяют запросу. Операции над битовыми масками очень быстрые и могут использоваться для сложных булевых операций надо многими столбцами со многими сочетаниями И, ИЛИ или НЕ. Также достоинством bitmap индексов является то, что они хранят значения NULL. С точки зрения битовой маски – NULL просто ещё одно уникальное значение со своей битовой маской.

В общем, bitmap индексы полезны когда

Мощность столбца низкая и

Количество строк в таблице большое и

Столбец используется в операциях булевой алгебры

If you knew in advance what the queries would be, then you could build B*Tree indexes that would work, such as a composite index on SHOP and CHANNEL. But usually you don’t know, which is where the dynamic merging of bitmaps gives great flexibility.

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

Всего доступно шесть свойств которые можно применить при создании индекса

• Уникальность / Unique или nonunique
• Реверсивность / Reverse key
• Сжатие / Compessed
• Составной или нет /Composite
• Основанный на функции или нет / Function based
• Сортировка по возрастанию или убыванию / Ascending или descending

Все шесть свойств можно применить к B* Tree индексам и только три последних можно использовать для bitmap индексов.

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

Реверсивный индекс строится на значениях ключа в которых байты строятся в обратном порядке: вместо индексирования значения к примеру ‘John’ будет использоваться значение ‘nhoJ’. Когда выполнится команда SELECT, Oracle автоматически преобразует строку поиска. Это используется для распределения строк по индексу в мультипользовательских системах. Например если много пользователей добавляют много строк в таблицу с первичным ключом как последовательно-увеличивающийся номер – все строки будут стремиться к концу индекса. Путем реверса ключа строки распределяются по всему индексу. При использовании индекса с реверсированным ключом базы данных не сохраняет ключи индекса друг за другом в лексикографическом порядке. Таким образом, когда в запросе присутствует предикат неравенства, ответ получается медленнее, поскольку база данных вынуждена выполнять полное сканирование таблицы. При индексе с реверсированным ключом база данных не может запустить запрос по диапазону ключа индекса.

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

Составной индекс – это индекс который строится для нескольких столбцов. Нет ограничений на использование столбцов разных типов данных. Если условие WHERE не использует все столбцы, то индекс всё ещё может быть использован, но если не используется самый левый столбец, то Oracle использует skip-scanning метод который гораздо менее эффективный чем если бы левый столбец был включен.

Основанный на функции индекс строится для результата выполнения функции к одному или нескольким столбцам, к примеру upper(last_name или to_char(startdate,’ccyy-mm-dd’). Запросы должны использовать ту же функцию для поиска или Oracle не сможет использовать индекс.

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

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

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

CREATE INDEX [ schema.]indexname

ON tablename (column [, column…]) ;

По умолчанию индекс не уникальный, без сжатия, не-реверсивный типа B* Tree. Невозможно создать уникальный битмап индекс (и не стоит этого поделать если вы подумаете об этом с точки зрения свойства селективности). Индексы это объекты схемы и возможно создать индекс в одной схеме и таблицу в другой, но большинство людей найдут такой способ странным. Составной индекс – это индекс для нескольких столбцов. Составные индексы могут быть созданы для столбцов разных типов и столбцы не обязательно следовать друг за другом.

Many database administrators do not consider it good practice to rely on implicit index creation. If the indexes are created explicitly, the creator has full control over the characteristics of the index, which can make it easier for theDBA to manage subsequently.

Рассмотрим пример создания таблиц, индексов и затем определение ограничений

create table dept(deptno number,dname varchar2(10));

create table emp(empno number, surname varchar2(10),

forename varchar2(10), dob date, deptno number);

create unique index dept_i1 on dept(deptno);

create unique index emp_i1 on emp(empno);

create index emp_i2 on emp(surname,forename);

create bitmap index emp_i3 on emp(deptno);

alter table dept add constraint dept_pk primary key (deptno);

alter table emp add constraint emp_pk primary key (empno);

alter table emp add constraint emp_fk

foreign key (deptno) references dept(deptno);

Первые два индекса помечены как UNIQUE, что значит нельзя добавить дубликат. Это не определяет ограничение, но на самом деле это не что иное. Третий индекс не UNIQUE и позволяет хранить дубликаты и это составной индекс для двух столбцов. Четвертый индекс – это bitmap индекс, так как ожидается что мощность столбца будет низкой.

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

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

The Oracle server should make the best decision about index use, but if it is getting it wrong, it is possible for a programmer to embed instructions, known as optimizer hints, in code that will force the use (or not) of certain indexes

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

Команда ALTER INDEX не может менять свойства индексов интересных с точки зрения программиста: тип, столбцы и всё иное. ALTER INDEX создана для администратора БД и обычно будет использоваться для управления физическими свойствами индекса. Если необходимо изменить логические свойства – то единственным способом будет удаление старого индекса и создание нового. К примеру чтобы изменить индекс EMP_I2 можно выполнить следующие команды

drop index emp_i2;

create index emp_i2 on emp(surname,forename,dob);

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