Как создавать и использовать индексы в БД Oracle
Индексы – это одни из самых мощных и непонятных аспектов производительности SQL. В этой статье мы рассмотрим назначение индекса, как правильно создать и выбрать нужный тип индекса. Затем мы обсудим как решить, что индексировать и полезно ли это.
Зачем индексы
Таблицы базы данных могут стать очень большими. Невероятно, колоссально большими. Например, Большой Адронный Колайдер в ЦЕРН генерирует суммарно 10 TB данных в день. А для поиска нескольких строк из миллионов, миллиардов, триллионов записей требуется большое количество времени. И индексы помогут вам это время сократить.
Индекс хранит значения в индексируемом столбце или столбцах. А для каждого значения - расположение строк, в которых есть это значение. Подобно указателю в конце книги. Это позволяет вам сосредоточиться только на тех данных, которые вас интересуют.
Индексы наиболее эффективны, когда позволяют вам найти несколько строк. Так что, если у вас есть запрос и большая таблица без индексов, то есть большая вероятность, что вы захотите их создать.
Как создать индекс
Создать индекс легко. Все что вам нужно сделать, это указать какие столбцы вы хотите индексировать, и дать имя индексу:
create index <index_name> on <table_name> ( <column1>, <column2>, … );Так, если вы хотите проиндексировать столбец Color таблицы Toys и назвать индекс toys_color_idx, то скрипт будет таким:
create index toys_color_i on toys ( color );Легко, правда?
Но как всегда, это еще не все. Вы можете помсетить много стобцов в один индекс. Например, вы можете включить в индекс тип игрушек, как показано ниже:
create index toys_color_type_i on toys ( color, type );Это завывается составным или комбинированным индексом. Порядок расположения столбцов в индексе оказывает большое влияние на то, будет ли оптимизатор использовать его. Мы обсудим это позже.
Как выбрать тип индекса
БД Oracle предлагает множество различных типов индексов для улучшения ваших SQL запросов. Одно из ключевых решений, которое вам необходимо принять, - это выбор Bitmap или B-tree индекса.
B-tree vs Bitmap
По умолчанию создаются B-tree индексы. Они являются сбалансированными деревьями. Это означает, что все листовые узлы находятся на одинаковой глубине в дереве. Поэтому для доступа к любому значению требуется одинаковое количество работы
Каждая запись листового индекса указывает ровно на одну строку.
Bitmap как и B-tree, хранят индексированные значения. Но вместо одной строки на запись, база данных связывает каждое значение с диапазоном строк. Затем имеется ряд единиц и нулей, чтобы показать, имеет ли каждая строка в диапазоне значение (1) или нет (0).
Value Start Rowid End Rowid Bitmap
VAL1 AAAA ZZZZZ 001000000...
VAL2 AAAA ZZZZZ 110000000...
VAL3 AAAA ZZZZZ 000111100...
...Обратите внимание, что начальный и конечный диапазоны строк охватывают все строки таблицы. Но в больших таблицах может потребоваться разделить строки на несколько диапазонов. Поэтому каждое индексированное значение имеет много записей.
Вот клчивые различия между B-tree и Bitmap.
Строки, в которых все индексированные значения равны null, НЕ включаются в B-tree. Но они включены в Bitmap! Поэтому оптимизатор может использовать Bitmap для ответов на такие запросы, как:
where indexed_column is null;Но обычно для B-tree это не так. Вы можете обойти это в B-tree, добавив константу в конец индекса. Таким образом, получается следующий составной индекс:
create index enable_for_is_null_i on tab ( indexed_column, 1 );Еще одним преимуществом Bitmap является то, что все эти единицы и нули легко сжимаются. Поэтому индекс Bitmap обычно меньше, чем тот же индекс B-tree. Но это преимущество уменьшается по мере увеличения количества различных значений в индексе.
Допустим у нас есть таблица победителей Олимпийских игр. Создание индексов на Edition, Sport, Medal, Event и Athleteдает следующие размеры (в блоках)
Column | B-tree | Bitmap |
Edition | 61 | 6 |
Sport | 83 | 1 |
Athlete | 117 | 115 |
Medal | 71 | 3 |
Event | 111 | 5 |
Gender | 64 | 1 |
Если вы знакомы с таблицами истинности булевой логики, вы можете заметить одно большое преимущество Bitmap:
Для оптимизатора не составит труда объединить их.
Наложив диапазоны строк двух индексов, вы можете найти строки, соответствующие предложению where в обоих индексах. Затем перейти к таблице только для этих строк.
Это означает, что индексы, указывающие на большое количество строк, могут быть полезны. Допустим, вы хотите найти всех женщин, завоевавших золотые медали на Олимпийских играх в Афинах в 2000 году. Ваш запрос будет следующим:
select * from olym_medals
where gender = 'Women'
and medal = 'Gold'
and edition = 2000;Каждая медаль составляет примерно одну треть строк. И можно подумать, что мужчины и женщины делятся примерно 50/50. К сожалению, это скорее 75/25 мужчин и женщин. Но в любом случае вряд ли будет полезен индекс только по gender или medal. Предположим, что в таблице 26 олимпийских игр. Что приближается к "малому количеству" строк. Но нет никакой гарантии, что оптимизатор выберет этот индекс.
Но в совокупности эти условия можно определить приблизительно:
(1/2) * (1/3) * (1/26) = 1/156 ~ 0.64% строкЭто определенно попадает в область "нескольких" строк. Поэтому, скорее всего, запрос выиграет от использования какого-то индекса.
Если вы создаете Bitmap с одним столбцом, база данных может объединить с помощью BITMAP AND следующим образом:
---------------------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| OLYM_MEDALS |
| 2 | BITMAP CONVERSION TO ROWIDS | |
| 3 | BITMAP AND | |
|* 4 | BITMAP INDEX SINGLE VALUE | OLYM_EDITION_BI |
|* 5 | BITMAP INDEX SINGLE VALUE | OLYM_MEDAL_BI |
|* 6 | BITMAP INDEX SINGLE VALUE | OLYM_GENDER_BI |
---------------------------------------------------------------Это дает большую гибкость. Вам нужно создавать индексы только для одного столбца вашей таблицы. Если у вас есть условия для нескольких столбцов, база данных сшивает их вместе!
В-tree не имеют этой роскоши. Вы не можете просто наложить одно на другое, чтобы найти то, что вы ищете. Хотя Oracle может объединять B-tree (через "преобразование bitmap из rowids"), это относительно дорого. В целом, чтобы получить ту же производительность, что и при использовании трех bitmap, необходимо поместить все три столбца в один индекс. Это влияет на то, насколько многоразовым является индекс, о чем мы поговорим позже.
На данный момент это выглядит как безоговорочная победа bitmap над B-tree. Но теперь вы можете задасться вопросом:
Почему по умолчанию используются B-tree, а не Bitmap?
Индексы Bitmap имеют существенный недостаток: Убивают параллелизм записи.
Это одна из немногих ситуаций в Oracle, когда вставка в одном сеансе может блокировать вставку в другом. Это делает их сомнительными для большинства OLTP-приложений.
Почему?
Каждый раз, когда вы вставляете, обновляете или удаляете строки таблицы, база данных должна синхронизировать индекс. Это происходит в B-tree путем прохождения по дереву, изменяя по мере необходимости записи в листьях.
Но Bitmap блокирует весь диапазон строк начала/конца! Допустим, вы добавляете строку со значением RED. Любые другие вставки, которые попытаются добавить еще одну строку со значением RED в тот же диапазон, будут заблокированы до тех пор, пока первая не будет зафиксирована!
Это еще большая проблема с обновлениями. Обновление в B-tree - это удаление старого значения и вставка нового. Но при использовании Bitmap Oracle должна заблокировать затронутые диапазоны для старого и нового значений!
В результате Bitmap лучше всего подходят для таблиц, в которые одновременно будет записываться только один процесс. Это часто бывает в таблицах отчетов или хранилищах данных. Но это не ваше типичное приложение.
Поэтому, если несколько сеансов будут одновременно изменять данные в таблице, хорошо подумайте, прежде чем использовать Bitmap.
Поэтому, как бы ни были хороши Bitmap, для большинства приложений лучше использовать B-tree!
Индексы на основе функций
Это просто индексы, в которых к одному или нескольким столбцам применяется функция. Индекс хранит результат этого вычисления. Например:
или
create index date_at_midnight_i on table ( trunc ( datetime ) );create index upper_names_i on table ( upper ( name ) );Вы можете использовать функции в индексах Bitmap или B-tree.
Имейте в виду, что если у вас есть индекс на основе функции, то для его использования функция в предложении where должна точно соответствовать определению в индексе. Поэтому если ваш индекс имеет вид:
create index dates_i on dates ( trunc (datetime) );Ваше условие where должно быть таким:
where trunc (datetime) = :some_dateЛучше избегать индексов на основе функций, если это возможно. Вместо этого перенесите вычисления из столбца в переменную.
Например, используя стандартную математику, перестройте формулу так, чтобы в столбце не было функций:
column + 10 = val -> column = val – 10
column * 100 = val -> column = val / 100
Или так. Тип данных даты в Oracle всегда включает время суток. Поэтому, чтобы гарантированно получить все строки, приходящиеся на определенный день, вы можете нормализовать дату до полуночи. Затем сравните результат с датой, например, так:
trunc( datetime_col ) = :dtНо есть и другой способ сделать это. Проверьте, что столбец больше или равен переменной и строго меньше переменной плюс один день:
datetime_col >= :dt and datetime_col < :dt + 1Уникальные индексы
Уникальный индекс - это форма ограничения. Он утверждает, что вы можете хранить заданное значение в таблице только один раз. Когда вы создаете первичный ключ или уникальное ограничение, Oracle автоматически создаст для вас уникальный индекс (при условии, что индекс еще не существует). В большинстве случаев вы добавляете ограничение в таблицу и позволяете базе данных создать индекс за вас.
Но есть один случай, когда необходимо вручную создать индекс: Уникальные ограничения на основе функций.
Вы не можете использовать функции в уникальных ограничениях. Например, вы можете захотеть создать таблицу "dates", которая хранит одну строку для каждого календарного дня. К сожалению, в Oracle нет типа данных "день". Даты всегда содержат компонент времени. Чтобы обеспечить одну строку в день, необходимо зафиксировать время до полуночи. Для этого применяется функция trunc(). Но ограничение не сработает:
alter table dates add constraint date_u unique ( trunc ( calendar_date ) );Поэтому необходимо прибегнуть к уникальному индексу на основе функции:
create unique index date_ui on dates ( trunc ( calendar_date ) );Или вы можете скрыть функцию в виртуальном столбце. Затем проиндексировать ее. Например:
alter table dates add cal_date_no_time as ( trunc(calendar_date) );
alter table dates add constraint date_u unique ( cal_date_no_time );Нисходящие индексы
Индексы B-tree - это упорядоченные структуры данных. Новые записи должны попадать в нужное место в соответствии с логическим порядком, навязанным столбцами. По умолчанию они сортируются от малого к большому.
Но вы можете изменить это. Указав desc после столбца, Oracle сортирует значения от больших к маленьким.
Обычно это не имеет большого значения. База данных может подниматься или опускаться по индексу по мере необходимости. Поэтому редко кто захочет их использовать.
Но есть случай, когда они могут помочь. Если ваш запрос содержит "order by desc", то нисходящий индекс может избежать операции сортировки. Простейший случай - это когда вы ищете диапазон значений, сортируете их по убыванию и по другому столбцу.
Например, поиск заказов по идентификаторам customer_ids в заданном диапазоне. Верните эти идентификаторы в обратном порядке. Затем отсортируйте по дате продажи:
select * from orders
where customer_id between :min_cust_id and :max_cust_id
order by customer_id desc, order_datetime;Если использовать обычный составной индекс на ( customer_id, order_datetime ), план будет выглядеть следующим образом:
-------------------------------------------------
| Id | Operation | Name |
-------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT ORDER BY | |
| 2 | TABLE ACCESS BY INDEX ROWID| ORDERS |
| 3 | INDEX RANGE SCAN | ORDERS_I |
-------------------------------------------------Но создайте его с ( customer_id desc, order_datetime ) и шаг сортировки исчезает!
------------------------------------------------
| Id | Operation | Name |
------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| ORDERS |
| 2 | INDEX RANGE SCAN | ORDERS_I |
------------------------------------------------Это может существенно сэкономить время, если сортировка "дорогая".
JSON Search Indexes
Хранение данных в виде документов JSON (к сожалению) становится все более распространенным. Все атрибуты являются частью документа, а не отдельным столбцом. Это затрудняет индексирование значений. Поэтому поиск документов, содержащих определенные значения, может быть мучительно медленным. Эту проблему можно решить, создав индексы на основе функций для искомых атрибутов.
Например, допустим, вы храните данные об Олимпийских играх в формате JSON. Вы регулярно ищете, какие медали завоевал тот или иной спортсмен. Вы можете проиндексировать это следующим образом:
create index olym_athlete_json_i on olym_medals_json (
json_value ( jdoc, '$.athlete' )
);И ваши запросы с этим выражением json_value должны получить хороший прирост.
Но вы можете захотеть выполнить специальный поиск в JSON, ища любые значения в документах. Чтобы помочь в этом, вы можете создать индекс специально для данных JSON начиная с Oracle 12.2. Это легко сделать:
create search index olym_medals_json_i on olym_medals_json
( jdoc ) for json;Затем база данных может использовать его для любого SQL запроса с помощью различных функций JSON.
Если вам интересно, как работает поисковый индекс JSON, то под обложкой он использует Oracle Text. Это подводит нас к следующему:
Oracle Text Indexes
Возможно, в вашей базе данных есть большие объемы свободного текста. Такие, по которым вы хотите проводить нечеткий поиск, семантический анализ и так далее. Для них вы можете создать текстовый индекс. Они бывают трех видов:
Context
Category
Rule
Использование этих индексов само по себе является огромной темой. Поэтому, если вы хотите выполнить такой поиск, я рекомендую начать с Руководства разработчика.
Существует несколько других типов индексов. Скорее всего, вам никогда не понадобится их создавать, но я включил их на всякий случай ;)
Application Domain Indexes
Время от времени вы хотите создать специализированный индекс, адаптированный к вашим данным. Здесь вы указываете, как база данных индексирует и хранит ваши данные.
Если вы хотите сделать это, ознакомьтесь с Руководством разработчика.
Reverse Key Indexes
Если у вас есть индекс на столбце, который хранит постоянно увеличивающиеся значения, все новые записи должны располагаться на правом краю индекса. Первичные ключи на основе последовательностей и временные метки вставки являются распространенными примерами.
Если у вас есть большое количество сессий, делающих это одновременно, это может привести к проблемам. Все они должны получить доступ к одному и тому же индексному блоку, чтобы добавить свои данные. Поскольку только один может изменить его за один раз, это может привести к конфликту. Этот термин называется "hot blocks".
Индексы с обратным ключом позволяют избежать этой проблемы, меняя порядок байтов индексируемых значений. Таким образом, вместо того чтобы хранить 12345, вы храните 54321. Чистым эффектом этого является распространение новых строк по всему индексу.
В редких случаях вам понадобится это сделать. А поскольку записи хранятся не в естественном порядке сортировки, вы можете использовать эти индексы только для запросов на равенство. Поэтому часто лучше решить эту проблему другим способом, например, с помощью хэш-разбиения индекса.
Итак, мы рассмотрели различные типы доступных индексов. Но мы все еще не разобрались с тем, какие индексы следует создавать для вашего приложения. Пришло время разобраться.
Как решить, что индексировать
Прежде всего, важно знать, что здесь нет "правильного" ответа (хотя есть несколько неправильных ;)). Вполне вероятно, что у вас будет много различных запросов к таблице. Если вы создадите индекс для каждого из них, то в итоге у вас будет масса индексов. Это проблема, поскольку каждый дополнительный индекс увеличивает накладные расходы на хранение и обслуживание данных.
Поэтому для каждого запроса необходимо найти компромисс между максимально возможной скоростью выполнения и общим воздействием на систему. Все - это компромисс!
Самое главное - помнить, что для того, чтобы база данных использовала индекс, столбец(и) должен быть в вашем запросе! Поэтому если столбец никогда не появляется в ваших запросах, его не стоит индексировать. С некоторыми оговорками:
Уникальные индексы - это действительно ограничения. Поэтому они могут понадобиться вам для обеспечения качества данных. Даже если вы не запрашиваете сами столбцы.
Вы должны индексировать все столбцы внешнего ключа, которые вы удаляете из родительской таблицы или обновляете ее первичный ключ.
Кроме того, индекс наиболее эффективен, когда его столбцы появляются в предложении where для поиска "немногих" строк в таблице.
Ключевой момент заключается в том, что важно не столько количество строк, сколько количество блоков базы данных, к которым вы обращаетесь. Как правило, индекс полезен, если он позволяет базе данных обращаться к меньшему количеству блоков, чем при полном сканировании таблицы. Запрос может вернуть "много" строк, но получить доступ к "малому количеству" блоков базы данных.
Обычно это происходит, когда логическая сортировка строк по столбцу (столбцам) запроса близко соответствует физическому порядку, в котором они хранятся в базе данных. Индексы хранят значения в этом логическом порядке. Поэтому вы обычно хотите использовать индексы там, где это происходит. Фактор кластеризации - это мера того, насколько тесно совпадают эти логический и физический порядки. Чем меньше это значение, тем более эффективным будет индекс. И, следовательно, оптимизатор будет его использовать.
Но, вопреки распространенному мнению, вам не обязательно указывать индексированный столбец (столбцы) в предложении where. Например, если у вас такой запрос:
select indexed_col from table;Oracle может выполнять полное сканирование индекса вместо таблицы. Это хорошо, потому что индексы обычно меньше, чем таблица, на которой они находятся. Но помните: null исключены из B-tree. Поэтому их можно использовать, только если у вас есть ограничение not null на столбец!
Если ваш SQL запрос использует только один столбец таблицы в предложениях join и where, то вы можете иметь одностолбцовые индексы и все.
Но реальный мир сложнее. Скорее всего, у вас относительно немного базовых запросов, таких как:
select * from tab
where col = 'value';Или:
select * from tab1
join tab2
on t1.col = t2.col
join tab3
on t2.col2 = t3.col1
where t1.other_col = 3
and t3.yet_another_col
order by t1.something_else;Как обсуждалось ранее, если вы используете Bitmap, вы можете создать индексы для одного столбца. И предоставить оптимизатору объединять их по мере необходимости. Но с B-tree этого может не произойти. А когда это происходит, работы становится больше.
Поэтому, скорее всего, вам понадобятся составные индексы. Это означает, что вам нужно подумать о том, в каком порядке расположить столбцы в индексе.
Почему?
Потому что Oracle читает индекс, начиная с самого левого ("первого") столбца. Затем он переходит ко второму, третьему и т.д. Поэтому если у вас есть индекс на:
create index i on tab ( col1, col2, col3 );И ваш where:
where col3 = 'value'Чтобы использовать индекс, база данных должна либо просмотреть все значения в col1 и col2. Или, что более вероятно, прочитать все значения, чтобы найти те, которые соответствуют вашим условиям.
Принимая это во внимание, вот несколько рекомендаций для составных индексов:
Колонки с условиями равенства (=) должны идти первыми в индексе
Те, которые имеют условия диапазона (<, >=, между и т.д.), должны идти ближе к концу
Столбцы только в пунктах select или order by должны идти последними
Приведенные здесь выводы для порядка колонок НЕ являются чугунными правилами. Специфика ваших данных и требований может привести вас к другим выводам и, соответственно, индексам. Важно понять процесс анализа компромиссов, чтобы найти "лучшие" индексы для вашего приложения.
Чтобы представить это в контексте, давайте вернемся к таблице олимпийских медалей и исследуем некоторые запросы.
Допустим, у вас есть три обычных запроса к этим данным:
select listagg(athlete,',') within group (order by athlete)
from olym_medals where medal = 'Gold';
select listagg(athlete,',') within group (order by athlete)
from olym_medals where event = '100m';
select listagg(athlete,',') within group (order by athlete)
from olym_medals where medal = 'Gold' and event = '100m';Что вы индексируете?
При наличии всего трех значений для medal и равномерном разбросе между ними индекс по этому показателю вряд ли поможет. Запросы по событию возвращают мало строк. Так что это определенно стоит индексировать.
Но как насчет запросов к обоим столбцам?
Нужно ли индексировать:
( event, medal )Или
( medal, event )Для запроса с поиском по обоим столбцам это мало что изменит. Оба варианта попадут в таблицу только для строк, соответствующих условию where.
Но порядок влияет на то, какие из других запросов будут его использовать.
Помните, что Oracle читает индексные столбцы слева направо. Поэтому если вы индексируете (medal, event), ваш запрос, ищущий всех победителей в беге на 100 метров, должен сначала считать значения medal.
Обычно оптимизатор не делает этого. Но в случаях, когда в первом столбце мало значений, он может обойти его при так называемом сканировании с пропуском индекса.
Использование ( event, medal ) имеет преимущество более целенаправленного индекса по сравнению с просто ( event ). И это дает небольшое сокращение работы запроса по обоим столбцам по сравнению с индексом только по event.
Так что у вас есть выбор. Вы индексируете только event, или event и medal?
Индекс на обоих столбцах немного сокращает объем работы при запросах на event и medal.
Но стоит ли иметь два индекса для такой экономии?
Помимо того, что составной индекс занимает больше дискового пространства, увеличивает накладные расходы на DML и т.д., есть еще одна проблема. Коэффициент кластеризации для event_medal почти в 3 раза выше, чем для medal!
Так что же в этом плохого?
Фактор кластеризации является одним из ключевых факторов, определяющих, насколько "привлекательным" является индекс. Чем он выше, тем меньше вероятность того, что оптимизатор выберет именно его. Если вам не повезет, этого может быть достаточно, чтобы оптимизатор решил, что полное сканирование таблицы дешевле...
Конечно, если вам действительно нужно, чтобы SQL запрос, ищущий золотых призеров в определенных соревнованиях, был сверхскоростным, используйте композитный индекс. Но в данном случае, возможно, лучше пойти на шаг дальше. Добавьте к индексу еще и спортсмена. Таким образом, Oracle сможет ответить на запрос, обратившись только к индексу. Избежав обращения к таблице, вы сэкономите немного работы. В большинстве других случаев я бы придерживался одноколоночного индекса событий.
Рассмотрим другой пример. Допустим, ваш доминирующий запрос заключается в поиске победителей для заданных событий в определенном году:
select listagg(athlete,',') within group (order by athlete)
from olym_medals where edition = 2000 and event = '100m';В большинстве случаев это дает три или шесть строк. Даже если речь идет о досадных командных соревнованиях с большим количеством спортсменов, получающих медали, вы получите не более 100 строк. Поэтому составной индекс - хорошая идея.
Но в каком порядке вы должны расположить колонки?!
Если у вас нет других запросов к этим столбцам, это не имеет значения. Но есть и другой аспект, который необходимо учитывать:
Сжатие индекса
Вы можете сжать индекс. Это эффективно удаляет дубликаты в его ведущем столбце (столбцах). При условии, что для каждого значения имеется "много" строк, это может значительно уменьшить размер индекса. Это экономит дисковое пространство. А это значит, что при запросе нужно сканировать меньше данных. Это может уменьшить объем работы, выполняемой вашим SQL запросом.
Итак, какую колонку следует поставить на первое место и сжать? Edition или event?
Для edition существует гораздо больше строк, чем для event. Так что это должно быть первым, верно?
Давайте посмотрим. Восстановление индекса с помощью "compress N" сжимает первые N столбцов. Поэтому, чтобы сжать ведущий столбец, нужно выполнить:
alter index olym_event_year_i rebuild compress 1;
alter index olym_year_event_i rebuild compress 1;Что приводит к уменьшению размеров индекса.
Размещение события на первом месте почти вдвое сокращает размер индекса по сравнению с edition. Несмотря на то, что в среднем на edition приходится в три раза больше строк, чем на event.
Так почему же? Все дело в длине значений.
Некоторые evnt имеют длинные описания, например, тяжелая атлетика "75 - 82,5 кг, один-два рывка руками 3 э. (полутяжелый-тяжелый вес)". Это приводит к тому, что средняя длина event составляет 15 байт. Почти в четыре раза больше, чем 4 байта, необходимые для хранения лет.
Поэтому нужно смотреть на то, насколько сжимаемы значения, а не только на то, сколько их у вас.
Конечно, в этом случае можно пойти дальше и сжать оба столбца обоих индексов с помощью функции:
alter index olym_event_year_i rebuild compress 2;
alter index olym_year_event_i rebuild compress 2;Поэтому вопрос о том, какой столбец разместить первым, остается открытым. Проверьте свой другой SQL и посмотрите, не подталкивает ли это вас к тому, чтобы поместить один или другой столбец первым.
До сих пор мы говорили только о проверке равенства. Часто в SQL запросах вы ищете диапазон значений в одном из столбцов. Например, поиск всех победителей в беге на 100 м после 2000 года:
select * from olym_medals
where event = '100m' and edition > 2000;В этом случае лучше создать индекс по (event, edition), а не наоборот. Это связано с тем, что база данных может сначала определить только записи о беге на 100 метров. Затем отфильтровать те, которые относятся к Олимпийским играм этого века. В отличие от поиска всех записей после 2000 года. Затем отфильтровать их, чтобы найти победителей в беге на 100 м.
Помните: приведенные здесь рекомендации по порядку колонок относятся только к данному набору данных. Важнее понять сам процесс и вопросы, которые необходимо задать. Например:
Какой столбец (столбцы) фигурирует в предложении where большинства запросов?
Возвращают ли условия по этим столбцам достаточно маленький набор строк и доступ к достаточно малому количеству блоков, чтобы индекс был полезен?
Какие столбцы в индексе наиболее сжимаемы?
Какие операторы SQL вы хотите сделать приоритетными для производительности?
Итак, вы создали индексы. И вы хотите оценить, помогают они или нет. Таким образом, остается важный вопрос:
Как узнать, какие индексы использовались в запросе?
Чтобы выяснить это, необходимо просмотреть план выполнения запроса. Он показывает все шаги, которые предприняла база данных для обработки запроса. Если оптимизатор выбрал индекс, он будет отображаться в плане как шаг.
Например, этот план показывает запрос, использующий индекс orders_i для доступа к таблице orders:
------------------------------------------------
| Id | Operation | Name |
------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| ORDERS |
| 2 | INDEX RANGE SCAN | ORDERS_I |
------------------------------------------------На этом этапе вы можете обнаружить, что оптимизатор не выбрал ваш индекс. Вы уверены, что это "правильный" индекс и SQL должен его использовать. Поэтому вы можете задаться вопросом:
Как заставить SQL-запрос использовать индекс?
Я уверен, что некоторые из вас сейчас испытывают искушение потянуться к подсказке индекса. Это дает указание базе данных использовать индекс. Это может быть полезно для того, чтобы проверить, действительно ли этот план более эффективен, чем тот, который выбрал оптимизатор.
Но я настоятельно не рекомендую использовать их в продакшене. Хотя они называются подсказками, на самом деле это директивы. База данных должна использовать индекс с подсказкой, если она в состоянии это сделать. Ваши данные могут измениться, в результате чего индекс будет работать медленнее, чем полное сканирование таблицы. Но запрос все равно вынужден его использовать. Это может создать для вас проблемы с обслуживанием в будущем.
Но что если ваш индекс действительно обеспечивает более быстрый план? Вы хотите каким-то образом заставить базу данных пойти по правильному пути, верно?
В этом случае я бы направил вас в сторону SQL Plan Management. Использование SQL Baselines гарантирует, что запросы используют нужный вам план, со встроенным процессом перехода на более быстрый план, если он доступен.
Заключительные слова
Сведите количество создаваемых индексов к минимуму.
Трудно доказать, что ваше приложение не нуждается в существующем индексе. Причины создания редко используемых индексов со временем забываются. Люди будут приходить и уходить из вашей команды. И никто не будет знать, почему кто-то создал это чудовище из пяти столбцов на основе функций. Вы не знаете, не используется ли он никогда, или он критически важен для отчетности на конец года и ни для чего другого.
Поэтому, как только вы создадите индекс в продакшене, отбрасывать его рискованно. Это может привести к странному случаю, когда в таблице больше индексов, чем столбцов!
Такие достижения, как невидимые индексы в 11.1 (версия Oracle) и улучшенный мониторинг индексов в 12.2, помогают снизить этот риск. Но он никогда не исчезает полностью. Если разрываться между созданием двух "идеальных" индексов и одного "достаточно хорошего", я обычно выбираю тот, который достаточно хорош.
В конце концов, если окажется, что вам действительно нужен более целевой индекс, я уверен, что ваши клиенты быстро расскажут вам, насколько медленно работает ваше приложение!
Но прежде всего, тестируйте!
Перевод статьи: How to Create and Use Indexes in Oracle Database Автор: Крис Саксон.