Use Oracle unnested VARRAY's instead of IN operator

Question

Let's say users have 1 - n accounts in a system. When they query the database, they may choose to select from m acounts, with m between 1 and n. Typically the SQL generated to fetch their data is something like

SELECT ... FROM ... WHERE account_id IN (?, ?, ..., ?)

So depending on the number of accounts a user has, this will cause a new hard-parse in Oracle, and a new execution plan, etc. Now there are a lot of queries like that and hence, a lot of hard-parses, and maybe the cursor/plan cache will be full quite early, resulting in even more hard-parses.

Instead, I could also write something like this

-- use any of these
CREATE TYPE numbers AS VARRAY(1000) of NUMBER(38);
CREATE TYPE numbers AS TABLE OF NUMBER(38);

SELECT ... FROM ... WHERE account_id IN (
  SELECT column_value FROM TABLE(?)
)

-- or

SELECT ... FROM ... JOIN (
  SELECT column_value FROM TABLE(?)
) ON column_value = account_id

And use JDBC to bind a java.sql.Array (i.e. an oracle.sql.ARRAY) to the single bind variable. Clearly, this will result in less hard-parses and less cursors in the cache for functionally equivalent queries. But is there anything like general a performance-drawback, or any other issues that I might run into?

E.g: Does bind variable peeking work in a similar fashion for varrays or nested tables? Because the amount of data associated with every account may differ greatly.

I'm using Oracle 11g in this case, but I think the question is interesting for any Oracle version.

Answer 1

I suggest you try a plain old join like in

SELECT Col1, Col2
FROM   ACCOUNTS ACCT
       TABLE TAB,
WHERE  ACCT.User = :ParamUser
AND    TAB.account_id = ACCT.account_id;

An alternative could be a table subquery

SELECT Col1, Col2
FROM   (
       SELECT account_id
       FROM   ACCOUNTS
       WHERE  User = :ParamUser
       ) ACCT,
       TABLE TAB
WHERE  TAB.account_id = ACCT.account_id;

or a where subquery

SELECT Col1, Col2
FROM   TABLE TAB
WHERE  TAB.account_id IN 
       (
       SELECT account_id 
       FROM   ACCOUNTS
       WHERE  User = :ParamUser
       );

The first one should be better for perfomance, but you better check them all with explain plan.

Answer 2

Looking at V$SQL_BIND_CAPTURE in a 10g database, I have a few rows where the datatype is VARRAY or NESTED_TABLE; the actual bind values were not captured. In an 11g database, there is just one such row, but it also shows that the bind value is not captured. So I suspect that bind value peeking essentially does not happen for user-defined types.

In my experience, the main problem you run into using nested tables or varrays in this way is that the optimizer does not have a good estimate of the cardinality, which could lead it to generate bad plans. But, there is an (undocumented?) CARDINALITY hint that might be helpful. The problem with that is, if you calculate the actual cardinality of the nested table and include that in the query, you're back to having multiple distinct query texts. Perhaps if you expect that most or all users will have at most 10 accounts, using the hint to indicate that as the cardinality would be helpful. Of course, I'd try it without the hint first, you may not have an issue here at all.

(I also think that perhaps Miguel's answer is the right way to go.)