Выполнение запроса занимает очень много времени
У меня есть запрос, который занимает часы, а иногда он даже не выполняется. Запрос выглядит следующим образом:
SELECT id, trim(regexp_substr(str, '[^,]+', 1, LEVEL)) str
FROM (SELECT id , MULTILIST01 as str from PAGE_TWO where MULTILIST01 is not null )
WHERE trim(regexp_substr(str, '[^,]+', 1, LEVEL)) is not null
CONNECT BY instr(str, ',', 1, LEVEL -1) > 0;
Результирующий набор запроса
SELECT id , MULTILIST01 as str from PAGE_TWO where MULTILIST01 is not null
это как следует:
ID MULTILIST01
295285 ,3434925,3434442,3436781,
212117 ,3434925,3434442,3436781,
212120 ,3434925,3434442,3436781,
6031650 ,3436781,
.
.
.
Во внешнем запросе я пытаюсь сделать каждое значение через запятую уникальным. Когда я выполняю внешний запрос, это занимает несколько часов. Я пытался оптимизировать его, но это было бесполезно.
Любая идея, как я могу оптимизировать это.
Информация о версии Oracle
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production
Объяснить информацию таблицы
Plan hash value: 4097679000
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1429 | 11432 | 840 (2)| 00:00:01 |
|* 1 | FILTER | | | | | |
|* 2 | CONNECT BY WITHOUT FILTERING| | | | | |
|* 3 | TABLE ACCESS FULL | PAGE_TWO | 1429 | 11432 | 840 (2)| 00:00:01 |
------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$F5BB74E1
3 - SEL$F5BB74E1 / PAGE_TWO@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TRIM( REGEXP_SUBSTR ("MULTILIST01",'[^,]+',1,LEVEL)) IS NOT NULL)
2 - filter(INSTR("MULTILIST01",',',1,LEVEL-1)>0)
3 - filter("MULTILIST01" IS NOT NULL)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "ID"[NUMBER,22], "MULTILIST01"[VARCHAR2,1020], LEVEL[4]
2 - "ID"[NUMBER,22], "MULTILIST01"[VARCHAR2,1020], LEVEL[4]
3 - "ID"[NUMBER,22], "MULTILIST01"[VARCHAR2,1020]
Таблица содержит 225 столбцов, где индекс только по столбцам первичного ключа (ID, CLASS).
Этот стол из Agile PLM.
1 ответ
Ваш подход работает только для таблицы с одной строкой.
SELECT id, trim(regexp_substr(str, '[^,]+', 1, LEVEL)) str
FROM (SELECT id , MULTILIST01 as str from PAGE_TWO where MULTILIST01 is not null and rownum <= 1 )
WHERE trim(regexp_substr(str, '[^,]+', 1, LEVEL)) is not null
CONNECT BY instr(str, ',', 1, LEVEL -1) > 0
order by 1,2;
ID STR
---------- -------------------------
295285 3434442
295285 3434925
295285 3436781
Начиная с таблицы из двух строк, вы получите (вероятно) гораздо больше ожидаемых результатов:
SELECT id, trim(regexp_substr(str, '[^,]+', 1, LEVEL)) str
FROM (SELECT id , MULTILIST01 as str from PAGE_TWO where MULTILIST01 is not null and rownum <= 2 )
WHERE trim(regexp_substr(str, '[^,]+', 1, LEVEL)) is not null
CONNECT BY instr(str, ',', 1, LEVEL -1) > 0
order by 1,2;
ID STR
---------- -------------------------
212117 3434442
212117 3434442
212117 3434925
212117 3436781
212117 3436781
212117 3436781
212117 3436781
295285 3434442
295285 3434442
295285 3434925
295285 3436781
295285 3436781
295285 3436781
295285 3436781
;
Эта переформулировка запроса даст вам (очевидно), что вы хотите. Используйте подзапрос, который доставляет индекс подстроки (1 ..N). Вы должны определить максимальное количество подстрок, которые будут разбиты. Соедините эту таблицу со своей таблицей, чтобы эффективно умножить число строк на N.
with substr_idx as (
select rownum colnum from dual connect by level <= 3 /* max number of substrings */)
SELECT id, trim(regexp_substr(str, '[^,]+', 1, colnum)) str
FROM (SELECT id , MULTILIST01 as str from PAGE_TWO where MULTILIST01 is not null), substr_idx
WHERE trim(regexp_substr(str, '[^,]+', 1, colnum)) is not null
order by 1,2;
ID STR
---------- -------------------------
212117 3434442
212117 3434925
212117 3436781
212120 3434442
212120 3434925
212120 3436781
295285 3434442
295285 3434925
295285 3436781
6031650 3436781
Дальнейшее (незначительное) улучшение производительности ожидается, если вы замените регулярное выражение извлечением substr / instr. Смотрите, например, здесь
Мораль этой истории: если вы не получаете результатов с большими данными, попробуйте с небольшими данными (проверьте rownum <= 2 ограничение выше) и посмотреть, если результаты соответствуют ожидаемым.