我这里有一个查询，如下所示：
SELECT field
FROM table
WHERE value IN ('val1', 'val2', 'val3', ... 'valn')
假设在IN子句中有2000个值，这些值在其他表中不存在。您有什么方法可以加快此操作的速度吗？
这个问题可以接受任何种类的方法...
谢谢！
使用大型“IN”子句优化Oracle SQL
6
- NeoNosliw
1
1值得一提的是，Oracle（无论是11g还是其他版本）如果在这种列表中放置超过1000个值，就会抛出一个错误。除此之外，它的工作表现还算不错。 - O. Jones
10个回答
5
创建一个覆盖 'field' 和 'value' 的索引。
将那些 IN 值放入临时表中并进行连接。
- Mitch Wheat
2
同意第一点。你有2的性能统计信息或链接吗？它似乎有点不直观。在什么时候IN变得低效，而插入到临时表中然后再JOIN到临时表会更有效呢？谢谢。 - StuartLC
这是我在面试中回答的。面试官随后评论说，这个答案以后一定有所涉及。我猜他在谈论对表的并发访问和数据清理方面的问题。 - NeoNosliw
1
SELECT field
FROM table
WHERE value IN SELECT somevalue from sometable
据我所知，您将面临另一个问题。那就是“IN”子句的限制。使用此方法，您可以避免该限制，希望能加快您的查询速度。
- Hendra Jaya
1
您可以将一个普通表与填充有值列表的内存表连接起来。
我不确定如何在Java中实现，但我知道如何使用C#实现。我认为Java应该也有类似的功能。
请参阅此处：http://forums.oracle.com/forums/thread.jspa?threadID=892457&tstart=375
让我们使用用户定义类型（UDT）的集合。 首先创建一个拥有100万行的表：
create table employees (id number(10) not null primary key, name varchar2(100) );
insert into employees
select level l, 'MyName'||to_char(level)
from dual connect by level <= 1e6;
1000000 rows created
commit;
exec dbms_stats.gather_schema_stats(USER, cascade=>TRUE);
现在我们转向C#代码：
让我们选择ID为3和4的员工。
使用集合类型MDSYS.SDO_ELEM_INFO_ARRAY，因为如果我们使用这个已经预定义的Oracle类型，我们就不必定义自己的Oracle类型。您可以使用最多1048576个数字填充集合MDSYS.SDO_ELEM_INFO_ARRAY。
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
[OracleCustomTypeMappingAttribute("MDSYS.SDO_ELEM_INFO_ARRAY")]
public class NumberArrayFactory : IOracleArrayTypeFactory
{
public Array CreateArray(int numElems)
{
return new Decimal[numElems];
}
public Array CreateStatusArray(int numElems)
{
return null;
}
}
private void Test()
{
OracleConnectionStringBuilder b = new OracleConnectionStringBuilder();
b.UserID = "sna";
b.Password = "sna";
b.DataSource = "ora11";
using (OracleConnection conn = new OracleConnection(b.ToString()))
{
conn.Open();
using (OracleCommand comm = conn.CreateCommand())
{
comm.CommandText =
@" select /*+ cardinality(tab 10) */ * " +
@" from employees, table(:1) tab " +
@" where employees.id = tab.column_value";
OracleParameter p = new OracleParameter();
p.OracleDbType = OracleDbType.Array;
p.Direction = ParameterDirection.Input;
p.UdtTypeName = "MDSYS.SDO_ELEM_INFO_ARRAY";
p.Value = new Decimal[] { 3, 4 };
comm.Parameters.Add(p);
int numPersons = 0;
using (OracleDataReader reader = comm.ExecuteReader())
{
while (reader.Read())
{
MessageBox.Show("Name " + reader[1].ToString());
numPersons++;
}
}
conn.Close();
}
}
}
当省略提示 /*+ cardinality(tab 10) */ 时，employees.id 上的索引将不会被使用。这个索引是由 Oracle 创建的，因为 id 是主键列。
这意味着您不必填充临时表。值列表保留在内存中，您可以将表 employees 与内存中的值列表 table(:1) tab 进行连接。
(wateenmooiedag=TTT)
- TTT
0
当我使用以下方法进行类似查询时，我获得了可接受的性能（执行时间接近无条件获取行）。
static final int MAX_QUERY_SET = 1000;
我迭代值并在每个 MAX_QUERY_SET 值上进行单独的查询。因此，对于10K个值，我有10个查询。我按顺序处理查询。
实施此算法后，我能够玩弄常量。对于30或3000的任何值，我都可以获得3倍的执行时间。所以我坚持使用1000。
如果您无法处理多个查询，则可能无法正常工作。我的经验是在不同的数据库（具有每个语句限制为65K字符的Pervasive）上收集的，但我认为这个问题相当普遍，其结论应该是共同的。
- Jarekczek
0
如果您已经在值字段上有一个索引，并且这些值不可用于任何要连接或子查询的表中，那么我认为没有任何优化的可能性。在特殊情况下，如果您的值确实是“val1”，“val2”等，您可以使用like查询，它将使用索引搜索前缀。但我假设那只是一个例子。
- Jörn Horstmann
0
使用绑定变量时，Oracle查询解析和缓存机制的效果更好。如果您的查询可能通过使用它们来获得更好的性能
从表中选择字段 WHERE value IN（？，？ ....）然后根据需要分配值。
最好使用诸如企业管理控制台之类的工具分析实际执行的性能并决定改进。创建索引可能是要执行的第一步。
在另一个表中存储潜在值并使用J Horstmann的建议似乎是一个正确的想法。请试试。
- Jayan
0
为选择更好的解决方案需要更多信息。
- 查询是否经常执行？
- 值val1，val2是否固定？
- 表有多大？
如果查询经常执行，值val1，val2等固定且表很大（拥有20,000行或更多行），则将所有值存储在另一个表中（例如临时表）并在值字段上连接两个表。
如果下面的查询中的表很大，则值字段应该有索引以提高性能。
从表中选择字段 WHERE value IN ('val1'，'val2'，'val3'，......'valn')
两个表都应该进行分析。
更好的性能的原因是，优化器将根据表的特性选择最佳连接方法。如果上述查询中的表非常大，则连接将是嵌套循环连接，并且上述表应该具有列val的索引。
如果上述查询中的表非常小（少于200-300行），则新表（临时表）应该在val列上具有索引。
如果两个表的大小几乎相同，则索引不会起太大作用。
结论：最佳解决方案取决于具体情况。
- Vishnu Gupta
0
如果其他建议都不起作用，而查询需要很长时间，您可以尝试并行运行它。
select /*+ parallel(table) */ field ...
- Jon Heller
0
这似乎是Java中正确的方法：http://knol.google.com/k/oracle-passing-a-list-as-bind-variable#
它类似于C#的解决方案。您的值列表保留在内存中（没有临时表），不会持久化到磁盘，并且使用参数化查询，因此查询执行器不必重新解析每个查询。我没有尝试过Java，但我认为它会很快。
- TTT
1
3上面的链接已失效。 - lubosdz
0
只需将您的 IN 重写为 exists。这样会更快。
- Alex
1
也许你可以包含一个例子？ - Nick Pierpoint
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接