Чтение из таблицы с выбором по неключевому полю; INDEX ; %_HINTS

Jouri

Добрый день,

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

Допустим есть таблица (скажем MSEG) и надо выбрать
по неключевоьу полю MATNR и для MATNR есть индекс M
(MSEG-M)....

Вопрос:
Достаточно написать просто
select * from MSEG where MATNR = '4711' into...

и система поймёт автоматически что для MATNR
есть индекс и применит его или надо как то
это сообщить системе особым образом ???

Вопрос пришёл после того как я увидел в одной
системе следущий SQL:

SELECT yyttcode FROM vepo
INTO CORRESPONDING FIELDS OF TABLE ivepo_tlp
FOR ALL ENTRIES IN ivekp_tlp
WHERE unvel = ivekp_tlp-venum
AND yyttcode IN ('YTVK010','YTVK011')
GROUP BY yyttcode
%_HINTS ORACLE
'INDEX("&TABLE&" "VEPO______A" "VEPO~A" "VEPO^A")' .

?????????????????????????????????????????????????????????????????
Что есть
%_HINTS ORACLE
'INDEX("&TABLE&" "VEPO______A" "VEPO~A" "VEPO^A")' .
?????????????????????????????????????????????????????????????????

заранее спасибо
Юрий

Alex80

Jouri написал:

Что есть
%_HINTS ORACLE
'INDEX("&TABLE&" "VEPO______A" "VEPO~A" "VEPO^A")' .

Юрий

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

Jouri

4o to ne ponial...

OPEN SQL eto OPEN SQL
esli ja pishu SELECT....
ra3we eto OPEN SQL?

3a4em napisan %_HINT.....????

Alex80

Alex80 написал:

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

Jouri написал:

3a4em napisan %_HINT.....????

Два человека могут смотреть на одно и тоже, а видеть это по разному.

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

Jouri написал:

4o to ne ponial...
OPEN SQL eto OPEN SQL
esli ja pishu SELECT....
ra3we eto OPEN SQL?

Вы, верно заметили - OPEN SQL это OPEN SQL.
Я, обычно, пишу оператор SELECT в транзакции "SE38 - ABAP-редактор"
и подразумеваю под этим: "Effect
Reads a selection and/or a summary of data from one or more database tables and/or views (see relational database). SELECT is an Open SQL statement. " (с) Документация к ключевым словам ABAP

gasjuk

Вопрос по ссылке понятен,ответ нет.Какой синтакис?Что это %_HINTS?

Удав

См. ноту 772497 - FAQ Oracle Hints

zsap

Jouri написал:

Допустим есть таблица (скажем MSEG) и надо выбрать
по неключевоьу полю MATNR и для MATNR есть индекс M
(MSEG-M)....

Вопрос:
Достаточно написать просто
select * from MSEG where MATNR = '4711' into...

Тут важна позиция поля MATNR в индексе. Если к таблице есть индекс, в котором первые 2 поля это MANDT и MATNR, он будет использован для такого запроса и без HINTS. В случае если в индексе перед MATNR идут какие-то другие поля, без HINTS он может быть задействован, а может и нет. Например, индекс с полями MANDT, MBLNR, MATNR не имеет смысла использовать для поиска по MATNR. Обычно правильная БД сама определяет какой индекс использовать, так что в HINTS нет особой необходимости

Удав

zsap написал:

Тут важна позиция поля MATNR в индексе. Если к таблице есть индекс, в котором первые 2 поля это MANDT и MATNR, он будет использован для такого запроса и без HINTS. В случае если в индексе перед MATNR идут какие-то другие поля, без HINTS он может быть задействован, а может и нет. Например, индекс с полями MANDT, MBLNR, MATNR не имеет смысла использовать для поиска по MATNR. Обычно правильная БД сама определяет какой индекс использовать, так что в HINTS нет особой необходимости

Как раз связка MKPF и MSEG нуждается в HINTS, если в критериях выбора есть и поля из MKPF, и поля из MSEG

Хотя бы SUBSTITUTE_VALUES, чтобы оптимизатор правильно определял заполненные SELECT-OPTIONS.

zsap

Удав, не понял вашей логики. Чем MKPF и MSEG принципиально отличаются от других таблиц? И как можно неправильно определить заполненные SELECT-OPTIONS? Оно либо заполнено, либо нет. В последнем случае в where просто ничего не прописывается. SUBSTITUTE_VALUES приведет только к тому что план выполнения запроса не будет кешироваться, не совсем понял, каким образом это помогает в оптимизации

Удав

