performance - 使用 Oracle 未嵌套的 VARRAY 而不是 IN 运算符

Question

假设用户在系统中有 1 - n 个帐户。当他们查询数据库时，他们可能会选择从 m 个数中选择，m 介于 1 和 n 之间。通常，为获取数据而生成的 SQL 类似于

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

因此，根据用户拥有的帐户数量，这将在 Oracle 中导致新的硬解析和新的执行计划等。现在有很多这样的查询，因此，很多硬-解析，并且游标/计划缓存可能会很早就满，从而导致更多的硬解析。

相反，我也可以这样写

-- 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)绑定(bind)到单个绑定(bind)变量。显然，对于功能等效的查询，这将导致缓存中更少的硬解析和更少的游标。但是，是否存在一般的性能缺陷或我可能遇到的任何其他问题？

例如:对于 varrays 或嵌套表，绑定(bind)变量查看是否以类似的方式工作？因为与每个帐户关联的数据量可能差别很大。

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

Answer 1

我建议你尝试一个普通的旧连接

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

另一种可能是表子查询

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

第一个应该对性能更好，但你最好用解释计划检查它们。