Написание эффективного SQL-кода

Как писать эффективный SQL код в приложениях под базы данных OracleОдним из самых искусных и приносящих наибольшее удовлетворение аспектов в работе администратора баз данных является оказание помощи в улучшение качества SQL-кода в приложении. Эффективный код означает быструю производительности, а простым способом снижения количества требуемых запросом операций ввода-вывода является уменьшение количества подлежащих изучению оптимизатором строк. Оптимизатор подбирает оптимальный план на основании запроса. Это означает, что он не будет переписывать написанный неэффективным образом запрос — он будет только лишь создавать для него план выполнения. Даже если запрос написан эффективно, оптимизатор может не всегда создавать для него наилучший план выполнения. Администратор лучше разбирается в приложении и данных, чем оптимизатор, и потому может, с помощью подсказок, заставить оптимизатор пользоваться своими знаниями. В следующих разделах описываются некоторые наилучшие методики для написания эффективного SQL-кода.

 

Эффективные конструкции WHERE

Критерии выбора, указываемые в конструкциях WHERE, могут значительно уменьшать объем данных, которые Oracle нужно просматривать во время выполнения запроса. Для обретения уверенности в эффективности структуры SQL-операторов можно следовать нескольким простым принципам. С методами соединения может быть все в порядке, но из-за несоблюдения некоторых из этих принципов, оператор все равно может оказываться обреченным с точки зрения производительности.

Тщательное задание условий WHERE оказывает значительное влияние на то, будет ли оптимизатор выбирать существующие индексы. Здесь ключевую роль играет принцип избирательности (selectivity), т.е. количество строк, возвращаемых запросом по сравнению с общим количеством строк в таблице (в процентах). Низкий процент означает высокую степень избирательности, а высокий — соответственно, низкую. Из-за того, что более избирательные конструкции WHERE предполагают меньшее количество операций ввода-вывода, CBO склонен отдавать предпочтение больше таким конструкциями, чем другим в том же самом запросе. Ниже приведен пример, показывающий, что имеется в виду: 

SQL> SELECT * FROM national_employees
WHERE ss_no = 515086789
AND city='DALLAS';

В этом примере присутствуют две конструкции WHERE, но можно видеть, что первая конструкция WHERE, в которой содержится условие ss_no = 515086789, требует меньшего количества операций ввода-вывода. Столбец ss_no является первичным ключом и имеет очень высокую степень избирательности: с таким значением ss_no во всей таблице присутствует только одна строка. Оптимизатор будет определять степень избирательности каждого из двух указанных в этом запросе столбцов за счет изучения статистических данных по индексам, которые скажут ему, сколько строк в таблице содержит каждое из двух указанных значений столбцов. Если ни у одного из этих столбцов нет индекса, Oracle использует для получения ответа на запрос операцию полного сканирования таблицы. Если же индексы есть у обоих, тогда Oracle использует имеющий более высокую степень избирательности (и, следовательно, являющийся более эффективным) индекс столбца ss_no.

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

  1. Представления в запросе иногда могут предотвращать использование индексов оптимизатором. Поэтому сначала нужно заглядывать в план выполнения и проверять, применяются ли корректные индексы.
  2. Если известно о сильной асимметричности данных в таблице, следует использовать гистограммы для обеспечения Oracle более точным представлением о распределении данных в таблице. CBO предполагает, что данные столбцов распределяются равномерно и из-за этого может не прибегать к применению индекса даже тогда, когда значение столбца является избирательным, потому что сам столбец является неизбирательным по своей природе. Гистограммы помогают тем, что обеспечивают CBO более точной картиной распределения данных столбцов. Более подробно о них будет рассказываться позже в этой статье, в разделе “Использование гистограмм”.
  3. Если Oracle по-прежнему отказывается использовать индекс, тогда нужно заставить это делать за счет добавления соответствующей подсказки, как будет объясняться в разделе “Использование подсказок для оказания влияния на план выполнения” далее в моем блоге.

На заметку! Причины, по которым Oracle не использует индекс, бывают не всегда очевидными. Например, Oracle может не использовать индекс из-за того, что индексированные столбцы являются частью списка IN и последующее преобразование препятствует применению индекса.


