Oracle 10g中的字符串匹配,其中任何一方都可以使用通配符

Oracle 10g确实有一个正则表达式函数,在这种情况下可能会对您有所帮助。 http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_regexp.htm

此外,如果必须在数据库中执行此操作,则可以查看java存储过程。

我不知道有什么索引可以在这种情况下帮助你,尽管u可以出现在任何地方,包括第一个字符。

如果是这样,您可以通过以下方式限制可能性

select VALS from tmp 
where specialmatch(vals,'12945678')
and (substr(vals,1,4) = substr('12945678',1,4) 
      or substr(vals,5) = substr('12945678',5));

我似乎记得读到过这样一篇文章,FBI可能会遇到一个问题,即没有为这些问题制定最佳计划,因此可以使用另一种SQL

    select VALS from tmp 
    where specialmatch(vals,'12945678')
    and substr(vals,1,4) = substr('12945678',1,4)
    union 
    select VALS from tmp 
    where specialmatch(vals,'12945678')
    substr(vals,5) = substr('12945678',5));

下一步建议。 简单选项:u是LIKE的单字符匹配,因此简单的解决方案是

SELECT * FROM tmp WHERE vals LIKE v_param OR v_param LIKE vals;

每次都将进行一次完整的表扫描,但保存了SQL和PL/SQL层之间的切换

复合期权

我做了一个测试。首先,我将10000个值加载到TMP中,几乎是随机生成的。不确定数据集有多大,或者不带通配符、一个通配符或多个通配符的条目的比例。这将对结果产生重大影响。

create table tmp ( vals varchar(8),  mask varchar(8));

insert into tmp
select new_val, translate(new_val,'0123456789','__________')
from
  (select case 
         when rn_3  is not null then translate(val,'34','__')
         when rn_5  is not null then translate(val,'2','_')
         when rn_7  is not null then translate(val,'78','__')
         when rn_11 is not null then translate(val,'12345','_____')
         else val end new_val
  from
    (select lpad(trunc(dbms_random.value(1,99999999)),8,'0') val, 
            decode(mod(rownum,3),0,1) rn_3, decode(mod(rownum,5),0,1) rn_5, 
            decode(mod(rownum,7),0,1) rn_7, decode(mod(rownum,11),0,1) rn_11
     from dual connect by level < 10000)
  )

declare
  cursor c_1 is
    select case 
         when rn_3  is not null then translate(val,'34','__')
         when rn_5  is not null then translate(val,'2','_')
         when rn_7  is not null then translate(val,'78','__')
         when rn_11 is not null then translate(val,'12345','_____')
         else val end try_val
    from
      (select lpad(trunc(dbms_random.value(1,99999999)),8,'0') val, 
              decode(mod(rownum,3),0,1) rn_3, decode(mod(rownum,5),0,1) rn_5, 
              decode(mod(rownum,7),0,1) rn_7, decode(mod(rownum,11),0,1) rn_11
       from dual connect by level < 1000);
  v_cnt number;
  v_start number;
  v_end number;
begin
 v_start := dbms_utility.get_time;
 for c_rec in c_1 loop
    select count(*) into v_cnt 
    from tmp 
    where (c_rec.try_val like vals or vals like c_rec.try_val);
 end loop;
 v_end := dbms_utility.get_time;
 dbms_output.put_line('Meth 1 :'||(v_end - v_start));
 v_start := dbms_utility.get_time;
 for c_rec in c_1 loop
    select count(*) into v_cnt from
      (select * from (select * from tmp where mask = '        ') v1 
       where vals like c_rec.try_val
       union all
       select * from (select * from tmp where mask > '        ') v2 
       where vals like maskmerge(mask,c_rec.try_val));
 end loop;
 v_end := dbms_utility.get_time;
 dbms_output.put_line('Meth 2 :'||(v_end - v_start));
end;
/

我比较了“双头”和“面具合并”。在测试中,这类测试通常需要200-250(百分之一秒),而马斯克麦格测试则需要大约十倍的时间。正如我所说,这将在很大程度上取决于数据分布。

select * from (select * from tmp where mask = '        ') v1 where vals like :srch
 union all
select * from (select * from tmp where mask > '        ') v2 where vals like maskmerge(mask,:srch);

现在,优化器说:

Operation                                Object Name       Rows Bytes Cost
SELECT STATEMENT Optimizer Mode=ALL_ROWS                      2          5    
  UNION-ALL
    TABLE ACCESS BY INDEX ROWID          SCHEMA.TMP           1    90    2  
      INDEX RANGE SCAN                   SCHEMA.I_TMP_MASK    1          1
    TABLE ACCESS BY INDEX ROWID          SCHEMA.TMP           1    90    3
      INDEX RANGE SCAN                   SCHEMA.I_TMP_MASK    2          1

这非常好,即使my:srch中有通配符,Oracle也可以进行优化。

最后,即使没有提示,VAL和mask COL的标准索引也足以实现这一目的。在10g上测试。注意:我们使用union all,因为v1和v2总是互斥的。

CREATE OR REPLACE FUNCTION maskmerge (A IN VARCHAR, B IN VARCHAR)
 RETURN VARCHAR deterministic parallel_enable
 IS  
 alen int;
 blen int;
 mlen int;   
 res varchar(4000);
 ca char;
 cb char;
BEGIN
 if (a is null) then
  return b;
 end if;
 if (b is null) then
  return a;
 end if;
 alen:=length(a);
 blen:=length(b);
 if (alen<blen) then
  mlen:=alen;
 else
  mlen:=blen;
 end if;
 for i in 1 .. mlen loop
  ca:=substr(a,i,1);
  cb:=substr(b,i,1);
  if (ca='_' or cb='_') then
   res:=res||'_';
  elsif (ca=' ') then
   res:=res||cb;
  elsif (cb=' ') then
   res:=res||ca;
  else
   res:=res||cb;
  end if;
 end loop;
 return res;
