Oracle：PL/SQL 中查看值是否存在的最快方法：列表、VARRAY 或临时表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21241680/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle: Fastest Way in PL/SQL to See if Value Exists: List, VARRAY, or Temp Table
提问by gfrobenius
UPDATEView the edits if you care to see the long original question. This is the clearer short version of the question...
更新如果您想查看很长的原始问题，请查看编辑。这是问题的更清晰的简短版本......
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...
我需要查看GroupA（并非总是如此GroupA，这会改变每次循环迭代）是否存在于 200 个左右的组的 [list,varray,temp table,whatever] 中。我如何存储这 200 个组完全由我控制。但是我想将它们存储在一个结构中，该结构有助于进行最快的“存在”检查，因为我必须在循环中针对不同的值（并非总是GroupA）多次检查此列表。那么什么是 PL/SQL 中最快的，检查一个列表......
IF 'GroupA' IN ('GroupA','GroupB') THEN...
IF 'GroupA' IN ('GroupA','GroupB') THEN...
or checking a VARRAY using MEMBER OF...
或使用 MEMBER OF ... 检查 VARRAY
IF 'GroupA' MEMBER OF myGroups THEN
or checking a VARRAY this way...
或以这种方式检查 VARRAY...
FOR i IN myGroups.FIRST .. myGroups.LAST
LOOP
IF myGroups(i) = 'GroupA' THEN
v_found := TRUE;
EXIT;
END IF;
END LOOP;
or checking associative arrays...
will test this tomorrow
或检查关联数组...
will test this tomorrow
UPDATE: FINAL RESULTS OF TESTING FROM EVERYONE'S SUGGESTIONSThanks all.
I ran these tests, looped 10 million times and the commas separated string using a LIKEseemed 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 OFmethod 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 OFto my attention)...
更新：来自每个人的建议的最终测试结果谢谢大家。我运行了这些测试，循环了 1000 万次，使用 a 的逗号分隔字符串LIKE似乎是最快的，所以我想这些点必须归于@Brian McGinity（时间在下面的评论中）。但是由于时间都非常接近，所以我采用哪种方法可能并不重要。我想我会使用该VARRAY MEMBER OF方法，因为我可以用一行代码（批量收集）加载数组，而不必循环游标来构建字符串（感谢 @Wernfried 引起MEMBER OF我的注意）...
comma separated list, example: ,GroupA,GroupB,GroupC,...around 200 groups... (list made by looping a cursor)
逗号分隔的列表，例如： ,GroupA,GroupB,GroupC,...大约 200 个组...（通过循环光标制作的列表）
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, same 200 groups (varray made by bulk collect)...
varray，相同的 200 组（由批量收集制作的 varray）...
FOR i IN 1 .. 10000000 loop
IF 'NONE' MEMBER of myGroups THEN
z:=z+1;
end if;
end loop;
--780msec
associative array method suggested by @Yaroslav Shabalin (assoc. array made by looping a cursor)...
@Yaroslav Shabalin 建议的关联数组方法（通过循环游标制作的关联数组）...
FOR i IN 1 .. 10000000 loop
if (a_values('NONE') = 1) then
z:=z+1;
end if;
end loop;
--851msec
回答by Brian McGinity
Is myGroup a varray? If it is a string try something like:
myGroup 是一个 varray 吗？如果是字符串，请尝试以下操作：
select 1
from dual
where 'abc,NONE,def' like '%,NONE,%'
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.
很难遵循您正在工作的约束......如果可能的话，在 sql 中执行所有操作，它会更快。
Update:
更新：
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:
因此，如果您已经在 plsql 单元中并想留在 plsql 单元中，那么上面的逻辑将是这样的：
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;
Also try instr which is probably faster than like:
也尝试 instr 这可能比这样更快：
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.
我不知道这是否比提到的其他解决方案更快（它很有可能），这是其他尝试。
回答by Wernfried Domscheit
I did not get your full question but perhaps this function helps you: MEMBER Condition
我没有得到你的完整问题，但也许这个功能可以帮助你： 成员条件
WHERE 'groupA' MEMBER of myGroups
回答by Yaroslav Shabalin
Have you considered using associative arraysalso formerly known as "index-by tables"? Associative arrays indexed by string are optimized for efficient lookup by implicitly using the B*-tree organization of the values. It is PL/SQL equivalent to hash tables in other programming languages.
您是否考虑过使用以前称为“索引表”的关联数组？由字符串索引的关联数组通过隐式使用值的 B* 树组织进行优化，以实现高效查找。它是 PL/SQL 等价于其他编程语言中的哈希表。
For example if you define an array as:
例如，如果您将数组定义为：
type t_values is table of number index by varchar2(20);
Then assign GroupAetc. to keys and 1to each respective value:
然后将GroupA等分配给键和1每个相应的值：
a_values t_values;
for c_cursor in (select ...)
loop
a_value(c_cursor.group_name) := 1;
end loop;
When you try to access the value for non-existent index, you will get null. Whereas for any real index you have 1returned;
当您尝试访问不存在索引的值时，您将获得空值。而对于您1返回的任何真实索引；
(a_value('GroupA') = 1) => TRUE
(a_value('Some_not_existent_index') IS NULL) => TRUE
