Указания базе данных в ABAP Open SQL

Михаил Прусов, Евгений Руденко

Введение

Выполнение Open SQL-оператора в ABAP-программе происходит в два этапа:

Интерфейс базы данных сервера приложений SAP-системы преобразует Open SQL-оператор в SQL-запрос.
Полученный SQL-запрос анализируется и выполняется базой данных SAP-системы.

В ходе обработки запроса могут возникнуть проблемы эффективности на каждом из этих этапов:

Интерфейс базы данных может выполнить преобразование Open SQL-оператора не самым эффективным способом.
Оптимизатор базы данных может выбрать не самый эффективный план выполнения SQL-запроса.

Для того чтобы помочь интерфейсу базы данных сервера приложений и оптимизатору базы данных, можно воспользоваться указаниями (hints).

Об указаниях в Open SQL

В некоторых базах данных существует возможность, известная как указания (hints), явным образом влиять на решения оптимизатора базы данных. Начиная с версии 4.5 в SAP-системах появилась возможность использовать подобный механизм в Open SQL-операторах. Указания в Open SQL-операторах могут предназначаться как интерфейсу базы данных и влиять на алгоритм преобразования в SQL-запрос, так и оптимизатору базы данных.

В этом разделе обсуждается возможность использования указаний, доступных из Open SQL-операторов. Описываются свойства указаний, применимые ко всем базам данных.

Указания в Open SQL-операторах применимы только к прозрачным таблицам и представлениям, основанным на прозрачных таблицах. Указания в операторах с кластерными и пул-таблицами игнорируются.
Указания в Open SQL-операторах следует рассматривать как «последнее» средство: в тех случаях, когда другие решения невозможны. Их следует применять с особой аккуратностью и только после консультаций с опытными разработчиками.
Указания разбираются в момент выполнения программы и не проверяются синтаксическим ABAP-анализатором. Таким образом, неправильно составленные указания могут стать причиной ошибок времени выполнения.
Указания всегда применимы к конкретной базе данных, часто их влияние зависит от версии базы данных.
Очень часто указания эффективны только в контексте конкретной системы, поэтому они могут использоваться в качестве стандартного решения только в очень специальных случаях.
Указание определяется в отдельном Open SQL-операторе или его подзапросе и влияет только на них. Указание влияет не на результат запроса, а лишь на способ его получения. Таким образом, неочевидно, использовалось ли конкретное указание в момент выполнения программы или нет.
Не существует указания, явно определяющего использование индекса с SAP-идентификатором «M» для доступа к таблице.

Указания могут использоваться для перечисленных ниже баз данных:

Oracle
MS SQL
DB2 UDB
Informix
DB2/390

Синтаксис

Указания вводятся последней фразой в Open SQL-операторах «SELECT», «DELETE» и «UPDATE» или подзапросах и не могут использоваться в других операторах. Указния начинаются с фразы «%_HINTS».

   SELECT [...] FROM [...]
   WHERE [...] GROUP BY [...] HAVING
   ORDER BY [...]
   %_HINTS <переключатель> '<текст>' <переключатель> '<текст>' [...].

Здесь:

Фраза «%_HINTS» содержит список пар, состоящих из переключателя базы данных и текста указания.
Переключатель является ключевым словом и может принимать одно из следующих значений:
```
   ADABAS, AS400, DB2, DB6, INFORMIX, MSSQLNT, ORACLE
```
В каждой системе используется указание с соответствующим переключателем. Другие указания игнорируются. Если указание оказывается пустым после выполнения операции подстановки, описанной ниже, или отсутствует вовсе (пробел), оно также игнорируется.
Текст указания может быть либо литеральным символом ('...'), либо переменной. Если используется переменная, то SQL-оператор становится динамическим. В этом случае эффективность кэша SQL-операторов снижается.
Для каждой базы данных возможно определить несколько указаний в одном операторе. Будут ли все они использоваться, и каким образом это произойдёт, зависит от базы данных.

Текстовые подстановки

