Oracle SQL：如何在 IN 子句中使用 1000 多个项目

Oracle SQL: How to use more than 1000 items inside an IN clause

提问by Mehper C. Palavuzlar

I have an SQL statement where I would like to get data of 1200 ep_codesby making use of INclause. When I include more than 1000 ep_codesinside IN clause, Oracle says I'm not allowed to do that. To overcome this, I tried to change the SQL code as follows:

我有一个 SQL 语句，我想ep_codes通过使用IN子句来获取 1200的数据。当我ep_codes在 IN 子句中包含超过 1000 个时，Oracle 说我不允许这样做。为了克服这个问题，我尝试将 SQL 代码更改如下：

SELECT period, ...
FROM   my_view
WHERE  period = '200912'
       ...
       AND ep_codes IN (...1000 ep_codes...)
       OR  ep_codes IN (...200 ep_codes...)

The code was executed succesfully but the results are strange (calculation results are fetched for all periods, not just for 200912, which is not what I want). Is it appropriate to do that using ORbetween INclauses or should I execute two separate codes as one with 1000 and the other with 200 ep_codes?

代码执行成功，但结果很奇怪（所有时间段的计算结果都取了，不仅仅是200912，这不是我想要的）。OR在IN子句之间使用这样做是否合适，或者我应该执行两个单独的代码，一个是 1000，另一个是 200 ep_codes？

---

---

Pascal Martin's solution worked perfectly. Thanks all who contributed with valuable suggestions.

Pascal Martin 的解决方案非常有效。感谢所有提供宝贵建议的人。

回答by retronym

The recommended way to handle this in Oracle is to create a Temporary Table, write the values into this, and then join to this. Using dynamically created INclauses means the query optimizer does a 'hard parse' of every query.

在 Oracle 中处理此问题的推荐方法是创建一个临时表，将值写入此表，然后加入此表。使用动态创建的IN子句意味着查询优化器会对每个查询进行“硬解析”。

create global temporary table LOOKUP
(
    ID NUMBER
) on commit delete rows;

-- Do a batch insert from your application to populate this table
insert into lookup(id) values (?)

-- join to it
select foo from bar where code in (select id from lookup)

回答by Pascal MARTIN

Not sure that using so many values in a IN()is that good, actually -- especially for performances.

不确定在 a 中使用这么多值IN()是否真的那么好——尤其是对于表演。

When you say "the results are strange", maybe this is because a problem with parenthesis ? What if you try this, instead of what you proposed :

当你说“结果很奇怪”时，也许这是因为括号有问题？如果你尝试这个，而不是你提出的：

SELECT ...
FROM ...
WHERE ...
      AND (
          ep_codes IN (...1000 ep_codes...)
          OR  ep_codes IN (...200 ep_codes...)
      )

Does it make the results less strange?

它会使结果不那么奇怪吗？

回答by Scott Bailey

Actually you can use collections/multisets here. You'll need a number table type to store them.

实际上你可以在这里使用集合/多重集。您需要一个数字表类型来存储它们。

CREATE TYPE NUMBER_TABLE AS TABLE OF NUMBER;
...
SELECT *
FROM my_view
WHERE period MEMBER OF NUMBER_TABLE(1,2,3...10000)

Read more about multisets here:

在此处阅读有关多重集的更多信息：

回答by David Gelhar

Seems like it would be a better idea, both for performance and maintainability, to put the codes in a separate table.

对于性能和可维护性而言，将代码放在单独的表中似乎是一个更好的主意。

SELECT ...
FROM ...
WHERE ...
   AND ep_code in (select code from ep_code_table)

回答by ninesided

could you insert the 1200 ep_codevalues into a temporary table and then INNER JOINto that table to filter rows instead?

您可以将 1200 个ep_code值插入临时表，然后插入该INNER JOIN表以过滤行吗？

SELECT a.*
FROM mytable a
INNER JOIN tmp ON (tmp.ep_code = a.ep_code)
WHERE ...