Без substitute_values:
1. интерфейсу с БД будут передаваться все параметры из WHERE, поиск индекса будет осуществляться без учета значений.
2. ORACLE(как минимум) не сможет использовать гистограммы для поиска оптимального индекса.

А то, что план запроса кэшируется - как раз вредно в многопользовательской среде, где одновременно будут выполняться много запросов по разным параметрам.

zsap

Удав написал(а):

Без substitute_values:
1. интерфейсу с БД будут передаваться все параметры из WHERE, поиск индекса будет осуществляться без учета значений.
2. ORACLE(как минимум) не сможет использовать гистограммы для поиска оптимального индекса.

А то, что план запроса кэшируется - как раз вредно в многопользовательской среде, где одновременно будут выполняться много запросов по разным параметрам.

1 и 2 в случае с Ораклом не актуально с версии 9i, т.к. он научился считывать значения из переменных связывания. Что касается гистограмм, для начала нужно заморочиться и собрать статистику по полям, без этого не получится их использовать

Удав

zsap написал:

1 и 2 в случае с Ораклом не актуально с версии 9i, т.к. он научился считывать значения из переменных связывания.

При чем здесь переменные связывания, если Oracle видит такой запрос:

Code:

SELECT
  T_00 . "MBLNR" , T_00 . "MJAHR" , T_01 . "ZEILE" , T_00 . "BUDAT" ,   T_00 . "CPUDT" ,
  T_00 . "CPUTM" , T_01 . "BWART" , T_01 . "XAUTO" ,   T_01 . "MATNR" , T_01 . "WERKS" ,
  T_01 . "LGORT" , T_01 . "SOBKZ" ,   T_01 . "LIFNR" , T_01 . "SHKZG" , T_01 . "SHKUM" ,
  T_01 . "WAERS" ,   T_01 . "DMBTR" , T_01 . "BWTAR" , T_01 . "MENGE" , T_01 . "MEINS" ,
  T_01 . "EBELN" , T_01 . "EBELP" , T_01 . "SJAHR" , T_01 . "SMBLN" ,   T_01 . "SMBLP" ,
  T_01 . "KZBEW" , T_01 . "KZZUG" , T_01 . "LBKUM" ,   T_01 . "SALK3" , T_01 . "MAT_KDAUF" ,
  T_01 . "MAT_KDPOS" ,   T_01 . "MAT_PSPNR" , T_01 . "UMWRK" , T_01 . "CHARG" , T_00 . "BKTXT" ,
  T_01 . "SGTXT"
FROM
  "MKPF" T_00 , "MSEG" T_01
WHERE ( T_01 . "MANDT" = :A0 AND T_01 . "MBLNR" = T_00 . "MBLNR" AND T_01 .   "MJAHR" = T_00 . "MJAHR" )
  AND T_00 . "MANDT" = :A0 AND T_00 . "BUDAT"   BETWEEN :A1 AND :A2 AND T_01 . "WERKS" = :A3