Интерфейс базы данных вычисляет текст указания перед передачей SQL-оператора в базу данных. При оценке текста указания интерфейс может выполнить некоторые преобразования в тексте результирующего SQL-запроса в соответствие с командами подстановки. Команды подстановки начинаются с символа «&». Если в тексте указания требуется использовать символ «&», его необходимо удвоить («&&»).

&TABLE[[<блок>,]<таблица>]&

В общем случае, разработчик не может знать, к какому виду интерфейс базы данных преобразует конкретный Open SQL-оператор, и, в частности, какие синонимы (aliases) будут использоваться для обозначения таблиц. Для того чтобы однозначно определить таблицу, используйте приведённую конструкцию. Каждая такая конструкция заменяется на имя таблицы.

<таблица> — порядковый номер таблицы во фразе «FROM», номер первой таблицы равен 1. Значение по умолчанию — «1», т.е первая таблица, указанная во фразе «FROM».

<блок> — уровень вложенности фразы «FROM». Значение «0» указывает на фразу запроса (подзапроса), к которому относится указание. Значения «-1», «-2» … отсылают к внешним фразам «FROM» вложенного запроса. Значение по умолчанию — «0».

Таким образом, конструкция '&TABLE&' является синонимом '&TABLE 0,1&'.

&REPARSE&

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

&SUBSTITUTE LITERALS&

Явное указание интерфейсу базы данных включить в текст SQL-запроса все литералы. Литералами являются все ABAP-литералы, а также глобальные ABAP-константы, но не константные параметры подпрограмм. Дополнительная информация, включённая в SQL-запростаким образом, даёт возможность оптимизатору базы данных использовать статистики распределения значений табличных атрибутов.

В качестве примера рассмотрим следующий Open SQL-оператор (используется база данных Oracle):

  SELECT * FROM USR02 
  WHERE BNAME = 'DDIC'.

после преобразования интерфейсом базы данных этот оператор будет приведён к SQL-запросу:

  SELECT * FROM "USR02"                          
  WHERE "MANDT" = :A0 AND "BNAME" = :A1

При использовании указания с подстановкой '&SUBSTITUTE LITERALS&':

  SELECT * FROM USR02 
  WHERE BNAME = 'DDIC'
  %_HINTS ORACLE '&SUBSTITUTE LITERALS&'.

результирующий SQL-запрос будет иметь вид:

  SELECT * FROM "USR02"
  WHERE "MANDT" = :A0 AND "BNAME" = 'DDIC'

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

Рекомендуется проверить результирующий SQL-оператор при использовании этой подстановки, например, с помощью транзакции «ST05».

&SUBSTITUTE VALUES&

Подобно подстановке '&SUBSTITUTE LITERALS&' данная конструкция является явным указанием интерфейсу базы данных использовать литералы в формируемом SQL-запросе.

Однако, в отличие от первой, данная подстановка приводит к тому, что интерфейс базы данных будет пытаться использовать литералы в SQL-запросе не только для ABAP-литералов, но и для переменных, включённых в Open SQL-оператор. Такое поведение, с одной стороны, даёт максимальный объём информации для оптимизации запроса на основе статистик распределения значений табличных атрибутов, с другой стороны — данная конструкция снижает эффективность использования кэша SQL-операторов.

Также, как и в случае с '&SUBSTITUTE LITERALS&', существуют ограничения на передачу литералов в SQL-запрос, которые зависят от базы данных и типа атрибута таблицы.

База данных Oracle

При составлении указаний для базы данных Oracle обратите внимание на следующие моменты:

Имена таблиц и индексов должны быть заключены в двойные кавычки «"», в т.ч. конструкция '&TABLE…&'.
Несколько указаний для блока «SELECT…FROM…WHERE» объединяются в один текст. В качестве разделителя между указаниями используется пробел.
Oracle игнорирует указания, имеющие синтаксические ошибки, таким образом некорректные указания не приводят к ошибкам при выполнении запроса. В общем случае, указания могут содержать произвольный текст. Oracle рассматривает только пригодные указания, остальной текст воспринимается как комментарий. Кроме того, даже бессмысленное указание имеет эффект: для построения плана выполнения запроса оператора выбирается оптимизатор основанный на стоимости (cost-based).
Если несколько указаний противоречат дург другу, то они игнорируются.
Указание 'RULE' активизирует оптимизатор основанных на правилах (rule-based) для построения плана выполнения запроса. Все другие указания активизируют оптимизатор основанный на стоимости.

