Regexp_likedbms_photoshopЕсли известно максимальное число символов шаблона в начале строки и в конце, то можешь использовать мой подход.
Есть условие:
3) Обязательное условие: 1 тип - 1 строка шаблонов.
Предлагаешь транспонировать в запросе строку из настройки, чтобы разнести по разным строкам каждый отдельный шаблон?Если поставлено требование "1 тип - 1 строка шаблонов" - никто ведь не запрещает в базе хранить в таком виде, как удобно разработчику?
Даже если и запрещает, то модифицируй на лету.
Как я уже сказал, всякие regexp_like, like - это всегда nested loops, что при более менее заметном числе шаблонов приводит к большим проблемам.
1.
2.
3.
4.
5.
6.
create table a as
select
substr((mod(rownum,999)+1)*1000,1,4)||'000000000'||substr((mod(rownum,55)+1)*100,1,3) as str_numb
from dual
where rownum > 0
connect by level <= 1000000;
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.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
SQL> with
2 S as
3 (
4 select 'TYPE_1' N_CLASS, '^3330\d+0300$|^3330\d+0500$|^3330\d+0550$' SETTING from dual
5 union all
6 select 'TYPE_2' N_CLASS, '^5550\d+0300$|^5550\d+0300$|^8880\d+0660$' SETTING from dual
7 union all
8 select 'TYPE_3' N_CLASS, '^9990\d+0440$|^1230\d+0100$|^3330\d+0770$' SETTING from dual
9 ),
10 S_MOD as
11 (
12 select
13 distinct
14 N_CLASS,
15 translate(substr(setting,instr(setting,'^',1,column_value),instr(setting,'$',1,column_value)-instr(setting,'^',1,column_value)),'d^\+','%') SET
TING
16 from s, table(cast(multiset(select rownum from dual connect by level <= length(setting)-length(replace(setting,'|'))+1) as sys.odcinumberlist))
17 )
18 select
19 count(*)
20 from A
21 join S_MOD S on a.str_numb like S.SETTING;
COUNT(*)
----------
183
Elapsed: 00:00:00.87
SQL>
SQL> with
2 S as
3 (
4 select 'TYPE_1' N_CLASS, '^3330\d+0300$|^3330\d+0500$|^3330\d+0550$' SETTING from dual
5 union all
6 select 'TYPE_2' N_CLASS, '^5550\d+0300$|^5550\d+0300$|^8880\d+0660$' SETTING from dual
7 union all
8 select 'TYPE_3' N_CLASS, '^9990\d+0440$|^1230\d+0100$|^3330\d+0770$' SETTING from dual
9 ),
10 S_MOD as
11 (
12 select
13 distinct
14 N_CLASS,
15 translate(substr(setting,instr(setting,'^',1,column_value),instr(setting,'$',1,column_value)-instr(setting,'^',1,column_value)),'_^\d+','_') SE
TTING
16 from s, table(cast(multiset(select rownum from dual connect by level <= length(setting)-length(replace(setting,'|'))+1) as sys.odcinumberlist))
17 )
18 select
19 count(*)
20 from A
21 join S_MOD S on substr(a.str_numb,1,4)||substr(a.str_numb,-4)=s.setting;
COUNT(*)
----------
183
Elapsed: 00:00:00.64
SQL>
SQL> with
2 S as
3 (
4 select 'TYPE_1' N_CLASS, '^3330\d+0300$|^3330\d+0500$|^3330\d+0550$' SETTING from dual
5 union all
6 select 'TYPE_2' N_CLASS, '^5550\d+0300$|^5550\d+0300$|^8880\d+0660$' SETTING from dual
7 union all
8 select 'TYPE_3' N_CLASS, '^9990\d+0440$|^1230\d+0100$|^3330\d+0770$' SETTING from dual
9 ),
10 S_MOD as
11 (
12 select
13 distinct
14 N_CLASS,
15 translate(substr(setting,instr(setting,'^',1,column_value),instr(setting,'$',1,column_value)-instr(setting,'^',1,column_value)),'_^\d+','_') SE
TTING
16 from s, table(cast(multiset(select rownum from dual connect by level <= length(setting)-length(replace(setting,'|'))+1) as sys.odcinumberlist))
17 )
18 select --+ use_nl(s a) leading(s)
19 count(*)
20 from A
21 join S_MOD S on substr(a.str_numb,1,4)||substr(a.str_numb,-4)=s.setting;
COUNT(*)
----------
183
Elapsed: 00:00:03.43
SQL>
SQL> with
2 S as
3 (
4 select 'TYPE_1' N_CLASS, '^3330\d+0300$|^3330\d+0500$|^3330\d+0550$' SETTING from dual
5 union all
6 select 'TYPE_2' N_CLASS, '^5550\d+0300$|^5550\d+0300$|^8880\d+0660$' SETTING from dual
7 union all
8 select 'TYPE_3' N_CLASS, '^9990\d+0440$|^1230\d+0100$|^3330\d+0770$' SETTING from dual
9 ),
10 S_MOD as
11 (
12 select
13 distinct
14 N_CLASS,
15 translate(substr(setting,instr(setting,'^',1,column_value),instr(setting,'$',1,column_value)-instr(setting,'^',1,column_value)),'d^\+','%') SET
TING
16 from s, table(cast(multiset(select rownum from dual connect by level <= length(setting)-length(replace(setting,'|'))+1) as sys.odcinumberlist))
17 union all
18 select 'dummy', 'padding' from dual connect by rownum <= 1000
19 )
20 select
21 count(*)
22 from A
23 join S_MOD S on a.str_numb like S.SETTING;
COUNT(*)
----------
183
Elapsed: 00:01:22.67
SQL>
SQL> with
2 S as
3 (
4 select 'TYPE_1' N_CLASS, '^3330\d+0300$|^3330\d+0500$|^3330\d+0550$' SETTING from dual
5 union all
6 select 'TYPE_2' N_CLASS, '^5550\d+0300$|^5550\d+0300$|^8880\d+0660$' SETTING from dual
7 union all
8 select 'TYPE_3' N_CLASS, '^9990\d+0440$|^1230\d+0100$|^3330\d+0770$' SETTING from dual
9 ),
10 S_MOD as
11 (
12 select
13 distinct
14 N_CLASS,
15 translate(substr(setting,instr(setting,'^',1,column_value),instr(setting,'$',1,column_value)-instr(setting,'^',1,column_value)),'_^\d+','_') SE
TTING
16 from s, table(cast(multiset(select rownum from dual connect by level <= length(setting)-length(replace(setting,'|'))+1) as sys.odcinumberlist))
17 union all
18 select 'dummy', 'padding' from dual connect by rownum <= 1000
19 )
20 select
21 count(*)
22 from A
23 join S_MOD S on substr(a.str_numb,1,4)||substr(a.str_numb,-4)=s.setting;
COUNT(*)
----------
183
Elapsed: 00:00:00.67
Как видно из воспроизведения, если шаблонов очень мало, то like раобтает соизмеримо с моим вариантом. Мой вариант, захинтованый как nested loops немного уступает, что говорит о том, что substr + || более дорогостоящие операции чем like. Если шаблонов достаточно много то 00:01:22.67 vs 00:00:00.67 достаточно красноречиво говорит о том какой подход избирать.
Для полноты картины, оригинальный подход 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
SQL> with
2 S as
3 (
4 select 'TYPE_1' N_CLASS, '^3330\d+0300$|^3330\d+0500$|^3330\d+0550$' SETTING from dual
5 union all
6 select 'TYPE_2' N_CLASS, '^5550\d+0300$|^5550\d+0300$|^8880\d+0660$' SETTING from dual
7 union all
8 select 'TYPE_3' N_CLASS, '^9990\d+0440$|^1230\d+0100$|^3330\d+0770$' SETTING from dual
9 )
10 select
11 count(*)
12 from A
13 join S on regexp_like(str_numb,S.SETTING);
COUNT(*)
----------
183
Elapsed: 00:01:00.25
|