В случае применения конструкции WHERE, подобной WHERE last_name LIKE '%MA%', оптимизатор может решить пропускать индекс и выполнять полное сканирование таблицы из-за необходимости осуществлять для извлечения данных сопоставление с образцом по всему столбцу LAST_NAME. Оптимизатор правильно считает, что в таком случае лучше идти дальше и просматривать всю таблицу, вместо того, чтобы считывать значения и из индекса, и из таблицы. Например, при наличии в таблице 1000 строк, размещенных в 200 блоках, и выполнении полного сканирования таблицы, исходя из того, что для DB_FILE_MULTIBLOCK_READ_COUNT в базе данных было установлено значение 8, считывание всей таблицы обойдется всего лишь в 25 операций ввода-вывода. Если индекс обладает низкой степенью избирательности, сначала придется считывать большую часть индекса. Если индекс состоит из 40 листовых блоков и сначала для получения индексированных данных из них требуется прочитать 90%, тогда количество операций ввода-вывода составит 32. Кроме этого, еще потребуется выполнять дополнительные операции ввода-вывода для считывания значений из таблицы. Полное же считывание таблицы обойдется всего лишь в 25 операций ввода-вывода, что делает его гораздо более эффективным вариантом по сравнению с использованием индекса. Следует иметь в виду, что просто существование индекса по столбцу совершенно не гарантирует того, что он будет применяться постоянно.

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

 

Использование SQL-функций

В случае использования в конструкции WHERE функций SQL (например, SUBSTR, INSTR, TO_DATE и TO_NUMBER) оптимизатор Oracle будет игнорировать индекс по данному столбцу. Поэтому при возникновении необходимости применить в конструкции WHERE функции SQL, нужно обязательно использовать специальный индекс на основе функций (function-based index).

 

Использование правых соединений

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

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

 

Использование оператора CASE

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

 

Эффективное выполнение подзапросов

Подзапросы выполняются лучше, когда используется конструкция IN, а не EXISTS. В Oracle рекомендуют использовать конструкцию IN, если подзапрос содержит избирательную конструкцию WHERE, а если ее содержит родительский запрос — тогда конструкцию EXISTS.

 

Использование конструкции WHERE вместо HAVING

Везде, где возможно, лучше применять конструкцию WHERE, а не HAVING. Конструкция WHERE ограничивает количество извлекаемых строк с самого начала. Конструкция HAVING заставляет извлекать гораздо больше строк, чем требуется, а также приводит к увеличению накладных расходов из-за сортировки и суммирования.

 

Сведение к минимуму поисков по таблицам

Один из главных постулатов в теории написания запросов гласит: “Посещайте данные как можно меньшее количество раз”. Это означает, что нужно избавляться от SQL- кода, предусматривающего получение доступа к таблице снова и снова для работы со значениями различных столбцов. Лучше использовать вместо этого многостолбцовые обновления (multicolumn updates).

 

Использование подсказок для оказания влияния на план выполнения

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

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

  • ALL_ROWS. Эта подсказка указывает Oracle оптимизировать производительность (т.е. сводить к минимуму общую стоимость), а не время отклика оператора.
  • FIRST_ROWS(n). Эта подсказка указывает Oracle быстро возвращать первые n строк. Ее задачей является достижение низкого времени отклика.

На заметку! При указании подсказки ALL_ROWS или FIRST_ROWS(n) она переопределяет текущее значение параметра OPTIMIZER_MODE, если то отличается от содержащегося в ней самой.


  • FULL. Эта подсказка указывает Oracle выполнять для таблицы полное сканирование и игнорировать любые индексы, которые могут существовать. Подобное поведение может быть выгодным, если есть причины полагать, что использование индекса в таком случае будет неэффективным по сравнению с полным сканированием таблицы. Другими словами, подсказка FULL заставляет Oracle выполнять полное сканирование таблицы.
  • ORDERED. Эта подсказка вынуждает Oracle применять для таблиц в запросе определенный порядок соединения.
  • INDEX. Эта подсказка вынуждает Oracle выполнять сканирование индексов, даже если оптимизатор по какой-то причине собирался игнорировать индексы или осуществлять вместо этого полное сканирование таблиц.
  • INDEX_FFS. Эта подсказка вынуждает Oracle выполнять быстрое полное сканирование индекса (Fast Full Scan — FFS), подобное полному сканированию таблицы, при котором сканируется сразу по несколько блоков одновременно. При быстром полном сканировании индекса все блоки в индексе сканируются посредством многоблочного ввода-вывода, размер которого зависит от значения параметра DB_FILE_MULTIBLOCK_READ_COUNT. Кроме того, быстрое полное сканирование индекса можно выполнять параллельно, из-за чего оно в целом является предпочтительнее полного сканирования таблицы.

