替代oracle中的REGEXP_LIKE

Can you please tell me if there is any alternative function which I can use for REGEXP_LIKE ?

你能否告诉我是否有可用于REGEXP_LIKE的替代功能?

I have the following situation:

我有以下情况:

Names:
ID NAME
1  Alex
2  Jim
3  Tom

Invoices:
ID Amount Name_IDs
1  100    1;2;
2  200    2;3;

Wanted output:
Invoice_ID Names     Amount:
1          Alex;Jim; 100
2          Jim;Tom;  200

I know that the database model is not quite technically right, but now I'm using a query for this with REGEXP_LIKE to add in a listagg() the names, but the performance is very very slow.

我知道数据库模型在技术上并不完全正确,但现在我使用REGEXP_LIKE对此进行查询以在listagg()中添加名称,但性能非常慢。

SELECT inv.id as invoice_id, 
       SELECT listagg(n.name,';') WITHIN GROUP (ORDER BY NULL) from names where REGEXP_LIKE(inv.names, '(^|\W)' || n.name || '(\W|$) 'as names
       inv.amount as amount
from invoices inv;

Can you please give me any idea how to improve this query ?

你能告诉我如何改进这个查询吗?

Thank you!

2 个解决方案

#1

If you have an index on your names table then this should be faster:

如果你的名字表上有索引,那么这应该更快:

select i.id,
       i.amount,
       (select listagg(n.name, ';') within group(order by null)
          from (select trim(regexp_substr(str, '[^;]+', 1, level)) ASPLIT
                  from (select i.name_ids as str from dual)
                connect by regexp_substr(str, '[^;]+', 1, level) is not null) t
          join names n
            on n.id = t.asplit)
  from invoices i;

i.e. first split by Name_IDs and join with the names table.

即首先按Name_ID分割并与名称表连接。

#2

I suggest that you change your data model to something like the following:

我建议您将数据模型更改为以下内容:

NAMES:
ID_NAMES NAME
10       Alex
20       Jim
30       Tom

INVOICES:
ID_INVOICE Amount
1          100
2          200

INVOICE_NAMES:
ID_INVOICE  ID_NAME
1           10
1           20
2           20
2           30

Best of luck.

祝你好运。

#1