REGEXP_SUBSTR与INSTR和SUBSTR的性能和可读性

郝冥夜

2023-03-14

问题内容：

由此产生的数据集只应delimters之前显示的字符串PLE，#并ALL以正确的顺序。程序包中已经存在的“当前查询”是这样的（DDL和DML在帖子的底部）：

SELECT  DATA1
      , DECODE(SIGN(0 - instr(DATA1, 'PLE')), -1, SUBSTR(DATA1, 1, instr(DATA1, 'PLE') - 1)) GET_DATA_TILL_FIRST_PLE
      , DECODE(SIGN(0 - instr(DATA1, '#')), -1, SUBSTR(DATA1, 1, instr(DATA1, '#') - 1)) GET_DATA_TILL_FIRST_NUM_SIGN
      , DECODE(SIGN(0 - instr(DATA1, 'ALL')), -1, SUBSTR(DATA1, 1, instr(DATA1, 'ALL') - 1)) GET_DATA_TILL_FIRST_ALL
      , NVL(DECODE(SIGN(0 - instr(DATA1, 'PLE')), -1, SUBSTR(DATA1, 1, instr(DATA1, 'PLE') - 1), 0,
        DECODE(SIGN(0 - instr(DATA1, '#')), -1, SUBSTR(DATA1, 1, instr(DATA1, '#') - 1), 0,
        DECODE(SIGN(0 - instr(DATA1, 'ALL')), -1, SUBSTR(DATA1, 1, instr(DATA1, 'ALL') - 1), DATA1), DATA1), DATA1), DATA1) PUT_THEM_ALL_TOGETHER    
FROM    table_x;

哪些结果会生成以下数据集：

DATA1                   | GET_DATA_TILL_FIRST_PLE | GET_DATA_TILL_FIRST_#_SIGN  | GET_DATA_TILL_FIRST_ALL    |  PUT_THEM_ALL_TOGETHER
----------------------- | ----------------------- | --------------------------- | -------------------------- |  ----------------------
STRING_EXAMPLE          | STRING_EXAM             |                             |                            |  STRING_EXAM
TREE_OF_APPLES          | TREE_OF_AP              |                             |                            |  TREE_OF_AP
FIRST_EXAMPLE           | FIRST_EXAM              |                             |                            |  FIRST_EXAM
IMPLEMENTATION          | IM                      |                             |                            |  IM
PARIS                   |                         |                             |                            |  PARIS
PLEONASM                |                         |                             |                            |  PLEONASM
XXXX 1                  |                         |                             |                            |  XXXX 1 
XXXX YYYYYY 2 FFFFFFFFF |                         |                             |                            |  XXXX YYYYYY 2 FFFFFFFFF
XXXX YYYYYY 5FFFFFFFFF  |                         |                             |                            |  XXXX YYYYYY 5FFFFFFFFF
OPOPOPOPO #09090 APPLE  | OPOPOPOPO #09090 AP     | OPOPOPOPO                   | OPOPOPOPO #                |  OPOPOPOPO #09090 AP
OPOPOPOPO BALL#         |                         | OPOPOPOPO BALL              | OPOPOPOPO B                |  OPOPOPOPO BALL
BALL IS #LIFE           |                         | BALL IS                     | B                          |  BALL IS

PS。我只需要专栏，PUT_THEM_ALL_TOGETHER但我也包括其他专栏，但确实添加了上下文。

我发现查询有点混乱并且难以阅读，因此我尝试使用REGEXP_SUBSTR@vkp并提出了建议，我想出了以下查询，结果与上面的数据集相同。

SELECT  DATA1
  , REGEXP_SUBSTR(DATA1, '(.+?)PLE',1,1,null,1) GET_DATA_TILL_FIRST_PLE
  , REGEXP_SUBSTR(DATA1, '(.+?)#',1,1,null,1) GET_DATA_TILL_FIRST_#_SIGN
  , REGEXP_SUBSTR(DATA1, '(.+?)ALL',1,1,null,1) GET_DATA_TILL_FIRST_ALL
  , COALESCE(REGEXP_SUBSTR(DATA1, '(.+?)PLE',1,1,null,1),
             REGEXP_SUBSTR(DATA1, '(.+?)#',1,1,null,1),
             REGEXP_SUBSTR(DATA1, '(.+?)ALL',1,1,null,1),
             DATA1) PUT_THEM_ALL_TOGETHER
