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

В представлении USER_INDEXES есть столбец distinct_keys, который содержит кол-во уникальных ключей для этого индекса. Сравнение  distinct_keys и числа строк в таблице позволяет определить избирательность индекса. Чем ближе distinct_keys к числу строк в таблице, тем более избирательный индекс, запрос с использованием этого индекса вернёт меньшее кол-во строк, сообтветсвенно быстрее отработает запрос. Но при использовании дополнительных индексов (сложные/сцепленные индексы) в запросе издержки могут превысить выгоду.

oracle: Index Scans

http://aguppi.blogspot.com/2011/06/oracle-access-path.html
Что такое индекс oracle: Index

Типы Index Scans:

• Index Unique Scans
• Index Range Scans
• Index Range Scans Descending
• Index Skip Scans
• Full Scans
• Fast Full Index Scans
• Index Joins
• Bitmap Joins

---

Index Unique Scans

Возвращает один rowid.
Происходит, если выражение использует индекс, который содержит ограничение UNIQUE или PRIMARY KEY, которые гарантирую что будет возвращена только одна строка.

Hints
/*+ INDEX( tab_name idx1_name idx2_name ...) */

Этот хинт указывает оптимизатору использывать индекс, но не определённый путь индексного доступа.
Обычно не нужно указывать хинт для Index Unique Scans. Но если используем db link или таблица мала и оптимизатор выбирает Full Table Scan, то можно указать хинт.

oracle: Индексы по внешним ключам

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

Не проиндексированный внешний ключ плох еще и в следующих случаях:

- При наличии конструкции ON DELETE CASCADE. Например, таблица ЕМР является подчиненной для таблицы DEPT. Оператор DELETE FROM DEPT WHERE DEPTNO = 10 должен вызвать каскадное удаление в таблице ЕМР. Если столбец DEPTNO в таблице ЕМР не проиндексирован, для этого придется выполнить полный просмотр таблицы ЕМР. Этот полный просмотр нежелателен; кроме того, при удалении большого количества строк из главной таблицы подчиненная будет каждый раз полностью просматриваться.

- При выполнении запроса от главной таблицы к подчиненной. Рассмотрим пример с таблицами EMP/DEPT еще раз. Очень часто таблица ЕМР запрашивается с условием по столбцу DEPTNO. Если приходится часто выполнять запрос:

select * from dept, emp
where emp.deptno = dept.deptno
and dept.dname = :X;

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

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

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

Можно провести анализ используемого индексом пространства. 
Для этого анализируется структура индекса, с помощью SQL оператора ANALYZE INDEX … VALIDATE STRUCTURE, а затем запрашивается представление INDEX_STATS:

-- анализируем (dbms_stats не подходит)
ANALYZE INDEX object_id_idx  VALIDATE STRUCTURE;
-- смотрим
SELECT PCT_USED FROM INDEX_STATS WHERE NAME = 'имя_индекса';

Note: ANALYZE INDEX … VALIDATE STRUCTURE ставит блокировку на таблицу, по которой построен индекс.

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

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

-- Пример начала мониторинга индекса:
ALTER INDEX ALL_ORACLE_EVENT_NO MONITORING USAGE;

-- Для прекращения мониторинга указывается NOMONITORING, как показано ниже:
ALTER INDEX ALL_ORACLE_EVENT_NO NOMONITORING USAGE;

Кроме того, можно выполнить запрос к представлению V$OBJECT_USAGE, чтобы узнать, использовался требуемый индекс или нет. В представлении есть столбец USED, который принимает значение YES или NO, в зависимости от того, использовался ли индекс во время проведения мониторинга. Еще представлен столбец времени начала и окончания мониторинга и столбец указывающий состояние мониторинга, проводиться он на данный момент или нет, это столбец MONITORING, принимающий значение YES или NO.

Каждый раз при указании MONITORING USAGE или NOMONITORING USAGE, значения столбцов в представлении V$OBJECT_USAGE изменяются для соответствующего индекса. Информация о предыдущих запусках удаляется или сбрасывается. При указании NOMONITORING USAGE записывается время окончания.

oracle: Удаление индексов

Чтобы иметь возможность удалять индексы, индекс должен находиться в вашей схеме, или если он находить в другой схеме, то пользователю необходима системная привилегия – DROP ANY INDEX.

