分类导航
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLOceanBase

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

MsSQL   发布时间：2022-05-16  发布网站：大佬教程  code.js-code.com
大佬教程收集整理的这篇文章主要介绍了sql – REGEXP_SUBSTR与INSTR和SUBSTR的性能和可读性，大佬教程大佬觉得挺不错的，现在分享给大家，也给大家做个参考。
从我的另一个问题,Using REGEXP_SUBSTR with Strings Qualifier,我正在试图决定哪种方法更好用. @H_673_2@结果数据集应仅以正确的顺序显示分隔符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,'#')),'#') - 1)) GET_DATA_TILL_FirsT_NUM_SIGN,'ALL')),'ALL') - 1)) GET_DATA_TILL_FirsT_ALL,NVL(DECODE(SIGN(0 - instr(DATA1,'PLE') - 1),'#') - 1),'ALL') - 1),DATA1),DATA1) PUT_THEM_all_TOGETHER    
FROM    table_x;
@H_673_2@以下数据集中的哪些结果：

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
@H_673_2@Ps.我只需要列PUT_THEM_all_TOGETHER但我包括其他列以及添加上下文.

@H_673_2@我发现查询位有点令人困惑,难以阅读,所以我尝试使用REGEXP_SUBSTR和@vkp的建议,我提出了以下查询
这导致上面的相同数据集.

SELECT  DATA1,REGEXP_SUBSTR(DATA1,'(.+?)PLE',null,1) GET_DATA_TILL_FirsT_PLE,'(.+?)#',1) GET_DATA_TILL_FirsT_#_SIGN,'(.+?)ALL',1) GET_DATA_TILL_FirsT_ALL,COALESCE(REGEXP_SUBSTR(DATA1,1),DATA1) PUT_THEM_all_TOGETHER
FROM    table_x;
@H_673_2@但是,从@ MathGuy的答案来看,似乎instr和SUBSTR效率更高.
我在某种程度上测试了这个,这就是我得到的：

@H_673_2@使用instr和SUBSTR：

SET TIMING ON;    
BEGIN
    updatE  table_x
    SET     DATA2 = NVL(DECODE(SIGN(0 - instr(DATA1,DATA1);    
    ROLLBACK;        
END;
/
@H_673_2@使用REGEXP_SUBSTR：

SET TIMING ON;  
BEGIN    
    updatE  table_x
    SET     DATA2 = COALESCE(REGEXP_SUBSTR(DATA1,DATA1);
    ROLLBACK;        
END;
/
@H_673_2@虽然这是一个非常有限的测试数据,但它表明instr和SUBSTR的组合比REGEXP_SUBSTR快一点.
为了便于阅读,使用REGEXP_SUBSTR而不是instr和SUBSTR可以忽略不计吗？

@H_673_2@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');
@H_673_2@谢谢.

解决方法

我已经发布了一个答案,展示了如何使用instr和SUBSTR以正确的方式解决这个问题. @H_673_2@在这个“答案”中,我解决了另一个问题 – 哪种解决方案更有效.我将在下面解释测试,但这里是底线：REGEXP解决方案比instr / SUBSTR解决方案长40倍.

@H_673_2@设置：我创建了一个@R_536_11262@50万个随机字符串的表(所有字符串长度均为8个字符,全部为大写字母).然后我修改了10％的字符串以添加子字符串’PLE’,另外10％添加’#’和另外10％添加’ALL’.我通过在位置mod(rownum,9)处分割原始字符串(即0到8之间的数字)并在该位置连接’PLE’或’#’或’ALL’来完成此操作.当然,这不是获得我们所需的测试数据的最有效或最优雅的方式,但这是无关紧要的 – 关键是创建测试数据并在我们的测试中使用它.

@H_673_2@所以：我们现在有一个只有一列data1的表,其中包含一些150万行的随机字符串.每个10％都有子串PLE或#或ALl.

@H_673_2@测试包括在原始帖子中创建新的字符串data2.我没有将结果插回表中;无论data2如何计算,将其重新插入表中的时间应该相同.

@H_673_2@相反,我将主查询放在外部查询中,计算结果data2值的长度总和.这样我保证优化器不能采用快捷方式：必须生成所有data2值,必须测量它们的长度,然后将它们相加在一起.

@H_673_2@下面是创建基表所需的语句,我称之为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,mod(rownum,9)) 
                               || 'PLE' || substr(data1,9) + 1)
when rownum between 150001 and 300000 then substr(data1,9)) 
                               || '#'   || substr(data1,9) + 1)
when rownum between 300001 and 450000 then substr(data1,9)) 
                               || 'ALL' || substr(data1,9) + 1)
          end
where rownum <= 450000;

commit;
@H_673_2@instr / SUBSTR解决方案

SELEct sum(length(data2))
from (
SELEct data1,case 
         when instr(data1,'PLE',2) > 0 then substr(data1,instr(data1,2) - 1)
         when 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
@H_673_2@REGEXP解决方案

SELEct sum(length(data2))
from (
SELEct data1,DATA1)
       as data2
from   table_z
);

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

1 row SELEcted.

Elapsed: 00:00:30.75
@H_673_2@在任何人提出这些建议之前：我多次重复这两个问题;第一个解决方案始终在0.75到0.80秒之间运行,第二个查询在30到35秒内运行.慢了40多倍. (因此,编译器/优化器花费时间来编译查询不是问题;它实际上是执行时间.)此外,这与从基表读取150万个值无关 – 这是相同的两种测试都比处理时间少得多.在任何情况下,我首先运行instr / SUBSTR查询,因此如果有任何缓存,REGEXP查询将是受益的.

@H_673_2@编辑：我刚刚发现了提议的REGEXP解决方案中的一个低效率.如果我们将搜索模式锚定到字符串的开头(例如’^(.？)PLE’,请注意^ anchor),REGEXP查询的运行时间从30秒下降到10秒.显然,Oracle实现不够聪明,无法识别这种等价,并尝试从第二个字符,第三个字符等进行搜索.仍然执行时间几乎是后者的15倍; 15< 40但这仍然是一个非常大的差异.

大佬总结

以上是大佬教程为你收集整理的sql – REGEXP_SUBSTR与INSTR和SUBSTR的性能和可读性全部内容，希望文章能够帮你解决sql – REGEXP_SUBSTR与INSTR和SUBSTR的性能和可读性所遇到的程序开发问题。

如果觉得大佬教程网站内容还不错，欢迎将大佬教程推荐给程序员好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ：384754419，请注明来意。
标签：
猜你在找的MsSQL相关文章
其他相关热搜词更多
phpJavaPython程序员load中如何string使用参数jquery开发安装listlinuxiosandroid工具javascriptcap