Настройки OPTIMIZER_MODE определяют способ, которым оптимизатор запросов будет осуществлять оптимизацию по всей базе данных. Однако иногда из-за отсутствия точных статистических данных оптимизатор может ошибаться в своих расчетах и, как следствие, приводить к выбору плохих планов выполнения. Поэтому в подобных случаях может быть лучше предоставлять оптимизатору подсказки и переопределять действующие в базе данных общие настройки по оптимизации на уровне отдельных SQL-операторов. В Oracle Database 11g предлагается средство SQL Profile (Профиль SQL), которое позволяет собирать вспомогательную информацию с помощью технологий выборки и частичного выполнения и тем самым избегать использования подсказок. Об этом можно прочесть в блогах наших участников в статье о применение SQL Tuning Advisor для настройки SQL.

 

Выбор наилучшего метода соединения

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

 

Избежание декартовых соединений

Операции декартового соединения обычно не являются результатом намеренного планирования; вместо этого они чаще всего происходят из-за наличия логических ошибок в запросе. В частности, они производятся, когда в соединениях полностью отсутствуют какие-либо конструкции WHERE. При соединении нескольких таблиц нужно обязательно проверять, чтобы каждая участвующая в соединении таблица упоминалась в условии WHERE. Даже если соединяемые таблицы имеют небольшой размер, все равно лучше избегать операций декартового соединения, потому что они являются неэффективными. Например, при наличии 2000 строк в таблице employee и 100 строк в таблице dept, декартово соединение этих таблиц приведет к получению 2 000 * 100 = 200 000 строк.

 

Вложенные циклы

При соединении небольших подмножеств данных применение вложенных циклов (Nested Loop — NL) является идеальным вариантом. В случае возврата менее, скажем, 10 000 строк, использование операции соединения с вложенными циклами (NL join) может быть правильным методом соединения. Если оптимизатор применяет хеш соединения или операции полного сканирования таблиц, заставлять его использовать метод операции соединения с вложенными циклами можно с помощью следующей подсказки: 

SELECT /*+ USE_NL (TableA, TableB) */

 

Хеш-соединение

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

SELECT /*+ USE_HASH */

 

Соединение слиянием

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

SELECT /*+ USE_MERGE (TableA, TableB) */

 

Использование битовых индексов соединения

Битовые индексы соединения ( Bitmap Join Indexes — BJI) позволяют предварительно сохранять (prestore) результаты соединения двух таблиц в индексе и тем самым избавляться от необходимости в дорогостоящей операции соединения времени выполнения. Они предназначены специально для хранилищ данных со звездообразными схемами, но в принципе могут применяться в любом приложении при условии наличия в нем между двумя таблицами отношений типа “первичный ключ — внешний ключ”.

К конфигурации хранилища данных первичный ключ обычно находится в таблице измерений (dimension table), а внешний — в таблице фактов (fact table). Например, столбец customer_id в таблице измерений customer может быть первичным ключом, а столбец customer_id в таблице фактов — внешним. Применение битовых индексов соединения (BJI) позволит избегать выполнения соединения между этими двумя таблицами, поскольку строки, которые должны будут получены в результате этого соединения, уже будут храниться в BJI-индексе. Давайте рассмотрим здесь простой пример BJI-индекса.

Предположим, что в приложении ожидается частое использование следующего SQL-оператора: 

