Oracle какие бывают индексы
Обновлено: 03.07.2024
Индексы (от латинского "указатель", обращающий внимание на запрещенные католической церковью книги или их фрагменты) являются хранимыми вспомогательными объектами, используемыми при отработке запросов на SQL. В базах данных смысл индексов — воплотить функцию, позволяющую по значению (или набору значений) получить адреса строк таблицы с этими значениями. В промышленных БД всех типов подобные функции обеспечиваются с помощью индекса как специальной хранимой структуры, хотя умозрительно требования в такой структуре нет, а нужна лишь функциональность.
Наиболее употребимы в Oracle B*-древовидные индексы. Они могут создаваться:
- автоматически, СУБД — как средство проверки ограничений целостности "первичный ключ" и "уникальность" в таблицах,
- либо вручную, разработчиком — ради ускорения доступа к строкам таблицы.
Во втором случае ("вручную") для создания индексов используется специальная команда SQL . Примеры:
На выбор столбцов для древовидного индекса есть ограничения.
- Разрешено создавать индекс не более чем на 32 столбца.
- Нельзя индексировать столбцы некоторых типов (например, семейства LOB или же LONG/LONG RAW ).
Влияние индексов на эффективность работы с БД противоречиво. Индексы:
- способны сокращать время обращения к строке таблицы;
- способны увеличивать время обращения к строке таблицы (при "неудачном" распределении индексированных строк по блокам);
- требуют места в БД;
- способны замедлять обновления таблиц.
Ускорение обращения к строке таблицы определяется, вопреки широко распространенному мнению, не избирательностью запроса и не размером таблицы, а в конечном итоге количеством посещаемых блоков. Этот показатель не всегда легко контролируется.
Некоторые общие и простые соображения по поводу использования древовидных индексов:
- Индекс неэффективен при малом количестве различных индексированых значений (например, пол: "М" и "Ж"), когда они представлены примерно равными количествами.
- При отсутствии значений ( NULL ) сразу во всех индексируемых столбцах (если индекс построен по нескольким столбцам) строка не индексируется. Поиск "по отсутствующим значениям" будет игнорировать индекс и выполняться полным просмотром таблицы.
Второй по важности тип индекса появился в версии Oracle 8.1 и существует для Enterprise Edition. Это поразрядный (bitmap) индекс. Он используется исключительно для ускорения доступа к данным таблицы и дает отдачу во вполне определенных обстоятельствах.
Доменный индекс (иначе — прикладной, предметный) программируется разработчиком приложения для конкретного типа объектов, однако несколько видов доменных индексов приходит в готовом виде с ПО Oracle, будучи уже запрограммированными разработчиками СУБД.
Для всех видов индексов допускаются частные случаи конфигурации.
Индексы для проверки заявляемых ограничений целостности
Употребление индексов ради ускорения доступа составляет предмет оптимизации запросов и не является темой настоящего материала. Здесь приводятся некоторые сведения об индексах, используемых СУБД для технической поддержки ограничений целостности.
При обычном объявлении в таблице первичного ключа или свойства уникальности столбцов СУБД автоматически создаст служебный уникальный древовидный индекс. В случае многостолбцовой уникальности допускается задать несколько ограничений на одних и тех же столбцах, но обязательно перечисляемых в разном порядке. Для всех таких ограничений будет использоваться один и тот же индекс — соответствующий первому по порядку создания ограничению. В результате следующих действий два "разных" ограничения AB и BA будут внутренне проверяться одним и тем же индексом AB:
В автоматику создания служебного индекса можно вмешаться. Так, желаемые свойства автоматически создаваемому индексу можно сообщить, вложив в предложение CREATE TABLE или ALTER TABLE … ADD ограничение (где формулируется ограничение целостности) конструкцию CREATE INDEX , например:
Таким образом мы получаем возможность самостоятельно не только назвать индекс первичного ключа , но и указать свойства организации и хранения индекса (выше этого не сделано, если не считать задания программистом индексу имени на свое усмотрение).
Если на необходимые столбцы индекс был заведен ранее, в качестве служебного можно взять уже имеющийся:
Это позволяет заметно снизить затраты на создание указанных ограничений для больших таблиц.
В последнем предложении конструкцию USING INDEX можно было бы не употреблять. Однако же если бы индекса PK_T заранее не существовало, эту же конструкцию можно было использовать для заведения индекса с желаемыми характеристиками, применив следующую формулировку:
Обратите внимание, что индекс в этом случае не обязан быть уникальным. (Упражнение. Проверьте свойство уникальности у индекса PK_T ). Более того, если ограничение создается как DEFERRABLE , индекс обязан быть неуникальным, и именно таковым он при том создается СУБД автоматически.
Если при заведения ограничения используется существующий индекс, явно создаваемый или же создаваемый ради ограничения с возможностью отложенной проверки ( DEFERRABLE ), то в отличие от автоматического он не будет удаляться вместе с удалением ограничения. (Упражнение. Проверьте это.) В этом есть своя логика, но это же может приводить к недоразумениям, когда по удалению ограничения целостности в БД сохранится "остаточный" индекс, не всегда нужный по делу.
Индекс на первичный ключ и на уникальные столбцы существует всегда благодаря автоматизму своего создания. С другой стороны, индекс на внешний ключ сам не создается и при необходимости делать это нужно вручную:
Упражнение. Проверьте, что создание индекса на внешний ключ не оказывает влияния на логику поведения последнего.
Решение о создании индекса на столбцы внешнего ключа принимается исходя из конкретных обстоятельств.
Таблицы с временным хранением строк
Отличаются от обычных таблиц БД тем, что время хранения строк в них ограничено концом либо транзакции, либо сеанса связи с СУБД — по выбору разработчика БД. Описания же таких таблиц (метаданные) хранятся в словаре-справочнике БД на общих основаниях с описаниями обычных таблиц, то есть вплоть до выдачи команды DROP TABLE . Эти свойства объясняют выбор фирмой Oracle названия: GLOBAL TEMPORARY в отличие от таблиц LOCAL TEMPORARY , имеющихся со времен SQL-92 (но не в Oracle), полный жизненный цикл которых ограничен программным блоком.
Пример создания таблицы с временем хранения строк, ограниченным транзакцией:
Упражнение. Как изменится результат команды CREATE выше, если в формулировке SELECT опустить фразу WHERE ?
Примеры создания таблиц с явно указанными временами хранения строк — до конца текущего сеанса или же до конца текущей транзакции:
ON COMMIT DELETE ROWS не требует явного указания, так как подразумевается по умолчанию.
Если не считать "короткого" времени жизни строк, по своим потребительским свойствам таблицы с временным хранением строк почти не отличаются от обычных. Например, для них можно строить индекс (напомним: ведь их описание хранится постоянно).
Таблицы обоих видов предоставляют каждому сеансу собственное множество строк, независимое от строк, заведенных в других сеансах (для таблиц, где время хранения строк ограничено сеансом, это неочевидно). Однако выполнение операций DDL с такими таблицами СУБД по понятным причинам увязывает с наличием в них строк (собственных) в других сеансах. Так, построить индекс ( CREATE INDEX ) удастся только, если в данный момент другой сеанс не завел в таблице собственные строки. Таким образом, косвенная связь содержимого таких таблиц в разных сеансах все-таки имеется.
Подобные таблицы используются не для моделирования прикладных данных, а как технологическое средство построения приложения: например, для хранения промежуточных результатов вычислений.
Семейство B-Tree индексов — это наиболее часто используемый тип индексов, организованных как сбалансированное дерево, упорядоченных ключей. Они поддерживаются практически всеми СУБД как реляционными, так нереляционными, и практически для всех типов данных.
Так как большинство, наверное, их хорошо знает(или могут прочесть о них например, здесь), то единственное, что, пожалуй, следует здесь отметить, это то, что данный тип индекса оптимален для множества с хорошим распределением значений и высокой мощностью(cardinality-количество уникальных значений).
Пространственные индексы
В данный момент все данные СУБД имеют пространственные типы данных и функции для работы с ними, для Oracle — это множество типов и функций в схеме MDSYS, для PostgreSQL — point, line, lseg, polygon, box, path, polygon, circle, в MySQL — geometry, point, linestring, polygon, multipoint, multilinestring, multipolygon, geometrycollection, MS SQL — Point, MultiPoint, LineString, MultiLineString, Polygon, MultiPolygon, GeometryCollection.
В схеме работы пространственных запросов обычно выделяют две стадии или две ступени фильтрации. СУБД, обладающие слабой пространственной поддержкой, отрабатывают только первую ступень (грубая фильтрация, MySQL). Как правило, на этой стадии используется приближенное, аппроксимированное представление объектов. Самый распространенный тип аппроксимации – минимальный ограничивающий прямоугольник (MBR – Minimum Bounding Rectangle) [100].
Для пространственных типов данных существуют особые методы индексирования на основе R-деревьев(R-Tree index) и сеток(Grid-based Spatial index).
Spatial grid
Spatial grid(пространственная сетка) index – это древовидная структура, подобная B-дереву, но используется для организации доступа к пространственным(Spatial) данным, то есть для индексации многомерной информации, такой, например, как географические данные с двумерными координатами(широтой и долготой). В этой структуре узлами дерева выступают ячейки пространства. Например, для двухмерного пространства: сначала вся родительская площадь будет разбита на сетку строго определенного разрешения, затем каждая ячейка сетки, в которой количество объектов превышает установленный максимум объектов в ячейке, будет разбита на подсетку следующего уровня. Этот процесс будет продолжаться до тех пор, пока не будет достигнут максимум вложенности (если установлен), или пока все не будет разделено до ячеек, не превышающих максимум объектов.
В случае трехмерного или многомерного пространства это будут прямоугольные параллелепипеды (кубоиды) или параллелотопы.
Quadtree
Quadtree – это подвид Grid-based Spatial index, в котором в родительской ячейке всегда 4 потомка и разрешение сетки варьируется в зависимости от характера или сложности данных.
R-Tree
R-Tree (Regions Tree) – это тоже древовидная структура данных подобная Spatial Grid, предложенная в 1984 году Антонином Гуттманом. Эта структура данных тоже разбивает пространство на множество иерархически вложенных ячеек, но которые, в отличие от Spatial Grid, не обязаны полностью покрывать родительскую ячейку и могут пересекаться.
Для расщепления переполненных вершин могут применяться различные алгоритмы, что порождает деление R-деревьев на подтипы: с квадратичной и линейной сложностью(Гуттман, конечно, описал и с экспоненциальной сложностью — Exhaustive Search, но он, естественно, нигде не используется).
Квадратичный подтип заключается в разбиении на два прямоугольника с минимальной площадью, покрывающие все объекты. Линейный – в разбиении по максимальной удаленности.
Hash-индексы были предложены Артуром Фуллером, и предполагают хранение не самих значений, а их хэшей, благодаря чему уменьшается размер(а, соответственно, и увеличивается скорость их обработки) индексов из больших полей. Таким образом, при запросах с использованием HASH-индексов, сравниваться будут не искомое со значения поля, а хэш от искомого значения с хэшами полей.
Из-за нелинейнойсти хэш-функций данный индекс нельзя сортировать по значению, что приводит к невозможности использования в сравнениях больше/меньше и «is null». Кроме того, так как хэши не уникальны, то для совпадающих хэшей применяются методы разрешения коллизий.
Bitmap
Bitmap index – метод битовых индексов заключается в создании отдельных битовых карт (последовательность 0 и 1) для каждого возможного значения столбца, где каждому биту соответствует строка с индексируемым значением, а его значение равное 1 означает, что запись, соответствующая позиции бита содержит индексируемое значение для данного столбца или свойства.
| EmpID | Пол |
|---|---|
| 1 | Мужской |
| 2 | Женский |
| 3 | Женский |
| 4 | Мужской |
| 5 | Женский |
Битовые карты
| Значение | Начало | Конец | Битовая маска |
|---|---|---|---|
| Мужской | Адрес первой строки | Адрес последней строки | 10010 |
| Женский | Адрес первой строки | Адрес последней строки | 01101 |
Основное преимущество битовых индексов в том, что на больших множествах с низкой мощностью и хорошей кластеризацией по их значениям индекс будет меньше чем B*-tree. (Подробнее стоит прочесть здесь или здесь)
Reverse index
Reverse index – это тоже B-tree индекс но с реверсированным ключом, используемый в основном для монотонно возрастающих значений(например, автоинкрементный идентификатор) в OLTP системах с целью снятия конкуренции за последний листовой блок индекса, т.к. благодаря переворачиванию значения две соседние записи индекса попадают в разные блоки индекса. Он не может использоваться для диапазонного поиска.
Пример:
| Поле в таблице(bin) | Ключ reverse-индекса(bin) |
|---|---|
| 00000001 | 10000000 |
| … | … |
| 00001001 | 10010000 |
| 00001010 | 01010000 |
| 00001011 | 11010000 |
Inverted index
Инвертированный индекс – это полнотекстовый индекс, хранящий для каждого лексемы ключей отсортированный список адресов записей таблицы, которые содержат данный ключ.
| 1 | Мама мыла раму |
| 2 | Папа мыл раму |
| 3 | Папа мыл машину |
| 4 | Мама отполировала машину |
В упрощенном виде это будет выглядеть так:
| Мама | 1,4 |
| Мыла | 1 |
| Раму | 1,2 |
| Папа | 2,3 |
| Отполировала | 4 |
| Машину | 3,4 |
Partial index
Partial index — это индекс, построенный на части таблицы, удовлетворяющей определенному условию самого индекса. Данный индекс создан для уменьшения размера индекса.
Function-based index
Самим же гибким типом индексов являются функциональные индексы, то есть индексы, ключи которых хранят результат пользовательских функций. Функциональные индексы часто строятся для полей, значения которых проходят предварительную обработку перед сравнением в команде SQL. Например, при сравнении строковых данных без учета регистра символов часто используется функция UPPER. Создание функционального индекса с функцией UPPER улучшает эффективность таких сравнений.
Кроме того, функциональный индекс может помочь реализовать любой другой отсутствующий тип индексов данной СУБД(кроме, пожалуй, битового индекса, например, Hash для Oracle)
Сводная таблица типов индексов
| MySQL | PostgreSQL | MS SQL | Oracle | |
| B-Tree index | Есть | Есть | Есть | Есть |
| Поддерживаемые пространственные индексы(Spatial indexes) | R-Tree с квадратичным разбиением | Rtree_GiST(используется линейное разбиение) | 4-х уровневый Grid-based spatial index (отдельные для географических и геодезических данных) | R-Tree c квадратичным разбиением; Quadtree |
| Hash index | Только в таблицах типа Memory | Есть | Нет | Нет |
| Bitmap index | Нет | Есть | Нет | Есть |
| Reverse index | Нет | Нет | Нет | Есть |
| Inverted index | Есть | Есть | Есть | Есть |
| Partial index | Нет | Есть | Есть | Нет |
| Function based index | Нет | Есть | Есть | Есть |
Стоит упомянуть, что в PostgreSQL GiST позволяет создать для любого собственного типа данных индекс основанный на R-Tree. Для этого нужно реализовать все 7 функций механизма R-Tree.
PS. Возможно я что-либо забыл упомянуть, пишите на личку или в комментарии — добавлю.
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.
Добавим строки в таблицу. По мере того как новые строки будут вставлять в таблицу, новые индексные записи будут добавляться в блок индекса, до тех пор пока блок не заполнится.
Далее Oracle выделяет два новых индексных блока и переносит все записи из начального блока (root block) в эти два новых блока, и добавляет в root block указатели(RBA - Relative Block Address) на эти два новых блока (которые теперь являются листовыми) и наименьшее проиндексированное значение из каждого из этих двух листовых блоков. RBA1 - min(value) leaf_blk_1, RBA2 - min(value) leaf_blk_2. Таким образом Oracle с этой информацией из root блока может искать нужное значение в листовых блоках.
Теперь Index height = 2, blevel = 1.
Продолжаем вставлять строки в таблицу. Два leaf блока заполняются индексами, и когда они заполнятся, Oracle добавит ещё один листовой блок, содержимое старого заполненного блока, куда должен был бы попасть новый индекс распределяется между старым и новым листовыми блоками. Указатель на новый листовой блок помещается в root блок. Каждый раз когда листовой блок заполняется и разделяется на новый, в root записывается указатель, таким образом со временем заполнится и root блок.
Когда 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);
Когда следует использовать B*Tree индексы:
Когда следует использовать Bitmap индексы:
Для данных с небольшим количеством уникальных значений. Это данные, для которых при делении количества уникальных значений в строках на общее количество строк получается небольшое число (близкое к нулю).
create bitmap index empno_bmx on t(empno);
Function Based
Индексы по функциям позволяют индексировать вычисляемые столбцы и эффективно
использовать их в запросах.
Индексы можно создавать не только по системным оракловым ф-циям, но и по своим написанным ф-циям
create index emp_upper_idx on emp (upper(ename));
Application domain indexes(прикладные индексы)
Прикладные индексы позволяют создавать новые, еще не существующие в базе данных, типы индексов.
Избирательность индекса
Избирательность представляет собой отношение количества выбранных строк к общему количеству строк. Если это отношение мало, значит, индекс обладает высокой избирательностью. Он позволяет отбросить большую часть строк и значительно уменьшает размер результирующего набора. Подобный индекс является эффективным. Индекс, не обладающий избирательностью, не является эффективным.
Наилучшей избирательностью обладает уникальный индекс. При использовании уникального индекса возвращается только одна строка, что делает этот индекс наиболее эффективным для применения в запросах, которые должны возвращать только одну строку. Например, индексирование по столбцу, содержащему уникальный код, позволяет быстро найти требуемую строку.
В этом учебном материале вы узнаете, как создавать, переименовывать и удалять индексы (create, rename and drop indexes) в Oracle/PLSQL с синтаксисом и примерами.
Что такое indexes в Oracle?
В Oracle Indexes представляет собой метод настройки производительности, чтобы более быстрее извлекать записи из таблиц. Indexes создает запись для каждого значения, которое появляется в индексированных столбцах. По умолчанию, Oracle создает индексы B-дерева.
Create INDEX
Синтаксис
Синтаксис для создания индекса в Oracle/PLSQL:
CREATE [UNIQUE] INDEX index_nameON table_name (column1, column2, . column_n)
[ COMPUTE STATISTICS ];
UNIQUE
Указывает на то, что сочетание значений в индексируемых столбцах должны быть уникальными.
index_name
Наименование индекса.
table_name
Имя таблицы для которой создается индекс.
column1 , column2 , . column_n
Столбцы для использования в индексе.
COMPUTE STATISTICS
Это послание Oracle для сбора статистических данных во время создания индекса. Статистические данные затем используются оптимизатором, чтобы выбрать "план выполнения", когда выполняются SQL запросы.
Пример
Рассмотрим пример того, как создать индекс в Oracle/PLSQL.
Например:
В этом примере мы создали index таблицы supplier под названием supplier_idx . Он состоит только из одного поля supplier_name .
Index также можно создать для нескольких полей, как в примере ниже:
При создании index, для сбора статистических данных можно сделать следующим образом:
Создание Function-Based INDEX
В Oracle, вы не ограничены созданием индексов только на столбцах. Вы можете создавать индексы на базе функций.
Синтаксис
Синтаксис для создания function-based index в Oracle/PLSQL:
CREATE [UNIQUE] INDEX index_nameON table_name (function1, function2, . function_n)
[ COMPUTE STATISTICS ];
UNIQUE
Указывает на то, что сочетание значений в индексируемых столбцах должны быть уникальными.
index_name
Наименование индекса.
table_name
Имя таблицы для которой создается индекс.
function1 , function2 , . function_n
Функции для использования в индексе.
COMPUTE STATISTICS
Это послание Oracle для сбора статистических данных во время создания индекса. Статистические данные затем используются оптимизатором, чтобы выбрать "план выполнения", когда выполняются SQL запросы.
Пример
Рассмотрим на примере того, как создать function-based index в Oracle/PLSQL.
Например:
Читайте также: