База данных Oracle медленно выполняет оператор с несколькими рекурсивными общими табличными выражениями
У меня есть таблица базы данных Oracle 19c с «ресурсами», которые организованы иерархически, как дерево вложенных папок. Таблица содержит около 2,5 миллионов строк, а дерево имеет глубину до 10 уровней.
create table RESOURCES (
ID_ NUMBER(10) not null constraint PK_RESOURCES primary key,
FOLDERID_ NUMBER(10) constraint FK_PARENTFOLDER references RESOURCES
);
create index FOLDERIDINDEX on RESOURCES (FOLDERID_);
Я использую рекурсивные общие табличные выражения SQL (также известные как факторинг рекурсивных подзапросов), чтобы найти всех потомков некоторых заданных ресурсов.
В целом, это работает довольно хорошо, но если я попытаюсь получить потомков нескольких папок одним запросом, некоторые запросы вообще не будут выполняться с использованием Oracle. Я хотел бы понять, почему это так, и есть ли какой-нибудь простой способ ускорить процесс (подсказка к запросу?, исправление?, ...?)
Например, этот оператор не возвращается в течение 60 минут (!):
WITH cte1 (id_) AS (SELECT id_ FROM Resources where id_ = 11
UNION ALL
SELECT r.id_ FROM Resources r, cte1 c WHERE r.folderId_ = c.id_),
cte2 (id_) AS (SELECT id_ FROM Resources where id_ = 808965
UNION ALL
SELECT r.id_ FROM Resources r, cte2 c WHERE r.folderId_ = c.id_)
SELECT count(*)
FROM Resources r
WHERE (r.folderId_ IN (SELECT * FROM cte1) OR r.folderId_ IN (SELECT * FROM cte2));
Если я заменю два дополнительных элемента в последней строке на UNION, это займет всего несколько секунд:
WITH cte1 (id_) AS (SELECT id_ FROM Resources where id_ = 11
UNION ALL
SELECT r.id_ FROM Resources r, cte1 c WHERE r.folderId_ = c.id_),
cte2 (id_) AS (SELECT id_ FROM Resources where id_ = 808965
UNION ALL
SELECT r.id_ FROM Resources r, cte2 c WHERE r.folderId_ = c.id_)
SELECT count(*)
FROM Resources r
WHERE (r.folderId_ IN (SELECT * FROM cte1 UNION SELECT * FROM cte2));
Хотя это уже может быть решением, в моем проекте это немного сложно изменить, потому что SQL автоматически генерируется кодом из запросов приложения, и в этот момент его не так просто изменить. Кроме того, запросы приложений могут быть гораздо более сложными, и такая замена может быть даже невозможна. Это просто упрощенные примеры. Может быть, есть какой-то другой способ ускорить процесс?
Интересно, что медленный запрос работает без проблем с производительностью на других базах данных, таких как MySQL 8, PostgreSQL 13, SQL Server 2016 (с небольшими изменениями синтаксиса для баз данных). Это просто Oracle, у которого, похоже, есть проблема.
Это план запроса для первого запроса, то есть медленного:
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 54G (1)|591:38:12 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | FILTER | | | | | |
| 3 | TABLE ACCESS FULL | RESOURCES | 2410K| 11M| 9389 (1)| 00:00:01 |
|* 4 | VIEW | | 239K| 3046K| 23128 (1)| 00:00:01 |
| 5 | UNION ALL (RECURSIVE WITH) BREADTH FIRST| | | | | |
|* 6 | INDEX UNIQUE SCAN | PK_RESOURCES | 1 | 6 | 2 (0)| 00:00:01 |
|* 7 | HASH JOIN | | 239K| 5623K| 23126 (1)| 00:00:01 |
| 8 | RECURSIVE WITH PUMP | | | | | |
| 9 | BUFFER SORT (REUSE) | | | | | |
| 10 | TABLE ACCESS FULL | RESOURCES | 2410K| 25M| 9386 (1)| 00:00:01 |
|* 11 | VIEW | | 239K| 3046K| 23128 (1)| 00:00:01 |
| 12 | UNION ALL (RECURSIVE WITH) BREADTH FIRST| | | | | |
|* 13 | INDEX UNIQUE SCAN | PK_RESOURCES | 1 | 6 | 2 (0)| 00:00:01 |
|* 14 | HASH JOIN | | 239K| 5623K| 23126 (1)| 00:00:01 |
| 15 | RECURSIVE WITH PUMP | | | | | |
| 16 | BUFFER SORT (REUSE) | | | | | |
| 17 | TABLE ACCESS FULL | RESOURCES | 2410K| 25M| 9386 (1)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
" 2 - filter( EXISTS (SELECT 0 FROM ""CTE1"" ""CTE1"" WHERE ""CTE1"".""ID_""=:B1) OR EXISTS (SELECT 0 "
" FROM ""CTE2"" ""CTE2"" WHERE ""CTE2"".""ID_""=:B2))"
" 4 - filter(""CTE1"".""ID_""=:B1)"
" 6 - access(""ID_""=11)"
" 7 - access(""R"".""FOLDERID_""=""C"".""ID_"")"
" 11 - filter(""CTE2"".""ID_""=:B1)"
" 13 - access(""ID_""=808965)"
" 14 - access(""R"".""FOLDERID_""=""C"".""ID_"")"
Для сравнения, более быстрый запрос с использованием UNION, кажется, использует лучший план:
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18 | | 55733 (1)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | 18 | | | |
|* 2 | HASH JOIN | | 2806K| 48M| 11M| 55733 (1)| 00:00:03 |
| 3 | VIEW | VW_NSO_1 | 479K| 6092K| | 50820 (1)| 00:00:02 |
| 4 | SORT UNIQUE | | 479K| 6092K| 9424K| 50820 (1)| 00:00:02 |
| 5 | UNION-ALL | | | | | | |
| 6 | VIEW | | 239K| 3046K| | 23128 (1)| 00:00:01 |
| 7 | UNION ALL (RECURSIVE WITH) BREADTH FIRST| | | | | | |
|* 8 | INDEX UNIQUE SCAN | PK_RESOURCES | 1 | 6 | | 2 (0)| 00:00:01 |
|* 9 | HASH JOIN | | 239K| 5623K| | 23126 (1)| 00:00:01 |
| 10 | RECURSIVE WITH PUMP | | | | | | |
| 11 | BUFFER SORT (REUSE) | | | | | | |
| 12 | TABLE ACCESS FULL | RESOURCES | 2410K| 25M| | 9386 (1)| 00:00:01 |
| 13 | VIEW | | 239K| 3046K| | 23128 (1)| 00:00:01 |
| 14 | UNION ALL (RECURSIVE WITH) BREADTH FIRST| | | | | | |
|* 15 | INDEX UNIQUE SCAN | PK_RESOURCES | 1 | 6 | | 2 (0)| 00:00:01 |
|* 16 | HASH JOIN | | 239K| 5623K| | 23126 (1)| 00:00:01 |
| 17 | RECURSIVE WITH PUMP | | | | | | |
| 18 | BUFFER SORT (REUSE) | | | | | | |
| 19 | TABLE ACCESS FULL | RESOURCES | 2410K| 25M| | 9386 (1)| 00:00:01 |
| 20 | INDEX FAST FULL SCAN | FOLDERIDINDEX | 2410K| 11M| | 2392 (1)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
" 2 - access(""R"".""FOLDERID_""=""ID_"")"
" 8 - access(""ID_""=11)"
" 9 - access(""R"".""FOLDERID_""=""C"".""ID_"")"
" 15 - access(""ID_""=808965)"
" 16 - access(""R"".""FOLDERID_""=""C"".""ID_"")"