Всем доброго дня.

Возникла проблема с производительностью отчета (стандартный отчет книга покупо/продаж).
Оказалось, что "тормозит" такой запрос:

SELECT * INTO TABLE et_deftax_item FROM deftax_item
FOR ALL ENTRIES IN it_bkpf_dti
WHERE tbeln = it_bkpf_dti-belnr AND
tgjah = it_bkpf_dti-gjahr

В таблице deftax_item около 60 000 записей. Во внутренней таблице it_bkpf_dti около 900 записей.
Время выполнения - секунд 20.

Трассировка показывает, что индекс 005 не отрабатывает.

Если же я указываю индекс явно в запросе, то он выполняется меньше секунды:

SELECT * INTO TABLE et_deftax_item FROM deftax_item
FOR ALL ENTRIES IN it_bkpf_dti
WHERE tbeln = it_bkpf_dti-belnr AND
tgjah = it_bkpf_dti-gjahr
%_HINTS ORACLE 'INDEX("DEFTAX_ITEM" "DEFTAX_ITEM~005")' ."


По какой причине индекс 005 автоматически не срабатывает?

Добрый день, а наверно EXPLAIN запроса уже смотрели? Может он даст понять почему оптимизатор запросов начал обходится без индекса ?
DBACOCKPIT > производительность > session monitor > найти свою сессию, пока она крутит запрос > нажать explain. Цифры - шаги запроса, начинаются с 1, 2, 3...
Там есть кнопка "объяснить с советом" - скормите туда свой хинт и сравните стоимости запроса с хинтом и без. И там же есть трассировка оптимизатора, возможно в ней будет объяснение, почему оптимизатор так решил делать.
А еще может тупо статистика долго не пересобиралась, от этого крыша и поехала у него.

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

В дополнение, статистику пересобирал, индексы удалял и восстанавливал. Не помогает.
Причем, четыре первых индекса исправно работают.

EXPLAIN:

SQL Statement

SELECT
/*+
INDEX(DEFTAX_ITEM DEFTAX_ITEM~005)
*/
*
FROM
"DEFTAX_ITEM"
WHERE
"MANDT"=:A0 AND "TBELN"=:A1 AND "TGJAH"=:A2 OR "MANDT"=:A3 AND "TBELN"=:A4 AND "TGJAH"=:A5 OR
"MANDT"=:A6 AND "TBELN"=:A7 AND "TGJAH"=:A8 OR "MANDT"=:A9 AND "TBELN"=:A10 AND "TGJAH"=:A11 OR
"MANDT"=:A12 AND "TBELN"=:A13 AND "TGJAH"=:A14


Execution Plan

SELECT STATEMENT ( Estimated Costs = 249 , Estimated #Rows = 64.480 )

1 TABLE ACCESS FULL DEFTAX_ITEM
( Estim. Costs = 249 , Estim. #Rows = 64.480 )
Estim. CPU-Costs = 99.150.249 Estim. IO-Costs = 242
Filter Predicates


А что нужно ввести в хинт, чтобы сравнить?

Ну в окошко объяснить с советом вводите свой хинт (без /*+ ... */) и он должен explain показать уже с учетом хинта, а сравнивать Estimated Costs ? Не работает так ?

Какой хинт сюда нужно подать для сравнения я не понял, но когда в АБАПЕ использую тотже запрос с хинтом, то результат такой:

С хинтом.
SELECT
/*+
INDEX("DEFTAX_ITEM" "DEFTAX_ITEM~005")
*/
*
FROM
"DEFTAX_ITEM"
WHERE
"MANDT"=:A0 AND "TBELN"=:A1 AND "TGJAH"=:A2 OR "MANDT"=:A3 AND "TBELN"=:A4 AND "TGJAH"=:A5 OR
"MANDT"=:A6 AND "TBELN"=:A7 AND "TGJAH"=:A8 OR "MANDT"=:A9 AND "TBELN"=:A10 AND "TGJAH"=:A11 OR
"MANDT"=:A12 AND "TBELN"=:A13 AND "TGJAH"=:A14

Execution Plan

SELECT STATEMENT ( Estimated Costs = 2.139 , Estimated #Rows = 64.480 )

3 INLIST ITERATOR

2 TABLE ACCESS BY INDEX ROWID DEFTAX_ITEM
( Estim. Costs = 2.139 , Estim. #Rows = 64.480 )
Estim. CPU-Costs = 103.944.628 Estim. IO-Costs = 2.132

1 INDEX RANGE SCAN DEFTAX_ITEM~005
( Estim. Costs = 321 , Estim. #Rows = 64.480 )
Search Columns: 3
Estim. CPU-Costs = 17.400.329 Estim. IO-Costs = 320
Access Predicates


Вопрос тот же, почему автоматически не срабатывает индекс?

Distinct этого индекса = 1 (по крайней мере у меня)
Column Name #Distinct
MANDT 1
TBELN 1
TGJAH 1

Поэтому oracle считает - дешевле не использовать индекс.
Что можно предпринять:
Попробовать снизить "Clustering factor" - CLSTFCT
exec DBMS_STATS.SET_INDEX_STATS('"SAPSR3"','"DEFTAX_ITEM~005"', CLSTFCT => 50, NO_INVALIDATE=>FALSE);
Но это временное решение, т.к. первый же сбор статистики вернет все на свои места.
Если это решение сработало, то можно попробовать реорганизовать таблицу с упорядочиванием данных относительно этого индекса.
Но тут надо все хорошо проверить, т.к. могут поплыть другие запросы.

to Oleg_ST.
Можно по подробнее, где я могу увидеть Distinct этого индекса?
И где нужно выполнить строку exec DBMS_STATS.SET_INDEX_STATS('"SAPSR3"','"DEFTAX_ITEM~005"', CLSTFCT => 50, NO_INVALIDATE=>FALSE);
Это обычный АБАП?

где я могу увидеть Distinct этого индекса?
db02 -> Diagnostics -> EXPLAN всавить запрос и слева вверху нажать "Explan"

где нужно выполнить строку
Это sql запрос, под пользователем ora<SID> набрать
sqlplus / as sysdba
далее эту строку

По этому поводу есть хорошая нота 832343 FAQ: Clustering factor