Указания интерфейсу базы данных

&REPARSE&

Может использоваться начиная с версии 4.5B.

&SUBSTITUTE LITERALS&

Может использоваться начиная с версии 4.5B.

&SUBSTITUTE VALUES&

Может использоваться начиная с версии 4.5B.

Примеры

Последующие примеры показывают синтаксис указаний Open SQL для базы данных Oracle. Кроме того, они демонстрируют важную проблему, связанную с явным указанием индекса таблицы в тексте указания.

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

   SELECT * FROM RESB
   WHERE MATNR = '200-100' AND WERKS = '1100'.

В таблице «RESB» определены два индекса: с идентификатором «0» на атрибутах («MANDT, RSNUM, RSPOS, RSART») и идентификатором «M» на атрибутах («MANDT, MATNR, WERKS, XLOEK, KZEAR, BDTER»). Для эффективного выполнения запроса должен использоваться индекс с идентификатором «M».

Вместо того, чтобы явно указывать индекс, часто достаточно «намекнуть» оптимизатору, что для выполнения запроса необходимо использовать индекс. Выбор подходящего индекса оптимизатор сделает сам:

   SELECT * FROM RESB
   WHERE MATNR = '200-100' AND WERKS = '1100'
   %_HINTS ORACLE 'FIRST_ROWS'.

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

Более явной, указывающей на необходимость доступа к таблице с использованием индекса, является следующая конструкция:

   SELECT * FROM RESB
   WHERE MATNR = '200-100' AND WERKS = '1100'
   %_HINTS ORACLE 'INDEX("&TABLE&")'.

Здесь указание говорит о необходимости использования индекса. Оптимизатор сам выбирает наиболее подходящий индекс.

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

   SELECT * FROM RESB
   WHERE MATNR = '200-100' AND WERKS = '1100'
   %_HINTS ORACLE 'INDEX("&TABLE&" "RESB~M" "RESM^M")'.

Основная трудность заключается в том, что существует несколько форм именования индексов в SAP-системах. Приведенные в примере имена должны использоваться в любом случае, однако вы не должны забывать о форме имён индексов с символами подчеркивания.

DB6 (DB2 UDB)

При составлении указаний для базы данных DB2 UDB обратите внимание на следующие моменты:

Некорректные указания не приводят к ошибкам и игнорируются.
Используйте только прописные буквы к тексте указаний.
'use_optlevel' не рассматривается как правильное указание.

Указания интерфейсу базы данных

OPT_FOR_ROWS n

Может использоваться начиная с версии 4.6A.

Уведомляет интерфейс базы данных, что к SQL-оператору должна быть добавлена фраза «OPTIMIZE FOR n ROWS» (сокращенно: OFnR).

Фраза OFnR делает следующее:

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

Используйте указание 'OPT_FOR_ROWS', если выполняются одно или несколько следующих условий:

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

Дополнительную информацию о фразе «OPTIMIZE FOR n ROWS» смотрите в документации по DB2.

USE_OPTLEVEL x

Может использоваться начиная с версии 4.6A.

Оптимизатор базы данных знает о нескольких уровнях оптимизации (сокращенно: Optlevel). Уровень оптимизации определяет, как быстро строится план доступа. Более высокий уровень необязательно даст более эффективный план доступа к данным, однако более выскоий уровень приведёт более длительному времени подготовки плана доступа.

SAP R/3 работает со стандартным уровнем оптимизации, гарантирующим хороший баланс между временем подготовки плана и его выполнением. Однако, для отдельных SQL-операторов, более удачным может быть выбор другого уровня оптимизации. Например, смена уровня может быть сделана в случае, когда оптимизатор выбирает полное сканирование таблицы, хотя проведённые эксперименты показывают, что доступ по индексу эффективнее.

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

&SUBSTITUTE LITERALS&