Основные причины удаления индекса:

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

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

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

Если индекс создан через CREATE INDEX, его можно удалить, используя команду DROP INDEX, как показано в примере:

DROP INDEX EVENT_NO;

Для удаления индекса связанного с ограничением целостности необходимо удалить само ограничение или отключить его. 

Нельзя удалить индексы, связанные с ограничением целостности – UNIQUE или PRIMARY KEY.

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

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

-- перестроим индекс:
ALTER INDEX EVENT_NO REBUILD;

Предложение REBUILD должно следовать непосредственно за именем индекса, предваряя все другие параметры. Его нельзя использовать вместе с DEALLOCATE UNUSED.

-- перестроим индекс в оперативном режиме:
ALTER INDEX EVENT_NO REBUILD ONLINE;

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

oracle: Создание больших индексов

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

1. Создайте временное табличное пространство, используя конструкцию CREATE TABLESPACE или CREATE TEMPORARY TABLESPACE.
2. С помощью параметра TEMPORARY TABLESPACE конструкции ALTER USER назначьте созданное табличное пространство как новое временное табличное пространство.
3. Создайте индекс с помощью CREATE INDEX.
4. Верните временное табличное пространство пользователю, выполнив ALTER USER … TEMPORARY TABLESPACE. И затем удалите ненужное табличное пространство, выполнив DROP TABLESPACE.

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

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

oracle: Создание индексов связанных с ограничением целостности

Oracle обеспечивает выполнение ограничения целостности UNIQUE или PRIMARY KEY для таблицы, создавая уникальный индекс для уникального или первичного ключа. Этот индекс создается автоматически, когда включается ограничение целостности. Когда выполняется CREATE TABLE или ALTER TABLE, для создания индекса не надо предпринимать никаких действий, но при желании можно указать предложение USING INDEX, чтобы контролировать его создание.

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

oracle: Уникальные и неуникальные индексы

Индексы могут быть уникальными или неуникальными.

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

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

-- Для создания уникального индекса используется SQL оператор – CREATE UNIQUE INDEX.
CREATE UNIQUE INDEX ANIKNAME_UNIQUE_IDX ON ALL_ORACLE_ADMIN (ANIKNAME)
    TABLESPACE ALL_ORACLE_IDX;

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

oracle: Выбор столбцов для индексирования

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

• Создавать индекс рекомендуется, если часто выбираются данные, составляющие примерно 15% от данных таблицы. Этот процент может значительно варьироваться в зависимости от относительной скорости просмотра таблицы и от степени кластеризации данных строки по отношению к ключу индекса. Чем быстрее просматривается таблица, тем ниже процент. Чем больше кластеризация данных строки, тем процент выше.
• Для повышения производительности соединений нескольких таблиц индексируйте столбцы, которые используются в соединении.
  Заметка: Для первичных и уникальных ключей индексы создаются автоматически, но при желании можно создать индексы и для внешних ключей.
• Для маленьких таблиц индексы не требуются. Если запрос занимает долгое время, то возможно количество данных в таблице значительно возросло.

oracle: Indexes and NULL

Индексы и NULL

Индексы на основе В*-дерева, кроме индекса кластера, не содержат записей для NULL, а индексы на основе битовых карт и индекс кластера - имеют.

Чтобы запрос select * from T where x is null; использовал индекс нужно, чтобы хотя бы один столбец в индексе имел ограничение NOT NULL.

Например:

-- создаём таблицу
create table t (x int, у int NOT NULL);
-- создаём индекс
create unique index t_idx on t(x,y);
-- выполняем запрос, он будет использовать индекс, т.к. у int NOT NULL 
select * from t where x is null;

oracle: Indexes and Views

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

Чтобы проиндексировать представление, надо просто проиндексировать базовые таблицы.

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

oracle: Index

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 блок. И так далее.

oracle: CLUSTERING FACTOR (фактор кластеризации)

CLUSTERING FACTOR - столбец в представлениях dba_indexes, user_indexes.

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

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

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

-- посмотреть и оценить фактор кластеризации (перед этим нужно собрать статистику)
exec dbms_stats.gather_table_stats(ownname=>'GUPPI', tabname=>'T', cascade=>true);

select a.index_name, b.num_rows, b.blocks, a.clustering_factor
from user_indexes a, user_tables b
where a.table_name = b.table_name and a.table_name='T';