Платформа 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.
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 - это упорядоченный список определенных столбцов или групп столбцов в таблице. Когда создается индекс по одному или нескольким полям, то сервер БД формирует соответствующий упорядоченный список. Таблица, конечно же, должна уже быть создана и должна содержать имена индексируемых столбцов.
Синтаксис CREATE INDEX
CREATE INDEX
Добавление индекса снижает производительность запросов, связанных с добавлением, изменением или удалением данных, поскольку каждый раз при выполнении транзакции данные индекса также обновляются, что требует выполнения от сервера дополнительной работы. Однако выполение запросов SELECT по индексируемым полям существенно перевешивают эти недостатки. Не следует создавать индексы по каждому столбцу таблицы, не определив, какие запросы будут выполняться.
Уникальный индекс, UNIQUE INDEX
Индекс может быть уникальным unique index , что не позволяет иметь в таблице дублированных записей с одинаковыми значениями индексируемых полей.
ПРИМЕЧАНИЕ: при создании уникального индекса транзакция будет отклонена, если уже имеются идентичные значения в записях таблицы по индексируемым полям. Для уникального индекса таблицы с несколькими полями комбинация значений должна быть единственной, но каждое из значений поля может и не быть уникальным.
Отличие PRIMARY KEY и UNIQUE INDEX
Ограничения "primary key" и unique index обеспечивают уникальность значений полей таблицы, в которой они определены. По умолчанию primary key создает кластерный индекс на столбце, а "unique index" - некластерный. Другим отличием является то, что "primary key" не может иметь нулевых записей, т.е. поле NOT NULL, в то время как "unique index" допускает только одну нулевую запись (NULL). Таблица может иметь только один первичный ключ, но несколько "unique index".
Удаление DROP INDEX
Удаление индекса не воздействует на содержание полей. Синтаксис оператора удаления индекса drop index:
DROP INDEX
ALTER INDEX
В разных СУБД имеются существенные различия по использованию оператора alter index . Так например MySQL не поддерживает данный оператор, в Interbase можно использовать данный оператор для отключения и повторного включения индекса, в результате чего будет выполнена переиндексация данных.
В СУБД PostgresSQL индекс можно переименовать с использованием оператора alter index . Синтаксис переменования индекса:
Переименование индекса в СУБД PostgresSQL ALTER INDEX index_name RENAME TO index_name_new;
ALTER INDEX в Oracle
Платформа Oracle также поддерживает инструкцию alter index . Данный оператор используется для изменения или перестройки существующего индекса без его удаления и повторного создания.
Синтаксис оператора для переименования индекса в Oracle имеет следующий вид:
Переименование индекса в СУБД Oracle ALTER INDEX index_name RENAME TO index_name_new;
Для переиндексации данных необходимо использовать следующий синтаксис оператора alter index :
ALTER INDEX index_name [ coalesce | [ rebuild | rebuild online ] ];
COALESCE
При использовании coalesce таблица не блокируется и переиндексация выполняется online. При этом индекс размещается в пределах существующей индексной структуры - соединяет блоки листа в пределах имеющихся ветвей дерева. Индексные листовые блоки быстро освобождаются для использования и не требуется много дискового пространства.
Однако coalesce генерирует много записей в журналах повторного выполнения (redo). При этом данный операнд может вызвать ошибку ORA-01555 (coalesce определяет "работу" Oracle с листовыми блоками, определенных количеством малых транзакций. А много малых транзакций, выполненных одной сессией, могут вызвать у другой сессии, выполняющей продолжительную транзакцию, эту ошибку). Кроме этого coalesce не опускает HWM индекс, т.е. место на диске не освобождает и не может переместить индекс в другое табличное пространство.
REBUILD
Использование rebuild позволяет быстро перемещать индекс в другое табличное пространство. Кроме этого "rebuild" создает новое дерево и уменьшает его высоту при необходимости. А также дает возможность быстро изменять storage и tablespace параметры, без необходимости удалять индекс. Может быть использован для уменьшения расходования ресурсов - передвигается отметка HWM.
Однако rebuild связан с более высокими издержками - требуется больше дискового пространства, чтобы разместить старый и новый индекс в соответствующем табличном пространстве. Кроме этого rebuild может вызвать ошибку ORA-01410: Invalid ROWID.
Rebuild "offline" может использовать существующий индекс для создания новой версии индекса, но блокирует таблицу во время выполнения.
Rebuild "online" не блокирует таблицу во время непосредственной перестройки индекса, и индекс доступен практически все время при перестроении, кроме времени переключения. Однако при этом блокируется таблица в начале и в конце перестроения. При этом старый индекс не используется для перестроения индекса, но с ним работают пользователи. Все изменения тем временем вносятся в журнальную таблицу, затем уже будут перенесены в новый индекс. Может потребоваться большая сортировка.
Таким образом, оператор coalesce особенно эффективен, когда процент проблематичного пространства к общему индексному пространству невелик (20% листовых блоков) и фрагментирован индекс несущественно. rebuild особенно эффективен, когда процент проблематичного пространства к общему индексному пространству велик и средняя степень фрагментации в пределах индексного блока листа сравнительно высокая.
Структура B-дерева имеет следующие преимущества:
B-дерево автоматически поддерживается в сбалансированном виде.
Все блоки-листья в дереве расположены на одном уровне, следовательно, поиск любой записи в индексе занимает примерно одно и то же время.
B-деревья обеспечивают хорошую производительность для широкого спектра запросов, включая поиск по конкретному значению и поиск в открытом и закрытом интервалах (благодаря ссылкам между блоками-листьями).
Модификация данных таблицы выполняется достаточно эффективно, т.к. в блоках индекса обычно есть свободное место для размещения новых значений, а полная перестройка дерева выполняется достаточно редко.
Производительность B-дерева одинаково хороша для маленьких и больших таблиц, и не меняется существенно при росте таблицы.
Использование индексов
Синтаксис команды create index следующий:
create index <имя_индекса>
on <имя_таблицы>(<поле1> [, <поле2>,...]) [<параметры>];
Имя индекса должно быть уникальным среди имён объектов БД. Если индекс составной, то входящие в него поля перечисляются через
запятую. Необязательные <параметры> зависят от используемой СУБД. Например, в Oracle с помощью следующей команды можно создать составной индекс для таблицы СОТРУДНИКИ (EMP) по полямФамилия (fam) иИмя (name):
create index ind_emp_name on emp(fam, name) TABLESPACE MY_INDEXES;
Индексы и таблицы желательно создавать в разных табличных пространствах.
Использование индексов
Выбор столбцов для индекса определяется следующими соображениями:
В первую очередь выбираются столбцы, которые часто встречаются в условиях поиска.
Стоит индексировать столбцы, которые используются для соединения таблиц или являются внешними ключами. В последнем случае наличие индекса позволяет обновлять строки подчинённой таблицы без блокировки основной таблицы, когда происходит интенсивное конкурентное обновление связанных между собою таблиц.
Нецелесообразно индексировать столбцы с низкой селективностью. Исключения для низкой селективности составляют случаи, при которых выборка чаще производится по редко встречающимся значениям.
Не индексируются столбцы, которые часто обновляются, т.к. команды обновления ведут к потере времени на обновление индекса.
Не индексируются столбцы, которые часто используются как аргументы выражений или функций: как правило, это не позволяет использовать индекс.
Использование индексов
В некоторых случаях использование составного индекса предпочтительнее, чем одиночного, а именно:
Если в запросах часто используются только столбцы, участвующие в индексе, система может вообще не обращаться к таблице для поиска данных.
Несколько столбцов с низкой селективностью в комбинации друг с другом могут дать гораздо более высокую селективность.
Обращение к составному индексу возможно только в том случае, если в условиях выбора участвуют столбцы, представляющие собой лидирующую часть составного индекса. Если индекс, например, включает поля (X, Y, Z), то обращение к индексу будет происходить в тех случаях, когда в условии запроса участвуют поля XYZ, XY или X, причём именно в таком порядке.
Использование индексов
Вопрос. Будет ли система пользоваться индексом при выполнении следующих запросов:
1) SELECT * FROM emp;
2) SELECT * FROM emp
WHERE name = "Даль";
3) SELECT * FROM emp
WHERE sex = "ж";
4) SELECT depno, count(*) FROM emp GROUP BY depno;
5) SELECT * FROM emp e, child c WHERE e.tabno=c.tabno;
Необходимое условие использования индекса: в запросе есть условие на значение индексируемого поля.
Достаточное условие использования индекса: запрос по индексу выполняется быстрее, чем без индекса (повышение эффективности).
Индекс имеет смысл, если нужно обеспечить доступ одновременно не более чем к 4-5% данных таблицы. Помните, что применение индексов для извлечения строк требует двух операций чтения: индекса и затем таблицы.
Избегайте создания индексов для сравнительно небольших таблиц. Для таких таблиц больше подходит полное сканирование. В случае маленьких таблиц нет необходимости в хранении данных и таблиц, и индексов.
Создавайте первичные ключи для всех таблиц. При назначении столбца в качестве первичного колюча Oracle автоматически создаст индекс по этому столбцу.
Индексируйте столбцы, участвующие в многотабличных операциях соединения.
Индексируйте столбцы, которые часто используются в конструкциях WHERE.
Использование индексов в Oracle
Индексируйте столбцы, участвующие в операциях ORDER BY и GROUP BY или других операциях, таких как UNION и DISTINCT, включающих сортировку. Поскольку индексы уже отсортированы, объем работы по выполнению необходимой сортировки данных для упомянутых операций будет существенно сокращен.
Столбцы, стоящие из длинно-символьных строк, обычно плохие кандидаты на индексацию.
Столбцы, которые часто обновляются, в идеале не должны быть индексированы из-за связанных с этим накладных расходов.
Индексируйте таблицы в которых мало строк имеют одинаковые значения.
Сохраняйте количество индексов небольшим.
Составные индексы могут понадобиться там, где одностолбцовые значения сами по себе не уникальны. В составных индексах первым столбцом ключа должен быть столбец в котором количество строк с одинаковым значением минимально.
Битовые индексы Oracle
Битовые индексы (BITMAP) используют битовые карты для указания значения индексированного столбца. Это идеальный индекс для столбца с низкой кардинальностью (число уникальных записей в таблице мало) при большом размере таблицы.
Эти индексы обычно не годятся для таблиц с интенсивным обновлением, но хорошо подходят для приложений хранилищ данных. Битовые индексы состоят из битового потока (единиц и нулей) для каждого столбца индекса. Битовые индексы очень компактны по сравнению с нормальными индексами на основе B-деревьев.
Битовые индексы Oracle
Для создания битового индекса используется оператор^ CREATE BITMAP INDEX day_ind ON tab(day) TABLESPACE MY_INDEXES;
Индексы Oracle с реверсированным ключом
Индексы с реверсированным ключом – это, по сути, то же самое, что и индексы B-деревьев, за исключением того, что байты данных ключевого столбца при индексации меняют порядок на противоположный. Порядок столбцов остается нетронутым, меняется только порядок байтов. Самое большое преимущество применения индексов с реверсивным ключом состоит в том, что они исключают неприятные последствия упорядоченной вставки значений в индекс. Вот как создается индекс с реверсированным ключом:
CREATE INDEX reverse_idx ON employee(emp_id) REVERSE;
При использовании индекса с реверсированным ключом базы данных не сохраняет ключи индекса друг за другом в лексикографическом порядке. Таким образом, когда в запросе присутствует предикат неравенства, ответ получается медленнее, поскольку база данных вынуждена выполнять полное сканирование таблицы. При индексе с реверсированным ключом база данных не может запустить запрос по диапазону ключа индекса.
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.
Добавим строки в таблицу. По мере того как новые строки будут вставлять в таблицу, новые индексные записи будут добавляться в блок индекса, до тех пор пока блок не заполнится.
Далее 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
Индексы на основе битовых карт — это структуры, в которых хранятся указатели на множество строк, соответствующих одному значению ключа индекса
, тогда как в структуре В*-дерева количество ключей индекса обычно примерно соответствует количеству строк. В индексе на основе битовых карт записей очень мало, и каждая из них указывает на множество строк. В индексе на основе В*-дерева обычно имеется однозначное соответствие — запись индекса ссылается на одну строку.
Когда следует использовать 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 при необходимости потребует от прикладного индекса вернуть ответ.
Избирательность индекса
Избирательность представляет собой отношение количества выбранных строк к общему количеству строк. Если это отношение мало, значит, индекс обладает высокой избирательностью. Он позволяет отбросить большую часть строк и значительно уменьшает размер результирующего набора. Подобный индекс является эффективным. Индекс, не обладающий избирательностью, не является эффективным.
Наилучшей избирательностью обладает уникальный индекс. При использовании уникального индекса возвращается только одна строка, что делает этот индекс наиболее эффективным для применения в запросах, которые должны возвращать только одну строку. Например, индексирование по столбцу, содержащему уникальный код, позволяет быстро найти требуемую строку.