SQL> SELECT SUM((s.quantity)
FROM sales s, customers c
WHERE s.customer_id = c.customer_id
AND c.city = 'DALLAS';

В этом примере таблица sales представляет собой таблицу фактов, в которой содержатся все детали, касающиеся продаж продуктов, а таблица customers — таблицу измерений, в которой хранится информация о заказчиках. Столбец customer_id выступает в роли первичного ключа в таблице customers и в роли внешнего — в таблице sales, что делает эти таблицы подходящими для создания BJI-индекса.

Ниже приведен оператор, который необходимо использовать для создания BJI-индекса. Обратите внимание на строку 2, в которой указано, что индекс должен быть создан по столбцу c.city. Вот так и извлекаются те данные соединения, которые подлежат размещению в новом BJI-индексе. Из-за того, что таблица sales является секционированной, в строке 5 используется конструкция LOCAL для создания локально секционированного индекса: 

SQL> CREATE BITMAP INDEX cust_city_BJI
2 ON city (c.city)
3 FROM sales s, customers c
4 WHERE c.cust_id = s.cust_id
5 LOCAL
6* TABLESPACE users;
Index created.
SQL>

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

SQL> SELECT index_name, index_type, join_index
2 FROM dba_indexes
3* WHERE table_name='SALES';
INDEX_NAME         INDEX_TYPE  JOIN_INDEX
-----------------  ----------  ----------
CUST_CITY_BJI      BITMAP      YES
SALES_CHANNEL_BIX  BITMAP      NO
SALES_CUST_BIX     BITMAP      NO
3 rows selected.
SQL>

Благодаря тому, что новый BJI-индекс представляет собой битовый индекс, он будет использовать пространство чрезвычайно эффективным образом. Реальная выгода от применения этого индекса, однако, состоит в том, что при необходимости получить данные о продажах в том или ином городе, выполнять соединение таблиц sales и join не потребуется. Вместо этого достаточно будет просто использовать таблицу sales и новый BJI-индекс с уже готовыми результатами соединения.

 

Выбор наилучшего порядка соединения

Когда SQL-оператор включает в себя операцию соединения двух или более таблиц, порядок соединения этих таблиц является чрезвычайно важным. Управляющей таблицей (driving table) в соединении считается первая таблица, которая идет после конструкции WHERE. Управляющая таблица в соединении должна содержать фильтр, который будет исключать большинство строк. В целом, следует выбирать такой порядок соединения, при котором количество строк, подлежащих соединению с другими таблицами, получается наименьшим. То есть, в случае соединения, например, трех таблиц, та таблица, которая имеет наиболее ограничивающий фильтр, должна первой соединяться с одной из двух других таблиц. Нужно сравнивать порядки соединения, и после изучения количества строк, возвращаемого при каждом из них, выбирать тот, который является наилучшим.

Руководство по эффективному SQL под базы данных Oracle

 

Стратегия индексирования

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


На заметку! Краткий обзор основных рекомендаций по созданию индексов можно в статье “Руководство по созданию индексов”.


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


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


 

Когда индексировать

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

При необходимости принудительно наделить строки в таблице уникальностью, можно создать для этой таблицы первичный индекс (primary index). По определению столбец, выбираемый на роль первичного индекса, должен обязательно быть не нулевым и уникальным. Помимо первичного индекса, можно создавать несколько вторичных индексов (secondary indexes). Например, атрибут LAST_NAME может исполнять роль первичного индекса. Однако если в большинстве запросов встречается столбец CITY, тогда, возможно, выгодно также проиндексировать и столбец CITY. Таким образом, получается, что добавление вторичных индексов может улучшать производительность запросов. Тем не менее, обслуживание дополнительных вторичных индексов имеет и свою цену. Помимо дополнительного дискового пространства, необходимого для больших вторичных индексов, не следует забывать о том, что все выполняемые в таблице вставки и обновления будут также требовать выполнения обновления и всех индексов.

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

 

Что индексировать

Для соблюдения критериев по производительности следует стараться использовать как можно меньше индексов. Применение слишком большого количества индексов имеет свою цену, особенно в базах данных OLTP. Каждый оператор INSERT, UPDATE и DELETE приводит к внесению изменений в лежащие в основе таблицы индексы, что в некоторых случаях вполне может замедлять работу всего приложения. Для уверенности в том, что индексы будут помогать приложению, а не препятствовать его работе, в целом можно следовать приведенным ниже рекомендациям.

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

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

  • Стараться избегать индексирования столбцов, состоящих из длинных символьных строк, если только не используется средство Oracle Text.
  • Везде где можно использовать только индексные (index-only) планы, позволяющие удовлетворять запрос за счет лишь данных из самого индекса. Это требует обращать внимание на наиболее часто выполняемые запросы и создавать любые необходимые составные индексы (индексы, включающие в себя более одного столбца-атрибута).
  • Использовать вторичные индексы по тем столбцам, которые часто принимают участие в операциях ORDER BY и GROUP BY, а также операциях сортировки вроде UNION или DISTINCT.

 

Использование надлежащих типов индексов

Принятым по умолчанию или обычным типом индекса в Oracle является индекс со структурой B-дерева (B-tree index). В типичных приложениях OLTP его можно применять для удовлетворения практически всех связанных с индексированием потребностей. Однако для получения более высокой производительности порой лучше использовать вместо него специальные индексы, предназначенные для специальных видов данных. Другими словами, выбор типа индекса должен зависеть типа данных, о которых идет речь, и от природы самого приложения. В следующих подразделах приведено краткое описание нескольких альтернативных типов индексов.

 

Битовые индексы

Битовые индексы (bitmap indexes) идеально подходят для данных столбцов с низкой степенью кардинальности, т.е. для столбцов с небольшим количеством различающихся значений. Они имеют компактный размер и работают в случае таких данных лучше, чем индексы со структурой B-дерева. Однако, при условии выполнения в отношении индексируемого столбца большого количества DML-операций, они будут вызывать некоторые проблемы.

 

Индекс-таблицы

Об индекс-таблицах (index-organized tables — IOT) уже рассказывалось в этой статье. В традиционных таблицах Oracle (heap-organized tables) данные сохраняются в том порядке, в котором вставляются. Индексы обеспечивают возможность более быстрого получения доступа к строкам. Однако они также подразумевают использование большего объема пространства и требуют получения доступа как к индексу, так и к строкам самой таблицы в большинстве запросов (при невозможности получить результаты запроса только по самим проиндексированным столбцам). Индекс-таблицы предусматривают размещение всех данных таблицы в своем собственном индексе по первичному ключу и тем самым устраняют необходимость в применении отдельного индекса.

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

 

Сцепленные индексы

Сцепленные (concatenated) или составные (composite) индексы представляют собой такие индексы, в состав которых входит более одного столбца, и замечательно подходят для повышения степени избирательности предикатов WHERE. Даже в случаях, когда степень избирательности отдельных столбцов является плохой, сцепление индекса улучшает ее. Если в сцепленном индексе содержатся все те столбцы, которые указаны в списке WHERE, необходимость заглядывать в таблицу отпадает, что сокращает количество операций ввода-вывода. Однако в составном индексе требуется обязательно обращать особое внимание на порядок столбцов. В случае не указания начального столбца сцепленного индекса первым в конструкции WHERE, Oracle может вообще не использовать этот индекс.

Вплоть до недавнего времени составной индекс применялся Oracle только в случае указания его начального столбца в конструкции WHERE или сканирования всего индекса. Сегодня функциональная возможность выполнения сканирования индекса с пропусками (index skip scan) позволяет Oracle использовать составной индекс даже тогда, когда его начальный столбец не указан в запросе. Очевидно, что эта функциональная возможность является очень полезной и устраняет необходимость в выполнении многих из тех операций полного сканирования таблиц, которые в таком случае требовалось бы предпринимать в предшествующих версиях Oracle.

 

Индексы на основе функций

Индексы на основе функций (function-based indexes) содержат столбцы, преобразуемые с помощью либо какой-то функции, либо какого-то выражения Oracle. Когда функция или выражение, применяемое для создания индекса, упоминается в запросе в конструкции WHERE, Oracle может быстро возвращать вычисленное значение этой функции или выражения прямо из индекса, вместо того чтобы вычислять его каждый раз заново. Индексы на основе функций хорошо подходят для часто используемых операторов, которые предусматривают применение в отношении столбцов функций или сложных выражений. Например, ниже показан индекс на основе функций, позволяющий выполнять поиск людей на основании значения в столбце last_name (со всеми буквами в верхнем регистре): 

SQL> CREATE INDEX upper_lastname_idx ON employees (UPPER(last_name));

 

Индексы с реверсированным ключом

При наличии в базе данных проблем с производительностью из-за выполнения большого количества вставок, следует рассмотреть вариант использования индексов с
реверсированным ключом (reverse-key index). Такие индексы идеально подходят для насыщенных операциями вставки приложений, хотя и страдают от недостатка, состоящего в невозможности их применения в операциях сканирования диапазонов по индексу. Структура индекса с реверсированным ключом выглядит так:

Index value  Reverse_Key Index Value
-----------  -----------------------
9001                 1009
9002                 2009
9003                 3009
9004                 4009

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

 

Стратегия секционированного индексирования

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

  • Локальные секционированные индексы (local partitioned indexes) соответствуют лежащим в основе секциям таблицы. При добавлении новой секции в таблицу также происходит и добавление новой секции в локальный секционированный индекс.
  • Глобальные секционированные индексы (global partitioned indexes) не соответствуют секциям локальной таблицы.
  • Префиксные индексы (prefixed indexes) секционируются по левому префиксу в индексных столбцах.
  • Непрефиксные индексы (nonprefixed indexes) представляются собой такие индексы, которые не секционируются по левому префиксу в индексных столбцах.

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


На заметку! В главе 5 было показано, как использовать инструмент SQL Access Advisor (Советник по оптимизации доступа для SQL-кода) для получения советов касательно создания индексов и материализованных представлений (а также журналов материализованных представлений). Регулярное применение этого инструмента позволит легко выявлять необходимость в создании любых новых индексов или материализованных представлений (и журналов материализованных представлений).


 

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

С таблицей может ассоциироваться несколько индексов, но это само по себе не дает никакой гарантии их применения в запросах. В случае не использования индексов, от них может быть лучше избавляться, чтобы не тратить место и время на их управление. Для сбора информации об использовании индексов служит представление V$OBJECT_USAGE. Структура этого представления выглядит так:

SQL> DESC V$OBJECT_USAGE
Name               Null?     Type
-----------------  --------  ------------
INDEX_NAME         NOT NULL  VARCHAR2(30)
TABLE_NAME         NOT NULL  VARCHAR2(30)
MONITORING                    VARCHAR2(3)
USED                          VARCHAR2(3)
START_MONITORING             VARCHAR2(19)
END_MONITORING               VARCHAR2(19)
SQL>

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

 

Удаление ненужных индексов

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

 

Использование похожих SQL-операторов

Как уже известно, повторное использование уже проанализированных (прошедших этап синтаксического разбора) операторов ведет к улучшению производительности, а также экономит место в области разделяемого пула в SGA. Уловка, однако, состоит в том, что для этого SQL-операторы должны обязательно быть идентичными во всех аспектах, вплоть до пробелов и прочих подобных вещей.

 

Сокращение накладных расходов SQL с помощью встраиваемых функций

Встраиваемые хранимые функции (inline stored functions) могут помочь улучшить производительность SQL-операторов. Применение встраиваемой функции для сокращения накладных расходов SQL-оператора легче всего продемонстрировать на простом примере. Предположим, что первоначально, до добавления встраиваемых функций, SQL-оператор выглядит так:

SQL> SELECT r.emp_id,
e.name, r.emp_type,t.type_des,
COUNT(*)
FROM employees e, emp_type t, emp_records r
WHERE r.emp_id = e.emp_id
AND r.emp_type = t.emp_type
GROUP BY r. emp_id, e.name, r.emp_type, t.emp_des;

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

SQL> CREATE OR REPLACE FUNCTION select_emp_detail (type IN) number
2 RETURN varchar2
3 AS
4 detail varchar2(30);
5 CURSOR a1 IS
6 SELECT emp_detail FROM emp_type
7 WHERE emp_type = type;
8 BEGIN
9 OPEN a1;
10 FETCH a1 into detail;
11 CLOSE a1;
12 RETURN (NVL(detail,'?'));
13 END;
Function created.
SQL>

Вторая функция называется SELECT_EMP, предусматривает возврат полного имени сотрудника в случае передачи ей в качестве входного параметра значения employee_id, и создается так:

SQL> CREATE OR REPLACE FUNCTION select_emp (emp IN number) RETURN varchar2
2 AS
3 emp_name varchar2(30);
4 CURSOR a1 IS
5 SELECT name FROM employees
6 WHERE employee_id = emp;
7 BEGIN
8 OPEN a1;
9 FETCH a1 INTO emp_name;
10 CLOSE a1;
11 RETURN (NVL(emp_name,'?'));
12 END;
Function created.
SQL>

Теперь, когда обе необходимые функции готовы, остается только вызвать их внутри SQL-оператора, как показано в следующем коде:

SQL> SELECT r.emp_id, select_emp(r.emp_id),
2 r.emp_type, select_emp_desc(r.emp_type),
3 COUNT(*)
4 FROM emp_records r
5* GROUP BY r.emp_id, r.emp_type;
SQL> 

 

Использование переменных связывания

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

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

 

Избежание неправильного применения представлений

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

 

Избежание ненужных операций полного сканирования таблиц

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

Вас заинтересует / Intresting for you:

SQL: Правила выполнения однота...
SQL: Правила выполнения однота... 1442 просмотров Дэйзи ак-Макарова Sat, 31 Jul 2021, 06:47:05
Типы данных SQL: стандарт ANSI...
Типы данных SQL: стандарт ANSI... 4857 просмотров Дэн Sat, 05 Jun 2021, 09:43:17
Правила именование объектов SQ...
Правила именование объектов SQ... 9748 просмотров Дэн Sat, 05 Jun 2021, 09:02:07
Операции SQL в базе данных Ora...
Операции SQL в базе данных Ora... 6769 просмотров Antoni Wed, 11 Apr 2018, 12:22:28
Войдите чтобы комментировать

ildergun аватар
ildergun ответил в теме #10629 1 год 8 мес. назад
Отличная статья по теоретическим основам написания эффективного SLQ кода. Благодарю!