Рекурсивные запросы использовать приходится редко, поэтому многие нюансы я забываю или упускаю из вида.
Помогите найти, что именно сейчас я сделал не так?
Задача простая — разделить строку по разделителю (пробелу).
В простейшем варианте такой пример дает вроде бы правильные результаты:
1.
2.
3.
4.
5.
6.
7.
with tmp as (select '1 2 3' str from dual union all select '4' from dual union all select '5 6' from dual)
select distinct str
, regexp_substr(str, '\S+', 1, level) re
, level
from tmp
connect by instr(str, ' ', 1, level - 1) > 0
order by 1, 2
Рабочий пример несколько более сложный:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
with NL as
(
select 0 as Y, '' as IP from DUAL
union all select 2020, '10.1.14.69' from dual
union all select 2020, '10.1.12.102 10.1.12.146 10.1.12.195' from dual
...
union all select 2021, '10.1.11.158 10.1.10.54 10.1.10.94 10.1.11.179 10.1.12.104 10.1.10.59 10.1.10.15 10.1.11.115 10.1.13.95 10.1.11.149 10.1.12.113 10.1.13.172 10.1.14.173' from dual
union all select 2021, '10.1.14.199' from dual
)
, LST as (select Y, row_number() over(partition by Y order by rownum) as RN, IP from NL where Y > 0)
select distinct LST.Y, LST.RN
, regexp_substr(LST.IP, '\S+', 1, level) as IP
, LST.IP as STR
from LST
connect by instr(LST.IP, ' ', 1, level - 1) > 0
order by 1, 2, 3, 4
В принципе этот запрос так же отрабатывает корректно.
Но если попробовать применить его к рабочему набору данных (около сотни строк, в которых может быть до пары десятков подстрок), как запрос "зависает" (за несколько минут не завершается).
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
with NL as
(
select 0 as Y, '' as IP from DUAL
union all select 2020, '10.1.14.69' from dual
union all select 2020, '10.1.12.102' from dual
union all select 2020, '10.1.12.102 10.1.12.146 10.1.12.195' from dual
union all select 2020, '10.1.11.155 10.1.11.124 10.1.11.134 10.1.11.135 10.1.12.161 10.1.14.100 10.1.11.177 10.1.12.112 10.1.10.78 10.1.10.9 10.1.12.186 10.1.13.103 10.1.14.193 10.1.13.8 10.1.10.104' from dual
union all select 2020, '10.1.10.66 10.1.10.96 10.1.12.107 10.1.13.47 10.1.14.31 10.1.10.64 10.1.13.97 10.1.10.106 10.1.11.128 10.1.10.97 10.1.12.183 10.1.10.63 10.1.12.180 10.1.12.150 10.1.11.183 10.1.13.132 10.1.14.151 10.1.14.153 10.1.14.80 10.1.14.112 10.1.14.158 10.1.10.74' from dual
union all select 2020, '10.1.10.44' from dual
union all select 2020, '10.1.13.48 10.1.14.41 10.1.13.169 10.1.14.95 10.1.12.55 10.1.12.147 10.1.14.77 10.1.13.171' from dual
union all select 2020, '10.1.11.192' from dual
union all select 2020, '10.1.12.79' from dual
union all select 2020, '10.1.14.60 10.1.13.102 10.1.14.56' from dual
union all select 2020, '10.1.10.125 10.1.14.65 10.1.14.92 10.1.14.88 10.1.14.13 10.1.13.151 10.1.14.109 10.1.13.152' from dual
union all select 2020, '10.1.10.74' from dual
union all select 2020, '10.1.10.20 10.1.14.181' from dual
union all select 2020, '10.1.10.80' from dual
union all select 2020, '10.1.12.86' from dual
union all select 2020, '10.1.14.39' from dual
union all select 2020, '10.1.10.6 10.1.11.155 10.1.11.124 10.1.13.159 10.1.14.166 10.1.13.142 10.1.12.152 10.1.11.135 10.1.12.193 10.1.11.191 10.1.12.192 10.1.13.181 10.1.14.45 10.1.11.116' from dual
union all select 2020, '10.1.10.74 10.1.10.66 10.1.13.47 10.1.14.31 10.1.10.64 10.1.13.97 10.1.11.128 10.1.10.97 10.1.12.183 10.1.11.183' from dual
union all select 2020, '10.1.10.106 10.1.12.180 10.1.12.150 10.1.12.107 10.1.14.80' from dual
union all select 2020, '10.1.10.96 10.1.14.151 10.1.14.153 10.1.14.112 10.1.14.158 10.1.10.63 10.1.13.132' from dual
union all select 2020, '10.1.14.101' from dual
union all select 2020, '10.1.11.168 10.1.12.71' from dual
union all select 2020, '10.1.12.79 10.1.14.60 10.1.13.102' from dual
union all select 2020, '10.1.14.145' from dual
union all select 2020, '10.1.13.35' from dual
union all select 2020, '10.1.14.97' from dual
union all select 2020, '10.1.10.17 10.1.10.98 10.1.13.177 10.1.13.105 10.1.14.11 10.1.11.163 10.1.13.187' from dual
union all select 2020, '10.1.10.17 10.1.10.98 10.1.13.177 10.1.13.105 10.1.14.11 10.1.11.163 10.1.13.187' from dual
union all select 2020, '10.1.11.169' from dual
union all select 2020, '10.1.11.138' from dual
union all select 2020, '10.1.14.172' from dual
union all select 2020, '10.1.10.93 10.1.12.118 10.1.11.117 10.1.14.25 10.1.10.88' from dual
union all select 2020, '10.1.14.99' from dual
union all select 2020, '10.1.13.9 10.1.13.170 10.1.14.182 10.1.13.18 10.1.14.117 10.1.13.88 10.1.14.127' from dual
union all select 2020, '10.1.14.60 10.1.13.102 10.1.14.56 10.1.12.79' from dual
union all select 2020, '10.1.14.97' from dual
union all select 2020, '10.1.14.97' from dual
union all select 2020, '10.1.14.97' from dual
union all select 2020, '10.1.14.97' from dual
union all select 2020, '10.1.12.124 10.1.14.51' from dual
union all select 2020, '10.1.14.60 10.1.13.102 10.1.14.56' from dual
union all select 2020, '10.1.12.79' from dual
union all select 2020, '10.1.14.97' from dual
union all select 2020, '10.1.11.148' from dual
union all select 2020, '10.1.14.67' from dual
union all select 2021, '10.1.10.65 10.1.13.93 10.1.13.81 10.1.10.192 10.1.11.171' from dual
union all select 2021, '10.1.11.158 10.1.10.54 10.1.10.94 10.1.11.179 10.1.12.104 10.1.10.59 10.1.10.15 10.1.11.115 10.1.13.95 10.1.11.149 10.1.12.113 10.1.13.172 10.1.14.173' from dual
union all select 2021, '10.1.14.199' from dual
union all select 2021, '10.1.14.82' from dual
union all select 2021, '10.1.11.198' from dual
union all select 2021, '10.1.14.112 10.1.12.183' from dual
union all select 2021, '10.1.10.34 10.1.12.82 10.1.11.127 10.1.13.185 10.1.11.181 10.1.14.40 10.1.14.174 10.1.12.134 10.1.14.169 10.1.14.159 10.1.14.78' from dual
union all select 2021, '10.1.14.30 10.1.13.119 10.1.11.68 10.1.11.145 10.1.10.70 10.1.12.139' from dual
union all select 2021, '10.1.10.60' from dual
union all select 2021, '10.1.10.34 10.1.11.68 10.1.12.82 10.1.11.127 10.1.13.185 10.1.14.30 10.1.10.9 10.1.10.64' from dual
union all select 2021, '10.1.11.153 10.1.13.98 10.1.14.62' from dual
union all select 2021, '10.1.14.45 10.1.13.142 10.1.14.100 10.1.13.143 10.1.12.193 10.1.11.191 10.1.13.6 10.1.12.192 10.1.14.71 10.1.11.199 10.1.13.45 10.1.13.103 10.1.11.116 10.1.13.5 10.1.13.159 10.1.11.177' from dual
union all select 2021, '10.1.14.92 10.1.14.88 10.1.13.151 10.1.13.152' from dual
union all select 2021, '10.1.13.38' from dual
union all select 2021, '10.1.10.74 10.1.13.47 10.1.10.97 10.1.12.183' from dual
union all select 2021, '10.1.12.71' from dual
union all select 2021, '10.1.14.83 10.1.14.70 10.1.13.77 10.1.14.6 10.1.11.168 10.1.14.15 10.1.14.18 10.1.14.178 10.1.13.188 10.1.13.28 10.1.13.35 10.1.10.21 10.1.14.86 10.1.12.71 10.1.14.20 10.1.11.73 10.1.12.136 10.1.14.19 10.1.14.111 10.1.14.152 10.1.12.110 10.1.10.43' from dual
union all select 2021, '10.1.10.74 10.1.12.150 10.1.13.47 10.1.10.97 10.1.12.183' from dual
union all select 2021, '10.1.13.26' from dual
union all select 2021, '10.1.14.83 10.1.14.70 10.1.13.77 10.1.14.6 10.1.11.168 10.1.14.15 10.1.14.18 10.1.14.178 10.1.13.188 10.1.13.28 10.1.13.35 10.1.10.21 10.1.14.86 10.1.12.71 10.1.14.20 10.1.11.73 10.1.12.136 10.1.14.19 10.1.14.111 10.1.14.152 10.1.12.110 10.1.10.43' from dual
union all select 2021, '10.1.10.128' from dual
union all select 2021, '10.1.14.151 10.1.14.158' from dual
union all select 2021, '10.1.14.109 10.1.13.152 10.1.13.151 10.1.14.88 10.1.14.92' from dual
union all select 2021, '10.1.12.183 10.1.10.97 10.1.13.47 10.1.10.74' from dual
union all select 2021, '10.1.11.142' from dual
)
Подозреваю, что причина в том, что я неправильно описал условия соединения, не указав Y и RN.
Но не соображу, как их указывать. |
