使用REGEXP_SUBSTR(AGGREGATOR，'[^;] +'，1，LEVEL)的oracle查询速度慢 [英] oracle query slow with REGEXP_SUBSTR(AGGREGATOR,'[^;]+',1,LEVEL)

问题描述

我正在使用此查询来获取diffrent中的行；分隔值

Hi i am using this query to get diffrent row in ; seprate value

表就像

row_id  aggregator
1             12;45
2             25

使用此查询，我希望输出类似

using this query i want output like

row_id  aggregator
1        12
1        45
2        25

我正在使用以下查询

SELECT 
DISTINCT ROW_ID,  
REGEXP_SUBSTR(AGGREGATOR,'[^;]+',1,LEVEL) as AGGREGATOR,                       
FROM DUMMY_1 
CONNECT BY REGEXP_SUBSTR(AGGREGATOR,'[^;]+',1,LEVEL) IS NOT NULL;

但是即使300条记录也很慢
我必须为40000条记录工作.

but it is very slow even for 300 records
I have to work for 40000 records.

推荐答案

已知正则表达式是昂贵的函数，因此在性能至关重要的情况下(如在CONNECT BY子句中使用标准函数)，应尽量减少对正则表达式的使用).

Regular expressions are known to be expensive functions, so you should try to minimize their use when performance is critical (such as using standard functions in the CONNECT BY clause).

使用标准函数(INSTR，SUBSTR，REPLACE)会更高效，但是生成的代码将难以阅读/理解/维护.

Using standard functions (INSTR, SUBSTR, REPLACE) will be more efficient, but the resulting code will be hard to read/understand/maintain.

我无法抗拒编写递归QTE，它比正则表达式和标准函数都效率更高.此外，递归QTE查询可以说具有一定的优雅性.您将需要Oracle 11.2:

I could not resist writing a recursive QTE, which I is much more efficient than both regular expressions and standard functions. Furthermore, recursive QTE queries have arguably some elegance. You'll need Oracle 11.2:

WITH rec_sql(row_id, aggregator, lvl, tail) AS (
SELECT row_id, 
       nvl(substr(aggregator, 1, instr(aggregator, ';') - 1), 
           aggregator),
       1 lvl,
       CASE WHEN instr(aggregator, ';') > 0 THEN
          substr(aggregator, instr(aggregator, ';') + 1)
       END tail
  FROM dummy_1 initialization
UNION ALL
SELECT r.row_id, 
       nvl(substr(tail, 1, instr(tail, ';') - 1), tail), 
       lvl + 1, 
       CASE WHEN instr(tail, ';') > 0 THEN
          substr(tail, instr(tail, ';') + 1)
       END tail
  FROM rec_sql r
 WHERE r.tail IS NOT NULL
)
SELECT * FROM rec_sql;

您可以在 SQLFiddle 上看到，该解决方案非常有效与 @ABCade的解决方案保持一致. (感谢A.B. Cade的测试用例.)

You can see on SQLFiddle that this solution is very performant and on par with @A.B.Cade's solution. (Thanks to A.B.Cade for the test case).

这篇关于使用REGEXP_SUBSTR(AGGREGATOR，'[^;] +'，1，LEVEL)的oracle查询速度慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！