FROM    table_x;

然而，从@ MathGuy的答案，似乎INSTR和SUBSTR有效得多。我在某种程度上进行了测试，这就是我得到的：

使用INSTR和SUBSTR：

SET TIMING ON;    
BEGIN
    UPDATE  table_x
    SET     DATA2 = NVL(DECODE(SIGN(0 - instr(DATA1, 'PLE')), -1, SUBSTR(DATA1, 1, instr(DATA1, 'PLE') - 1), 0,
                    DECODE(SIGN(0 - instr(DATA1, '#')), -1, SUBSTR(DATA1, 1, instr(DATA1, '#') - 1), 0,
                    DECODE(SIGN(0 - instr(DATA1, 'ALL')), -1, SUBSTR(DATA1, 1, instr(DATA1, 'ALL') - 1), DATA1), DATA1), DATA1), DATA1);    
    ROLLBACK;        
END;
/

PL / SQL过程成功完成。
播放时间：00：00：00.234

使用REGEXP_SUBSTR：

SET TIMING ON;  
BEGIN    
    UPDATE  table_x
    SET     DATA2 = COALESCE(REGEXP_SUBSTR(DATA1, '(.+?)PLE',1,1,null,1)
                            ,REGEXP_SUBSTR(DATA1, '(.+?)#',1,1,null,1)
                            ,REGEXP_SUBSTR(DATA1, '(.+?)ALL',1,1,null,1)
                            ,DATA1);
    ROLLBACK;        
END;
/

PL / SQL过程成功完成。
播放时间：00：00：00.236

虽然这是一个非常有限的测试数据，它表明的组合INSTR和SUBSTR一点点的速度比REGEXP_SUBSTR。难道是可以忽略不计在使用REGEXP_SUBSTR替代INSTR和SUBSTR提高可读性的缘故？

DML和DDL：

create table table_x 
(
    data1 varchar2(100)    
   ,data2 varchar2(100)
);

INSERT INTO table_x (DATA1) VALUES ('STRING_EXAMPLE');
INSERT INTO table_x (DATA1) VALUES ('TREE_OF_APPLES');
INSERT INTO table_x (DATA1) VALUES ('FIRST_EXAMPLE');  
INSERT INTO table_x (DATA1) VALUES ('IMPLEMENTATION');   
INSERT INTO table_x (DATA1) VALUES ('PARIS');            
INSERT INTO table_x (DATA1) VALUES ('PLEONASM');

INSERT INTO table_x (DATA1) VALUES ('XXXX 1');   
INSERT INTO table_x (DATA1) VALUES ('XXXX YYYYYY 2 FFFFFFFFF'); 
INSERT INTO table_x (DATA1) VALUES ('XXXX YYYYYY 5FFFFFFFFF');

INSERT INTO table_x (DATA1) VALUES ('OPOPOPOPO #09090 APPLE'); 
INSERT INTO table_x (DATA1) VALUES ('OPOPOPOPO BALL#'); 
INSERT INTO table_x (DATA1) VALUES ('BALL IS #LIFE');

谢谢你。

问题答案：

我已经张贴出如何解决使用这一问题的答案INSTR，并SUBSTR以正确的方式。

在本“答案”中，我解决了另一个问题-哪种解决方案更有效。我将在下面解释测试，但这是最重要的一点：REGEXP解决方案花费的时间
比INSTR/SUBSTR解决方案 长40倍 。