Может использоваться начиная с версии 4.6D. При использовании ядра 4.6D убедитесь, что уровень ядра не ниже, чем 1033, а в профайле системы установлен параметр:

   dbs/db6/dbsl_substitute_literals = 1

&SUBSTITUTE VALUES&

Может использоваться начиная с версии 4.6D. Смотрите замечания к указанию '&SUBSTITUTE LITERALS&'.

CONVERT_FAE_TO_CTE

Может использоваться начиная с версии 4.6D. Для версий 4.6D и 6.20 в описании ядра должен присутствовать текст:

   DB6: new hint 'CONVERT_FAE_TO_CTE'

Указание оказывает влияние только на ABAP-оператор «SELECT» с фразой «FOR ALL ENTRIES». Такие операторы, содержащие в секции «WHERE», по крайней мере, два поля таблицы из фразы «FOR ALL ENTRIES», обычно отображаются в SQL-запрос следующей формы:

  SELECT ... FROM TAB WHERE K1 = ? and K2 = ? ...
  UNION ALL
  SELECT ... FROM TAB WHERE K1 = ? and K2 = ? ...
  UNION ALL
  ...

Число фраз «SELECT» в таких конструкциях зависит от числа записей во внутренней таблице указанной в «FOR ALL ENTRIES». Дублирующие записи удаляются из результата сервером приложений.

Указание 'CONVERT_FAE_TO_CTE' приводит к тому, что интерфейс базы данных формирует SQL-оператор с использованием возможности "Common Table Expression":

  WITH DB6#CTE ( DB6#_1 , DB6#_2 , ... ) AS (
    VALUES ( ?, ? , ... )
  UNION
    VALUES ( ?, ? , ... )
  UNION
  ... )
  SELECT ... FROM TAB, DB6#CTE WHERE K1 = DB6#_1 and K2 = DB6#_2 ...

Такой оператор делает повторяющуюся структуру запроса более очевидной для оптимизатора базы данных DB2. Другим преимуществом такого SQL-оператора является то, что дублирующие записи удаляются из вспомогательной таблицы «DB6#CTE» перед обращением к таблице базы данных «TAB».

Указание 'CONVERT_FAE_TO_CTE' не влияет на ABAP-операторы, содержащие фразу «LIKE», а также операторы выбираюищие данные из более чем одной таблицы.

Примеры

В запросе

  SELECT * FROM RESB WHERE MATNR = '200-100' AND WERKS = '1100'.

можно сообщить оптимизатору, что необходимы только 15 записей из результирующего набора:

  SELECT * FROM RESB WHERE MATNR = '200-100' AND WERKS = '1100'
  %_HINTS DB6 'OPT_FOR_ROWS 15'.

Этот оператор может быть запущен с уровнем оптимизации 0 для того, чтобы форсировать использование индекса:

  SELECT * FROM RESB WHERE MATNR = '200-100' AND WERKS = '1100'
  %_HINTS DB6 'USE_OPTLEVEL 0'.

Литералы, определённые в ABAP-операторе могут быть переданы базе данных для того, чтобы помочь оптимизатору выбрать план выполнения запроса:

  SELECT * FROM RESB WHERE MATNR = '200-100' AND WERKS = '1100'
  %_HINTS DB6 '&SUBSTITUTE LITERALS&'.

Если необходимо ввести несколько указаний, их необходимо разместить ОТДЕЛЬНО:

  SELECT * FROM RESB WHERE MATNR = '200-100' AND WERKS = '1100'
  %_HINTS DB6 'USE_OPTLEVEL 0'
          DB6 'OPT_FOR_ROWS 65'.

Следующий оператор приведёт к формированию SQL-запроса с использованием возможности "Common Table Expression" базы данных:

  SELECT * FROM RESB WHERE MATNR = '200-100' AND WERKS = '1100'
  %_HINTS DB6 'USE_OPTLEVEL 0'
          DB6 'OPT_FOR_ROWS 65'.

Ссылки

«Нота» 129385: Database hints in Open SQL.
«Нота» 130480: Database hints in Open SQL for Oracle.
«Нота» 150037 : Database hints in Open SQL for DB6 (DB2 UDB).

Сайт создан в системе uCoz