结果数据集应仅以正确的顺序显示分隔符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;
以下数据集中的哪些结果：
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',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;
但是,从@ MathGuy的答案来看,似乎INSTR和SUBSTR效率更高.
我在某种程度上测试了这个,这就是我得到的：
使用INSTR和SUBSTR：
SET TIMING ON;
BEGIN
UPDATE table_x
SET DATA2 = NVL(DECODE(SIGN(0 - instr(DATA1,DATA1);
ROLLBACK;
END;
/
PL/sql procedure successfully completed.
Elapsed: 00:00:00.234
使用REGEXP_SUBSTR：
SET TIMING ON;
BEGIN
UPDATE table_x
SET DATA2 = COALESCE(REGEXP_SUBSTR(DATA1,DATA1);
ROLLBACK;
END;
/
PL/sql procedure successfully completed.
Elapsed: 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');
谢谢.
解决方法
在这个“答案”中,我解决了另一个问题 – 哪种解决方案更有效.我将在下面解释测试,但这里是底线：REGEXP解决方案比INSTR / SUBSTR解决方案长40倍.
设置：我创建了一个包含150万个随机字符串的表(所有字符串长度均为8个字符,全部为大写字母).然后我修改了10％的字符串以添加子字符串’PLE’,另外10％添加’#’和另外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,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;
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
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
在任何人提出这些建议之前：我多次重复这两个问题;第一个解决方案始终在0.75到0.80秒之间运行,第二个查询在30到35秒内运行.慢了40多倍. (因此,编译器/优化器花费时间来编译查询不是问题;它实际上是执行时间.)此外,这与从基表读取150万个值无关 – 这是相同的两种测试都比处理时间少得多.在任何情况下,我首先运行INSTR / SUBSTR查询,因此如果有任何缓存,REGEXP查询将是受益的.
编辑：我刚刚发现了提议的REGEXP解决方案中的一个低效率.如果我们将搜索模式锚定到字符串的开头(例如’^(.？)PLE’,请注意^ anchor),REGEXP查询的运行时间从30秒下降到10秒.显然,Oracle实现不够聪明,无法识别这种等价,并尝试从第二个字符,第三个字符等进行搜索.仍然执行时间几乎是后者的15倍; 15< 40但这仍然是一个非常大的差异.
