Oracle:PL/SQL 中查看值是否存在的最快方法:列表、VARRAY 或临时表 [英] Oracle: Fastest Way in PL/SQL to See if Value Exists: List, VARRAY, or Temp Table

问题描述

更新 如果您想查看很长的原始问题，请查看编辑.这是问题的更清晰的简短版本...

UPDATE View the edits if you care to see the long original question. This is the clearer short version of the question...

我需要查看 GroupA(并不总是 GroupA，这会改变每次循环迭代)是否存在于 200 或所以组.我如何存储这 200 个组完全由我控制.但是我想将它们存储在一个结构中，该结构适合于进行最快的存在"检查，因为我必须在循环中针对不同的值(并非总是 GroupA)多次检查此列表.那么什么是 PL/SQL 中最快的，检查一个列表...

I need to see if GroupA (not always GroupA, this changes each loop iteration) exists in a [list,varray,temp table, whatever] of 200 or so groups. How I store those 200 groups is totally in my control. But I want to store them in a construct that lends itself to the FASTEST "existence" checking because I will have to check this list MANY times within a loop against different values (not always GroupA). So whats fastest in PL/SQL, checking a list...

IF 'GroupA' IN ('GroupA','GroupB') THEN...

或使用 MEMBER OF... 检查 VARRAY

or checking a VARRAY using MEMBER OF...

IF 'GroupA' MEMBER OF myGroups THEN

或以这种方式检查 VARRAY...

or checking a VARRAY this way...

FOR i IN myGroups.FIRST .. myGroups.LAST
LOOP
    IF myGroups(i) = 'GroupA' THEN
        v_found := TRUE;
        EXIT;
    END IF;
END LOOP;

或检查关联数组...明天会测试这个

更新:测试的最终结果来自每个人的建议谢谢大家.我运行了这些测试，循环了 1000 万次，使用 LIKE 的逗号分隔字符串似乎是最快的，所以我想这些点必须归于@Brian McGinity(时间在下面的评论中).但由于时间都如此接近，所以我采用哪种方法可能并不重要.我想我会使用 VARRAY MEMBER OF 方法，因为我可以用一行代码(批量收集)加载数组，而不必循环游标来构建字符串(感谢@Wernfried让我注意到 MEMBER OF)...

UPDATE: FINAL RESULTS OF TESTING FROM EVERYONE'S SUGGESTIONS Thanks all. I ran these tests, looped 10 million times and the commas separated string using a LIKE seemed to be the fastest so I guess the points have to go to @Brian McGinity (the times are in the comments below). But since the times were all so close it probably doesn't matter which method I go with. I think I'll go with the VARRAY MEMBER OF method since I can load the array with a single line of code (bulk collect) instead of having to loop a cursor to build a string (thanks @Wernfried for bringing MEMBER OF to my attention)...

逗号分隔的列表，例如:,GroupA,GroupB,GroupC,...大约 200 个组...(通过循环游标制作的列表)

comma separated list, example: ,GroupA,GroupB,GroupC,...around 200 groups... (list made by looping a cursor)

FOR i IN 1 .. 10000000 loop
    if myGroups like '%,NONE,%' then
        z:=z+1;
    end if;
end loop;
--690msec

相同的逗号分隔列表(通过循环游标生成的列表)...

same commas separated list (list made by looping a cursor)...

FOR i IN 1 .. 10000000 loop
    if instr(myGroups, ',NONE,') > 0 then   
        z:=z+1;
    end if;
end loop;
--818msec

varray，相同的 200 个组(由批量收集制作的变量)...

varray, same 200 groups (varray made by bulk collect)...

FOR i IN 1 .. 10000000 loop
    IF 'NONE' MEMBER of myGroups THEN
        z:=z+1;
    end if;
end loop;
--780msec

@Yaroslav Shabalin 建议的关联数组方法(通过循环游标制作的关联数组)...

associative array method suggested by @Yaroslav Shabalin (assoc. array made by looping a cursor)...

FOR i IN 1 .. 10000000 loop
    if (a_values('NONE') = 1) then
        z:=z+1;
    end if;
end loop;
--851msec

推荐答案

myGroup 是一个 varray 吗?如果是字符串，请尝试以下操作:

Is myGroup a varray? If it is a string try something like:

select 1
  from dual
 where 'abc,NONE,def' like '%,NONE,%'

很难遵循您正在工作的约束...如果可能，请在 sql 中执行所有操作，这样会更快.

It is hard to follow the constraints you're working under... If at all possible, do everything inside of sql and it will be faster.

因此，如果您已经在 plsql 单元中并且想留在 plsql 单元中，那么上面的逻辑将是这样的:

So if you're already in a plsql unit and wanted to stay in a plsql unit then the logic above would go something like this:

declare
    gp varchar2(200) := 'abc,def,NONE,higlmn,op';
  begin
    if ','||gp||',' like '%,NONE,%' then
      dbms_output.put_line('y');
    else
      dbms_output.put_line('n');
    end if;
  end;

如果这本身是一个循环，那么将列表设为:

if this itself is in a loop then, make the list once as:

declare
    gp varchar2(200)  := 'abc,def,NONE,higlmn,op';
    gp2 varchar2(200) := ',' || gp || ',';
  begin
    if g2 like '%,NONE,%' then
      dbms_output.put_line('y');
    else
      dbms_output.put_line('n');
    end if;
  end;

还可以尝试 instr ，它可能比 like 更快:

Also try instr which is probably faster than like:

  declare
    gp varchar2(200) := ',abc,def,NONE,hig,';
  begin
    if instr(gp, ',NONE,') > 0 then
      dbms_output.put_line('y');
    else
      dbms_output.put_line('n');
    end if;
  end;

我不知道这是否比提到的其他解决方案更快(它很有可能)，这是其他尝试.

I have no idea if this faster than the other solutions mentioned (it stands a good chance), it is something else to try.

这篇关于Oracle:PL/SQL 中查看值是否存在的最快方法:列表、VARRAY 或临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！