Understanding 'BULK COLLECT' in Oracle Function

Question

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?

Kaushik Nayak · Accepted Answer · 2018-08-02T15:22:58.437

3

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

edited Aug 02 '18 at 15:22

answered Aug 02 '18 at 12:44

Kaushik Nayak

30,772
5
32
45

1

Given that the RETURN value is a scalar this response is more pertinent than mine :) – APC Aug 02 '18 at 12:54
Thanks! Why is the MAX needed? – Kevin Meredith Aug 02 '18 at 13:14
2

The `MAX()` gives you one scalar result, which will be `'Y'` if any records match your criteria, else it will b e`'N'` – APC Aug 02 '18 at 13:26
Too messy and hard to understand. Someone who would read that later trying to fix something gonna get mad. – Alexander Aug 02 '18 at 13:44

APC · Answer 2 · 2018-08-02T13:01:26.443

BULK COLLECT is simply a method for populating an PL/SQL collection (array) with a result set of multiple rows. Without it we're restricted to populating a scalar value with a single row.

In terms of performance, the biggest impact is actually the efficiency of the query, which you can tune in the usual fashion. Otherwise, the performance of BULK COLLECT is largely transparent = unless you're selecting lots (thousands) of rows. That matters because collections reside in session level memory, so very big collections (lots of rows, many columns) can lead to paging (writing to disk) if your PGA is not configured well.

If you are running into memory problems you could use BULK COLLECT with the LIMIT clause to fetch small subsets of the records and spit them out using a pipelined function implementation. But you really should look at the performance of the populating query first.

So looking up a value form the array is O(n)?

Looping through a collection is linear (at best). It's usually way more efficient to restrict a result set using SQL than to select everything and filter the result set in a loop. SQL is highly efficient at processing sets. In other words, Kaushik's solution.

Alexander · Answer 3 · 2018-08-02T13:46:06.267

0

This would be way more readable. Sure you could put index on id field in your table. If id is not a primary key (i.e. there may be duplicates), use ROWNUM = 1 in WHERE clause.

FUNCTION foo(p_id NUMBER, p_category VARCHAR) RETURN CHAR IS
n NUMBER;
BEGIN
    SELECT id INTO n FROM table WHERE id = p_id AND p_category = 'FOO';
    RETURN 'Y';
EXCEPTION WHEN OTHERS THEN
    RETURN 'N';
END;

edited Aug 02 '18 at 13:46

answered Aug 02 '18 at 13:40

Alexander

61
4

This will raise a no data found when there are no values for that id. The mess you were talking about was to avoid that. – Kaushik Nayak Aug 02 '18 at 15:13
1

This assumes `(ID, CATEGORY)` forms a unique key, which we don't know to be the case. Also, using WHEN OTHERS rather than WHEN NO_DATA_FOUND is poor practice. More generally, using exception handlers to manage valid state has debatable merit. – APC Aug 02 '18 at 15:19

Understanding 'BULK COLLECT' in Oracle Function

3 Answers3