Understanding 'BULK COLLECT' in Oracle Function

Given the following Oracle function:

CREATE or REPLACE FUNCTION foo(id NUMBER, category VARCHAR) RETURN CHAR IS

TYPE MY_ARRAY2 IS TABLE OF NUMBER;
MY_ARRAY MY_ARRAY2;

BEGIN

   SELECT my_id BULK COLLECT INTO my_array FROM my_table

   RETURN (
            CASE WHEN category = 'FOO' AND (id member of MY_ARRAY)
              THEN 'Y'
              ELSE 'N'
            END
   );
END;

What's the nature of the lookup of:

   SELECT my_id BULK COLLECT INTO my_array FROM my_table

Or, put differently, is there anything that I can add to this line or elsewhere to speed up the look-up - perhaps an index?

All you could do is to use a single select with MAX or COUNT

AS
..
..

v_retval VARCHAR2(10);


SELECT MAX(CASE 
            WHEN category = 'FOO'
                AND id = my_id 
                THEN 'Y'
            ELSE 'N'
            END) INTO v_retval
FROM my_table;
RETURN v_retval;

This relies on the fact that string "Y" > "N" . You may also use COUNT(CASE .. and another case where count > 1 THEN 'Y'

Adding an index on id (or other columns referred from the table ) will help to speed up the query

Note that it is better to use procedure arguments of the form p_id and p_category to avoid conflict