Oracle: предикаты доступа не использовались до агрегирования
Я борюсь с ООП Oracle (12.1.0.2.0), чтобы сделать мой запрос более эффективным.
У меня есть небольшой каскад вида, как это:
CREATE VIEW ZISUCDEVABRREL AS SELECT
ZW.MANDT AS MANDT,
ES.ANLAGE,
ZW.EQUNR,
MIN( CASE WHEN ZW.AB > ES.AB THEN ZW.AB ELSE ES.AB END ) AS AB,
MAX( CASE WHEN ZW.BIS < ES.BIS THEN ZW.BIS ELSE ES.BIS END ) AS BIS
FROM ZISUCETDZDEVREGA ZW INNER JOIN ZISUCEASTS ES ON (
ZW.MANDT = ES.MANDT AND ZW.LOGIKZW = ES.LOGIKZW
)
GROUP BY ZW.MANDT, ES.ANLAGE, ZW.EQUNR
Который использует
CREATE VIEW ZISUCETDZDEVREGA AS SELECT
ETDZ.MANDT AS MANDT,
ETDZ.LOGIKZW,
ETDZ.EQUNR,
MIN( ETDZ.AB ) AS AB,
MAX( ETDZ.BIS ) AS BIS
FROM ETDZ ETDZ
GROUP BY ETDZ.MANDT, ETDZ.LOGIKZW, ETDZ.EQUNR
А ТАКЖЕ
CREATE VIEW ZISUCEASTS AS SELECT
EASTS.MANDT AS MANDT,
EASTS.ANLAGE,
EASTS.LOGIKZW,
MIN( EASTS.AB ) AS AB,
MAX( EASTS.BIS ) AS BIS
FROM EASTS EASTS
WHERE EASTS.ZWNABR = ' '
GROUP BY EASTS.MANDT, EASTS.ANLAGE, EASTS.LOGIKZW
Примечание. Я работаю в SAP (использую представления CDS) и приложил все усилия, чтобы "перевести" все на простой SQL. Ограничение в 4 строки (как показано в запросах ниже) вводится некой SQL-консолью, которую я использую для отладки, и, к сожалению, я не могу избавиться от нее так просто. Кажется, нет никакой разницы в планах выполнения в пределах допустимого диапазона 1-5000 строк.
ETDZ имеет уникальный вторичный индекс ETDZ~ZZW on ( MANDT, LOGIKZW, ab, bis, EQUNR), поэтому все поля, выбранные из ETDZ, содержатся. Однако MANDT очень недискриминационный и используется только потому, что SAP автоматически включает его почти во все сгенерированные критерии / условия запроса.
Запрос представления
SELECT * FROM ZISUCDEVABRREL ab WHERE anlage = '4002833772' order by ab
производит следующий план:
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 428 | | 98946 (1)| 00:00:04 |
| 1 | SORT ORDER BY | | 4 | 428 | | 98945 (1)| 00:00:04 |
|* 2 | VIEW | | 4 | 428 | | 98944 (1)| 00:00:04 |
|* 3 | WINDOW SORT PUSHED RANK | | 3 | 396 | | 98943 (1)| 00:00:04 |
| 4 | HASH GROUP BY | | 3 | 396 | | 98943 (1)| 00:00:04 |
| 5 | MERGE JOIN | | 3 | 396 | | 98941 (1)| 00:00:04 |
| 6 | SORT JOIN | | 6513K| 434M| | 98938 (1)| 00:00:04 |
| 7 | VIEW | ZISUCETDZDEVREGA | 6513K| 434M| | 98938 (1)| 00:00:04 |
| 8 | HASH GROUP BY | | 6513K| 372M| 475M| 98938 (1)| 00:00:04 |
|* 9 | INDEX FAST FULL SCAN | ETDZ~ZZW | 6513K| 372M| | 3704 (1)| 00:00:01 |
|* 10 | SORT JOIN | | 2 | 124 | | 3 (67)| 00:00:01 |
| 11 | VIEW | ZISUCEASTS | 2 | 124 | | 2 (50)| 00:00:01 |
| 12 | HASH GROUP BY | | 2 | 108 | | 1 (0)| 00:00:01 |
|* 13 | TABLE ACCESS BY INDEX ROWID| EASTS | 2 | 108 | | 1 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | EASTS~0 | 4 | | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$6
2 - SEL$43057250 / from$_subquery$_002@SEL$6
3 - SEL$43057250
7 - SEL$3 / ZW@SEL$2
8 - SEL$3
9 - SEL$3 / ETDZ@SEL$3
11 - SEL$4 / ES@SEL$2
12 - SEL$4
13 - SEL$4 / EASTS@SEL$4
14 - SEL$4 / EASTS@SEL$4
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(from$_subquery$_002.rowlimit_$$_rownumber<=4)
3 - filter(ROW_NUMBER() OVER ( ORDER BY MIN(CASE WHEN ZW.AB>ES.AB THEN ZW.AB ELSE
ES.AB END ))<=4)
9 - filter(ETDZ.MANDT=:A0)
10 - access(ZW.MANDT=ES.MANDT AND ZW.LOGIKZW=ES.LOGIKZW)
filter(ZW.LOGIKZW=ES.LOGIKZW AND ZW.MANDT=ES.MANDT)
13 - filter(EASTS.ZWNABR=' ')
14 - access(EASTS.MANDT=:A0 AND EASTS.ANLAGE='4002833772')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=1) from$_subquery$_002.AB[VARCHAR2,24], from$_subquery$_002.MANDT[VARCHAR2,9],
from$_subquery$_002.ANLAGE[VARCHAR2,30], from$_subquery$_002.EQUNR[VARCHAR2,54],
from$_subquery$_002.BIS[VARCHAR2,24]
2 - from$_subquery$_002.MANDT[VARCHAR2,9], from$_subquery$_002.ANLAGE[VARCHAR2,30],
from$_subquery$_002.EQUNR[VARCHAR2,54], from$_subquery$_002.AB[VARCHAR2,24],
from$_subquery$_002.BIS[VARCHAR2,24], from$_subquery$_002.rowlimit_$$_rownumber[NUMBER,22]
3 - (#keys=1) MIN(CASE WHEN ZW.AB>ES.AB THEN ZW.AB ELSE ES.AB END )[24],
ES.MANDT[VARCHAR2,9], ES.ANLAGE[VARCHAR2,30], ZW.EQUNR[VARCHAR2,54], MAX(CASE WHEN
ZW.BIS<ES.BIS THEN ZW.BIS ELSE ES.BIS END )[24], ROW_NUMBER() OVER ( ORDER BY MIN(CASE
WHEN ZW.AB>ES.AB THEN ZW.AB ELSE ES.AB END ))[22]
4 - (#keys=3) ES.MANDT[VARCHAR2,9], ES.ANLAGE[VARCHAR2,30], ZW.EQUNR[VARCHAR2,54],
MIN(CASE WHEN ZW.AB>ES.AB THEN ZW.AB ELSE ES.AB END )[24], MAX(CASE WHEN
ZW.BIS<ES.BIS THEN ZW.BIS ELSE ES.BIS END )[24]
5 - (#keys=0) ES.MANDT[VARCHAR2,9], ZW.BIS[VARCHAR2,24], ZW.AB[VARCHAR2,24],
ZW.EQUNR[VARCHAR2,54], ES.BIS[VARCHAR2,24], ES.ANLAGE[VARCHAR2,30], ES.AB[VARCHAR2,24]
6 - (#keys=2) ZW.MANDT[VARCHAR2,9], ZW.LOGIKZW[VARCHAR2,54], ZW.BIS[VARCHAR2,24],
ZW.AB[VARCHAR2,24], ZW.EQUNR[VARCHAR2,54]
7 - ZW.MANDT[VARCHAR2,9], ZW.LOGIKZW[VARCHAR2,54], ZW.EQUNR[VARCHAR2,54],
ZW.AB[VARCHAR2,24], ZW.BIS[VARCHAR2,24]
8 - (#keys=3) ETDZ.MANDT[VARCHAR2,9], ETDZ.LOGIKZW[VARCHAR2,54],
ETDZ.EQUNR[VARCHAR2,54], MAX(ETDZ.BIS)[24], MIN(ETDZ.AB)[24]
9 - ETDZ.MANDT[VARCHAR2,9], ETDZ.EQUNR[VARCHAR2,54], ETDZ.BIS[VARCHAR2,24],
ETDZ.AB[VARCHAR2,24], ETDZ.LOGIKZW[VARCHAR2,54]
10 - (#keys=2) ES.MANDT[VARCHAR2,9], ES.LOGIKZW[VARCHAR2,54], ES.BIS[VARCHAR2,24],
ES.ANLAGE[VARCHAR2,30], ES.AB[VARCHAR2,24]
11 - ES.MANDT[VARCHAR2,9], ES.ANLAGE[VARCHAR2,30], ES.LOGIKZW[VARCHAR2,54],
ES.AB[VARCHAR2,24], ES.BIS[VARCHAR2,24]
12 - (#keys=3) EASTS.MANDT[VARCHAR2,9], EASTS.ANLAGE[VARCHAR2,30],
EASTS.LOGIKZW[VARCHAR2,54], MAX(EASTS.BIS)[24], MIN(EASTS.AB)[24]
13 - EASTS.MANDT[VARCHAR2,9], EASTS.ANLAGE[VARCHAR2,30], EASTS.LOGIKZW[VARCHAR2,54],
EASTS.BIS[VARCHAR2,24], EASTS.AB[VARCHAR2,24]
14 - EASTS.ROWID[ROWID,10], EASTS.MANDT[VARCHAR2,9], EASTS.ANLAGE[VARCHAR2,30],
EASTS.LOGIKZW[VARCHAR2,54], EASTS.BIS[VARCHAR2,24]
Таким образом, он не уничтожает строки перед агрегированием таблицы ETDZ. Я ожидал, что он будет использовать LOGIKZW (переданный из оценки ZISUCEASTS), чтобы избавиться от>99,999% строк. Я могу дать подсказку использовать индекс, что приведет к сканированию INDEX RANGE в ETDZ ~ ZZW вместо INDEX FAST FULL SCAN в ETDZ~ZZW (строка 9 в плане запроса). Предикат меняется с FILTER на ACCESS, но он все еще использует только самую первую часть MANDT (очень неразборчиво) из ETDZ ~ ZZW и все еще собирает 6 миллионов строк перед фильтрацией в LOGIKZW.
Почему он делает это и что я могу сделать, чтобы изменить свое мнение? Я попробовал некоторые другие подсказки (в дополнение к указателю), но большинство не оказало никакого влияния. Какие должны работать (учитывая, я получаю правильный синтаксис)? Спасибо!