Похожая ситуация.

Несложный запрос.

select pa11.Plan_ID Plan_ID,
RTRIM(a16.Plan_DESC) Plan_DESC,
pa11.Object_ID Object_ID,
a17.Object_DESC Object_DESC,
pa11.Month_ID Month_ID,
a18.Month_name Month_name,
a18.Month_of_Year_ID||'/'||a18.Year_ID CustCol_13,
pa12.WJXBFS1 WJXBFS1,
ISNULL((pa12.WJXBFS2 / NULLIF(pa13.WJXBFS1, 0)), 0) WJXBFS2,
ISNULL((pa14.WJXBFS1 / NULLIF(pa15.WJXBFS1, 0)), 0) WJXBFS3,
ISNULL(((pa14.WJXBFS2 / NULLIF(pa15.WJXBFS2, 0)) * pa13.WJXBFS2), 0) WJXBFS4,
ISNULL(((ISNULL((pa14.WJXBFS3 / NULLIF(pa15.WJXBFS3, 0)), 0) - ISNULL((pa12.WJXBFS3 / NULLIF(pa13.WJXBFS3, 0)), 0)) * pa15.WJXBFS4), 0) WJXBFS5,
pa12.WJXBFS4 WJXBFS6,
pa14.WJXBFS4 WJXBFS7,
ISNULL((pa12.WJXBFS5 / NULLIF(pa13.WJXBFS4, 0)), 0) WJXBFS8,
ISNULL(((pa14.WJXBFS5 / NULLIF(pa15.WJXBFS5, 0)) * pa13.WJXBFS5), 0) WJXBFS9,
ISNULL(((ISNULL((pa14.WJXBFS6 / NULLIF(pa15.WJXBFS6, 0)), 0) - ISNULL((pa12.WJXBFS6 / NULLIF(pa13.WJXBFS6, 0)), 0)) * pa15.WJXBFS7), 0) WJXBFSa,
pa14.WJXBFS7 WJXBFSb,
pa15.WJXBFS8 WJXBFSc
from ZZTST020BNLOD005 pa11
left outer join ZZTST020BNLMD000 pa12
on (pa11.Month_ID = pa12.Month_ID and
pa11.Object_ID = pa12.Object_ID and
pa11.Plan_ID = pa12.Plan_ID)
left outer join ZZTST020BNLMD001 pa13
on (pa11.Month_ID = pa13.Month_ID)
left outer join ZZTST020BNLMD002 pa14
on (pa11.Month_ID = pa14.Month_ID and
pa11.Object_ID = pa14.Object_ID and
pa11.Plan_ID = pa14.Plan_ID)
cross join ZZTST020BNLMD003 pa15
join DBA.LU_Plan a16
on (pa11.Plan_ID = a16.Plan_ID)
join DBA.LU_SHOP a17
on (pa11.Object_ID = a17.Object_ID)
join DBA.LU_MONTH a18
on (pa11.Month_ID = a18.Month_ID)

Таблицы все небольшие, несколько десятков тысяч строк. В таблице ZZTST020BNLMD003 - одна строка. По всем столбцам, по которым идёт соединение, построены HG индексы. При этом смотрю план запроса, вижу
Est. temp space for this node (Mb) 7685.89145279
Act. temp space for this node (Mb) 0.12500000

При большем количестве строк в таблицах, запрос не выполняется по причине превышения Query_temp_space_limit. Можно ли это победить?

up,

спасибо за ответ. Как доберусь до заказчика, так и вышлю.

Я немного поэкспериментировал, убрал cross join, вычисления (isnull), проблема не исчезла. От джойна по нескольким колонкам избавиться не получится, скрипт генерится автоматом, а там настроек не так много. Уже то хорошо, что одноколоночные индексы построены.

Виктор Саковичup,

спасибо за ответ. Как доберусь до заказчика, так и вышлю.

Я немного поэкспериментировал, убрал cross join, вычисления (isnull), проблема не исчезла. От джойна по нескольким колонкам избавиться не получится, скрипт генерится автоматом, а там настроек не так много. Уже то хорошо, что одноколоночные индексы построены.

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

Я приложил файл с планом запроса проблемные ноды #18 и #19, был бы очень признателен за помощь.

upВиктор Сакович,

Виктор смотря на данный конкретный запрос можно Вам порекомендовать отключить вообще поддержку двунаправленных крсоров (Tetch Prior). Врядли это нужно в Вашем случае. Это снимет нагрузку на сервер в целом.
set option public.Force_No_Scroll_Cursors='ON';
Переконнектить сессии после этого нужно.

Если пройтись по плану вниз, то доходим до ноды 9 и на ней останавливаемся:
17:
Generated Result Rows 201
Estimated Result Rows 20726596
Join Result Constraint Many to Many (cross - но справа 1 запись)
Estimated Right Inputs 1
Estimated Left Inputs 20726596 (левая ветка)
15:
Generated Result Rows 201
Estimated Result Rows 20726596
Join Result Constraint Many to 1
Estimated Right Inputs 120
Estimated Left Inputs 20726596 (опять по левой)
13:
Generated Result Rows 201
Estimated Result Rows 20726596
Join Result Constraint Many to 1
Estimated Right Inputs 318
Estimated Left Inputs 20726596 (опять по левой)
11:
Generated Result Rows 201
Estimated Result Rows 20726596
Join Result Constraint Many to 1
Estimated Right Inputs 359
Estimated Left Inputs 20726596 (опять по левой)
9:
Generated Result Rows 201
Estimated Result Rows 20726596
Join Result Constraint Many to Many
Estimated Right Inputs 9473
Estimated Left Inputs 535210

Тут мы и получаем основной прирост при оценке оптимизатора из-за Many to Many.

Output Vector 20 entries (280 data bytes) - это и есть наши Гиги при умножении на кол-во записей по оценке орптимизатора.

Смотрим:

Condition 1 (pa11.Month_ID AS Month_ID = pa14.Month_ID)
Condition 2 (pa11.Object_ID AS Object_ID = pa14.Object_ID)
Condition 3 (pa11.Plan_ID AS Plan_ID = pa14.Plan_ID)

А разве нельзя в настройках сервера MSTR (я не помню - давно это было) для временных таблиц сказать типа такого?

create table ZZTST010L62MD002 (Month_ID smallint,Object_ID unsigned int,Plan_ID unsigned int,
WJXBFS1 int,WJXBFS2 int,
UNIQUE(Month_ID,Object_ID,Plan_ID) )

Или попробуйте такой запрос в режиме Derived Tables (только в базовых таблицах, если есть джойны по нескольким колонкам создайте композитный HG индекс).

Надеюсь не ощибся с разбором.

Спасибо. Думаю, что я просто буду отключать query_temp_space_limit на последнем проходе, когда объединяются несколько коротких таблиц для формирования выходной формы. Остальные варианты как-то не проходят.

up,

При включении Force_No_Scroll_Cursors=ON слетело несколько отчётов. Пришлось вернуть опцию обратно.