title: Oracle: Fastest Way in PL/SQL to See if Value Exists: List, VARRAY, or Temp Table
body: <p><strong>UPDATE</strong> View the edits if you care to see the long original question.  This is the clearer short version of the question...</p>
  
  <p>I need to see if <code>GroupA</code> (not always <code>GroupA</code>, 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 <code>GroupA</code>).  So whats fastest in PL/SQL, checking a list...</p>
  
  <p><code>IF 'GroupA' IN ('GroupA','GroupB') THEN...</code></p>
  
  <p>or checking a VARRAY using MEMBER OF...</p>
  
  <pre><code>IF 'GroupA' MEMBER OF myGroups THEN
  </code></pre>
  
  <p>or checking a VARRAY this way...</p>
  
  <pre><code>FOR i IN myGroups.FIRST .. myGroups.LAST
  LOOP
      IF myGroups(i) = 'GroupA' THEN
          v_found := TRUE;
          EXIT;
      END IF;
  END LOOP;
  </code></pre>
  
  <p>or checking associative arrays...
  <code>will test this tomorrow</code></p>
  
  <p><strong>UPDATE: FINAL RESULTS OF TESTING FROM EVERYONE'S SUGGESTIONS</strong>
  Thanks all.
  I ran these tests, looped 10 million times and the commas separated string using a <code>LIKE</code> 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 <code>VARRAY MEMBER OF</code> 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 <code>MEMBER OF</code> to my attention)...</p>
  
  <p>comma separated list, example: ,GroupA,GroupB,GroupC,...around 200 groups... (list made by looping a cursor)</p>
  
  <pre><code>FOR i IN 1 .. 10000000 loop
      if myGroups like '%,NONE,%' then
          z:=z+1;
      end if;
  end loop;
  --690msec
  </code></pre>
  
  <p>same commas separated list (list made by looping a cursor)...</p>
  
  <pre><code>FOR i IN 1 .. 10000000 loop
      if instr(myGroups, ',NONE,') &gt; 0 then   
          z:=z+1;
      end if;
  end loop;
  --818msec
  </code></pre>
  
  <p>varray, same 200 groups (varray made by bulk collect)...</p>
  
  <pre><code>FOR i IN 1 .. 10000000 loop
      IF 'NONE' MEMBER of myGroups THEN
          z:=z+1;
      end if;
  end loop;
  --780msec
  </code></pre>
  
  <p>associative array method suggested by @Yaroslav Shabalin (assoc. array made by looping a cursor)...</p>
  
  <pre><code>FOR i IN 1 .. 10000000 loop
      if (a_values('NONE') = 1) then
          z:=z+1;
      end if;
  end loop;
  --851msec
  </code></pre>