Code:

 SELECT STATEMENT ( Estimated Costs = 41 , Estimated #Rows = 16 )

        7 FILTER
          Filter Predicates

            6 TABLE ACCESS BY LOCAL INDEX ROWID MSEG
              ( Estim. Costs = 1 , Estim. #Rows = 1 )
              Filter Predicates

                5 NESTED LOOPS
                  ( Estim. Costs = 41 , Estim. #Rows = 16 )

                    2 TABLE ACCESS BY INDEX ROWID MKPF
                      ( Estim. Costs = 5 , Estim. #Rows = 120 )

                        1 INDEX RANGE SCAN MKPF~BUD
                          ( Estim. Costs = 4 , Estim. #Rows = 216 )
                          Search Columns: 2
                          Access Predicates

                    4 PARTITION RANGE ITERATOR
                      Pstart: KEY Pstop: KEY

                        3 INDEX RANGE SCAN MSEG~0
                          ( Estim. Costs = 2 , Estim. #Rows = 1 )
                          Pstart: KEY Pstop: KEY
                          Search Columns: 3
                          Access Predicates

А при substitute_values получается быстрее, хоть и cost больше получился:

Code:

SELECT
  T_00 . "MBLNR" , T_00 . "MJAHR" , T_01 . "ZEILE" , T_00 . "BUDAT" ,
  T_00 . "CPUDT" , T_00 . "CPUTM" , T_01 . "BWART" , T_01 . "XAUTO" ,
  T_01 . "MATNR" , T_01 . "WERKS" , T_01 . "LGORT" , T_01 . "SOBKZ" ,
  T_01 . "LIFNR" , T_01 . "SHKZG" , T_01 . "SHKUM" , T_01 . "WAERS" ,
  T_01 . "DMBTR" , T_01 . "BWTAR" , T_01 . "MENGE" , T_01 . "MEINS" ,
  T_01 . "EBELN" , T_01 . "EBELP" , T_01 . "SJAHR" , T_01 . "SMBLN" ,
  T_01 . "SMBLP" , T_01 . "KZBEW" , T_01 . "KZZUG" , T_01 . "LBKUM" ,
  T_01 . "SALK3" , T_01 . "MAT_KDAUF" , T_01 . "MAT_KDPOS" ,
  T_01 . "MAT_PSPNR" , T_01 . "UMWRK" , T_01 . "CHARG" , T_00 . "BKTXT" ,
  T_01 . "SGTXT"
FROM
  "MKPF" T_00 , "MSEG" T_01
WHERE
  ( T_01 . "MANDT" = '200' AND T_01 . "MBLNR" = T_00 . "MBLNR" AND T_01 .
  "MJAHR" = T_00 . "MJAHR" ) AND T_00 . "MANDT" = '200' AND T_00 . "BUDAT"
  BETWEEN '20090901' AND '20100331' AND T_01 . "WERKS" = '1250'

Code:

SELECT STATEMENT ( Estimated Costs = 154 , Estimated #Rows = 153 )

       8 MERGE JOIN
         ( Estim. Costs = 154 , Estim. #Rows = 153 )

           4 SORT JOIN
             ( Estim. Costs = 96 , Estim. #Rows = 1 219 )

               3 PARTITION RANGE ALL
                 Pstart: 1 Pstop: 5

                   2 TABLE ACCESS BY LOCAL INDEX ROWID MSEG
                     ( Estim. Costs = 56 , Estim. #Rows = 1 219 )
                     Pstart: 1 Pstop: 5

                       1 INDEX RANGE SCAN MSEG~ZZ5
                         ( Estim. Costs = 17 , Estim. #Rows = 1 219 )
                         Pstart: 1 Pstop: 5
                         Search Columns: 2
                         Access Predicates

           7 SORT JOIN
             ( Estim. Costs = 58 , Estim. #Rows = 1 947 )
             Access Predicates Filter Predicates

               6 TABLE ACCESS BY INDEX ROWID MKPF
                 ( Estim. Costs = 40 , Estim. #Rows = 1 947 )

                   5 INDEX RANGE SCAN MKPF~BUD
                     ( Estim. Costs = 17 , Estim. #Rows = 1 )
                     Search Columns: 2
                     Access Predicates

zsap

Удав написал(а):

zsap написал:

1 и 2 в случае с Ораклом не актуально с версии 9i, т.к. он научился считывать значения из переменных связывания.

При чем здесь переменные связывания, если Oracle видит такой запрос:

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

Удав написал(а):

А при substitute_values получается быстрее, хоть и cost больше получился

Странно что у вас cost больше получился, интересно было бы сравнить реальное время работы запроса

Удав

zsap написал:

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

Он эти значения не использует при выборе индексов

В ST05 при трассировке реальных запросов всегда есть операции PREPARE и OPEN.
В случае с SUBSTITUTE_VALUES в PREPARE передаются реальные значения, без него - абстрактные переменные :A0, :A1 и т.п.

zsap написал:

Странно что у вас cost больше получился, интересно было бы сравнить реальное время работы запроса

Выигрыш для MKPF+MSEG процентов до 40 при реальном выполнении, за счет правильного подбора индексов и меньшего количества операций поиска по индексам.
Это при результирующем количестве строк >1000.

Вот кстати, что сам SAP пишет про связку MKPF+MSEG:
Note 921164 - MB51: Improving the runtime using database hints
Note 902675 - MB51: Database hints to improve runtime / 4.70

zsap

Удав написал(а):

zsap написал:

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

Он эти значения не использует при выборе индексов

Теоретически вроде может

Цитата:

If you use Oracle 9i, and the parameter _OPTIM_PEEK_USER_BINDS is not explicitly set to FALSE, Oracle automatically determines the contents of the bind variables during the initial parse. Due to other performance problems, however, we currently recommend that you set the parameter to FALSE (Note 755342)

Ссылка

Удав написал(а):

Выигрыш для MKPF+MSEG процентов до 40 при реальном выполнении, за счет правильного подбора индексов и меньшего количества операций поиска по индексам.

Наверное это если построены гистограммы. Если не ошибаюсь, по умолчанию они отключены во всех таблицах

Правила форума

Чтение из таблицы с выбором по неключевому полю; INDEX ; %_HINTS

Кто сейчас на конференции