使用Oracle未嵌套的VARRAY代替IN运算符 [英] Use Oracle unnested VARRAY's instead of IN operator

问题描述

假设用户在系统中拥有1 - n个帐户.当他们查询数据库时，他们可以选择使用m between 1 and n从m个帐户中进行选择.通常，生成用于获取其数据的SQL类似于

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 (?, ?, ..., ?)

因此，根据用户拥有的帐户数，这将导致Oracle中进行新的硬解析以及执行计划等.现在有很多类似的查询，因此，很多困难-解析，也许游标/计划缓存会在很早的时候就满了，从而导致更多的硬解析.

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.

相反，我也可以这样写

-- 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

并使用JDBC将java.sql.Array(即oracle.sql.ARRAY)绑定到单个绑定变量.显然，对于功能等效的查询，这将导致较少的硬解析和较少的游标.但是是否存在一般的性能下降之类的问题，或者我可能遇到的任何其他问题?

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?

例如:对于varray或嵌套表，绑定变量偷看是否以类似的方式工作?因为与每个帐户相关联的数据量可能相差很大.

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.

在这种情况下，我使用的是Oracle 11g，但是我认为这个问题对于任何Oracle版本都是有趣的.

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

推荐答案

我建议您尝试像

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;

或where子查询

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.

这篇关于使用Oracle未嵌套的VARRAY代替IN运算符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！