设置
：我创建了一个包含150万个随机字符串的表（所有字符串长度均为8个字符，全部为大写字母）。然后，我修改了10％的字符串以添加子字符串'PLE'，再修改了10％的添加了a
'#'，再修改了10％的添加'ALL'。我这样做的分裂在位置上的原始字符串mod(rownum, 9)-这是0和8之间的数字-
和连接'PLE'或'#'或'ALL'在该位置。当然，这不是获取我们所需测试数据的最有效或最优雅的方法，但这无关紧要-
关键是创建测试数据并将其用于我们的测试中。

所以：现在我们有了一个只有一列的表，其中data1有150万行中的一些随机字符串。各10％的子串PLE或#或ALL在其中。

测试包括创建data2与原始帖子中相同的新字符串。我没有将结果插入表中；而是将结果插入表中。无论如何data2计算，将其重新
插入表中的时间都应该相同。

相反，我将主查询放在一个外部查询中，该查询用于计算结果data2值的长度之和。这样，我保证优化器不能采用快捷方式：data2必须生成所有值，必须测量它们的长度，然后将它们求和。

下面是创建基表所需的语句，我将其称为table_z，然后运行了查询。

create table table_z as
select dbms_random.string('U', 8) as data1 from dual
connect by level <= 1500000;

update table_z 
set data1 = case
when rownum between      1 and 150000 then substr(data1, 1, mod(rownum, 9)) 
                               || 'PLE' || substr(data1, mod(rownum, 9) + 1)
when rownum between 150001 and 300000 then substr(data1, 1, mod(rownum, 9)) 
                               || '#'   || substr(data1, mod(rownum, 9) + 1)
when rownum between 300001 and 450000 then substr(data1, 1, mod(rownum, 9)) 
                               || 'ALL' || substr(data1, mod(rownum, 9) + 1)
          end
where rownum <= 450000;

commit;

INSTR/SUBSTR 解决方案

select sum(length(data2))
from (
select data1, 
       case 
         when instr(data1, 'PLE', 2) > 0 then substr(data1, 1, instr(data1, 'PLE', 2) - 1)
         when instr(data1, '#'  , 2) > 0 then substr(data1, 1, instr(data1, '#'  , 2) - 1)
         when instr(data1, 'ALL', 2) > 0 then substr(data1, 1, instr(data1, 'ALL', 2) - 1)
         else data1 end
       as data2
from   table_z
);

SUM(LENGTH(DATA2))
------------------
          10713352

1 row selected.

Elapsed: 00:00:00.73

REGEXP 解决方案

select sum(length(data2))
from (
select data1, 
       COALESCE(REGEXP_SUBSTR(DATA1, '(.+?)PLE',1,1,null,1)
                            ,REGEXP_SUBSTR(DATA1, '(.+?)#',1,1,null,1)
                            ,REGEXP_SUBSTR(DATA1, '(.+?)ALL',1,1,null,1)
                            ,DATA1)
       as data2
from   table_z
);

SUM(LENGTH(DATA2))
------------------
          10713352

1 row selected.

Elapsed: 00:00:30.75

在任何人提出这些建议之前，我都重复了两次查询；第一个解决方案的运行时间通常为0.75到0.80秒，第二个查询的运行时间为30到35秒。慢40倍以上。（因此，编译器/优化器花费时间来编译查询不是问题；这实际上是执行时间。）而且，这与从基表中读取150万个值无关，这与两种测试的时间都比处理要少得多。无论如何，我都会先运行INSTR/SUBSTR查询，因此，如果有任何缓存，那么该REGEXP查询将是一个受益的地方。

编辑
：我只是想出了建议的REGEXP解决方案中的一个低效率。如果我们将搜索模式锚定到字符串的开头（例如'^(.+?)PLE'，注意^锚点），则REGEXP查询的运行时间将从30秒降至10秒。显然，Oracle实现不足以识别这种等效性，并尝试从第二个字符，第三个字符等进行搜索。执行时间仍然要长15倍；15
<40，但仍然相差很大。

REGEXP_SUBSTR与INSTR和SUBSTR的性能和可读性

相关阅读

相关文章

相关问答

相关工具

相关文档