How do I check for a IN condition against a dynamic list in Oracle?

Question

EDIT: changed the title to fit the code below.

I'm trying to retrieve a list of acceptable values from an Oracle table, then performing a SELECT against another while comparing some fields against said list.

I was trying to do this with cursors (like below), but this fails.

DECLARE
    TYPE gcur IS REF CURSOR;
    TYPE list_record IS TABLE OF my_table.my_field%TYPE;
    c_GENERIC gcur;
    c_LIST list_record;
BEGIN
    OPEN c_GENERIC FOR
    SELECT my_field FROM my_table
    WHERE some_field = some_value;

    FETCH c_GENERIC BULK COLLECT INTO c_LIST;

    -- try to check against list
    SELECT * FROM some_other_table
    WHERE some_critical_field IN c_LIST;

END

Basically, what I'm trying to do is to cache the acceptable values list into a variable, because I will be checking against it repeatedly later.

How do you perform this in Oracle?

Vesica

Annoyingly, your code would work perfectly if your final operation was an UPDATE or DELETE - it is only SELECTs that don't accept bulk-binding as an input. – Hedrick

btw, you have resource leak - haven't closed ref cursor. Why not good old local cursor ? – Solingen

Answer 1

We can use collections to store values to suit your purposes, but they need to be declared as SQL types:

create type list_record is table of varchar2(128)
/

This is because we cannot use PL/SQL types in SQL statements. Alas this means we cannot use %TYPE or %ROWTYPE, because they are PL/SQL keywords.

Your procedure would then look like this:

DECLARE
    c_LIST list_record;
BEGIN

    SELECT my_field 
    BULK COLLECT INTO c_LIST 
    FROM my_table
    WHERE some_field = some_value;

    -- try to check against list
    SELECT * FROM some_other_table
    WHERE some_critical_field IN ( select * from table (c_LIST);

END;    

---

"I see that you still had to perform a SELECT statement to populate the list for the IN clause."

If the values are in a table there is no other way to get them into a variable :)

"I'm thinking that there's a significant performance gain using this over a direct semi-join"

Not necessarily. If you're only using the values once then the sub-query is certainly the better approach. But as you want to use the same values in a number of discrete queries then populating a collection is the more efficient approach.

In 11g Enterprise Edition we have the option to use result set caching. This is a much better solution, but one which is not suited for all tables.

Peterman

Thanks. I get what you're saying, but I see that you still had to perform a SELECT statement to populate the list for the IN clause. I'm thinking that there's a significant performance gain using this over a direct semi-join (as offered by Adam Musch below)? :) – Vesica

Thanks. The name list_record did confuse a bit though, since it really is a table... – Affect

@UlrikLarsen - but it's not a TABLE, it's a TYPE. Types don't store data, but are used used to define structures (persistent or session bound) which do store data. Although if it were my code I would have given it a better name - or even used an Oracle built-in type such as sys.dbms_debug_vc2coll. But I kept the Seeker's original name to make it clearer to them what I had changed to make their code work. – Peterman

True, it is not a table, it is a type that 'is table of...'. Thus, it should not be named _record, but rather _table, agree? – Affect

Answer 2

Why pull the list instead of using a semi-join?

SELECT * 
  FROM some_other_table 
 WHERE some_critical_field IN (SELECT my_field 
                                FROM my_table
                               WHERE some_field = some_value); 

Liebman

Because the primary SELECT statement may be performed repeatedly. I was thinking that any nested SELECTs would be inefficient if the number of iterations was really high, so I'm trying to cache my list instead. – Vesica

Not to sound snide, but it sounds like you think you have a performance optimization problem, not that you know you have one. I'd recommend benchmarking what I've provided vs. APC's solution to see if there's any significant difference. – Liebman

Actually, I do know I have an optimization problem. I'm stuck refactoring someone else's code well into the dawn right now because an SP made up of blocks like the code I pseudo-typed above is buckling against our large data sets. :) Truth be told, I'm really interested in benchmarking both your solutions to sate my curiosity, but it's just that I'd rather try something new to get this finally over with. :D – Vesica

Provided the nested sub-query doesn't reference anything in the main query, Oracle should determine that the sub-query can be executed once, and then re-use the result set. That should be identical to having C_LIST as a variable / object collection type. We all know the optimiser can often decide to do the worst possible thing instead. Hints may help, or otherwise forcing a different execution plan. – Hedrick