END;

完整测试用例(具有典型数据分布):

-----------------------------------------------------------------

CREATE OR REPLACE FUNCTION maskmerge (A IN VARCHAR, B IN VARCHAR)
 RETURN VARCHAR deterministic parallel_enable
 IS  
 alen int;
 blen int;
 mlen int;   
 res varchar(4000);
 ca char;
 cb char;
BEGIN
 if (a is null) then
  return b;
 end if;
 if (b is null) then
  return a;
 end if;
 alen:=length(a);
 blen:=length(b);
 if (alen<blen) then
  mlen:=alen;
 else
  mlen:=blen;
 end if;
 for i in 1 .. mlen loop
  ca:=substr(a,i,1);
  cb:=substr(b,i,1);
  if (ca='_' or cb='_') then
   res:=res||'_';
  elsif (ca=' ') then
   res:=res||cb;
  elsif (cb=' ') then
   res:=res||ca;
  else
   res:=res||cb;
  end if;
 end loop;
 return res;
END;
/

create table tmp
(
id int not null primary key,
ipv6address varchar(32) not null,
ipv6addressmask varchar(32) default ('                                ') not null
);

create sequence s_tmp;

create index i_tmp_addr on tmp(ipv6address);

create index i_tmp_mask on tmp(ipv6addressmask);

create or replace trigger t_i_tmp before insert on tmp referencing new as new old as old FOR EACH ROW
DECLARE
    tmpVar tmp.id%TYPE;
begin
SELECT s_tmp.NEXTVAL INTO tmpVar FROM dual;
:new.id:=tmpVar;
end;

exec dbms_random.initialize(17809465);

insert into tmp (ipv6address) 
select decode(trunc(dbms_random.value(0,2)),0,'20010db80000000000000000',1,'00000000000000000000ffff','00000000000000000000ffff') 
||trim(to_char(dbms_random.value(0, 4294967296),'0000000x')) 
as val from dual
connect by level <= 10000;

insert into tmp
SELECT * FROM
( SELECT * FROM tmp
ORDER BY dbms_random.value )
WHERE rownum <= 200;

insert into tmp values (null,'00000000000000000000ffff12345678','                                ');

insert into tmp values (null,'00000000000000000000ffff12345678','                              _ ');

insert into tmp values (null,'00000000000000000000ffff1234567_','                              __');

--select * from tmp order by ipv6address

-- network redaction of ipv4 
update tmp set ipv6addressmask=maskmerge('                        ______  ',ipv6addressmask),ipv6address=maskmerge('                        ______  ',ipv6address) where length(ipv6address)/32*dbms_random.value<0.005;

-- host redaction of ipv4 
update tmp set ipv6addressmask=maskmerge('                              __',ipv6addressmask),ipv6address=maskmerge('                              __',ipv6address) where length(ipv6address)/32*dbms_random.value<0.005;

-- full redaction of ipv4 
update tmp set ipv6addressmask=maskmerge('                              __',ipv6addressmask),ipv6address=maskmerge('                              __',ipv6address) where ipv6addressmask='                        ______  ' and length(ipv6address)/32*dbms_random.value<0.04;

-- network report redaction of ipv4 
update tmp set ipv6addressmask=maskmerge('                        ______  ',ipv6addressmask) where length(ipv6address)/32*dbms_random.value<0.005;

-- host report redaction of ipv4 
update tmp set ipv6addressmask=maskmerge('                              __',ipv6addressmask) where length(ipv6address)/32*dbms_random.value<0.005;

-- full report redaction of ipv4 
update tmp set ipv6addressmask=maskmerge('                              __',ipv6addressmask) where ipv6addressmask='                        ______  ' and length(ipv6address)/32*dbms_random.value<0.04;

select count(*) from tmp where instr(ipv6address,'_')>0;

select count(*) from tmp where ipv6addressmask > '                                '; 

-- srch := '00000000000000000000ffff12345678';  

select * from (select * from tmp where ipv6addressmask = '                                ') v1 where ipv6address like :srch
 union all
select * from (select * from tmp where ipv6addressmask > '                                ') v2 where ipv6address like maskmerge(ipv6addressmask,:srch);

/*
Operation                                Object Name Rows Bytes Cost
---------------------------------------- ----------- ---- ----- ----
SELECT STATEMENT Optimizer Mode=ALL_ROWS              510         29                                 
  UNION-ALL                                              
    TABLE ACCESS BY INDEX ROWID          TMP          500   23K   10                                 
      INDEX RANGE SCAN                   I_TMP_ADDR    92          2                                 
    TABLE ACCESS BY INDEX ROWID          TMP           10   490   19                                 
      INDEX RANGE SCAN                   I_TMP_MASK   207          2                                 

*/

SELECT * FROM tmp WHERE ipv6address LIKE :srch OR :srch LIKE ipv6address

/*

Operation                                Object Name Rows Bytes Cost
---------------------------------------- ----------- ---- ----- ----
SELECT STATEMENT Optimizer Mode=ALL_ROWS              995         22                                 
  TABLE ACCESS FULL                      TMP          995   47K   22                                 

*/

-----------------------------------------------------------------

drop table tmp;

drop sequence s_tmp;

drop function maskmerge;

-----------------------------------------------------------------

仅供参考, 我发现,当您需要在%var%上进行匹配并使其能够快速处理大量数据时,最好的方法是使用Oracle的Oracle文本索引。