最大的IN查询大小我们可以传递给一个查询是多少?

huangapple go评论81阅读模式
英文:

What is the max IN query size we can pass to a query?

问题

我正在寻找可以传递给Teradata SELECT查询中的IN操作符的最大大小。

在ORACLE中,IN查询最多接受1000个值,但在Teradata中,它接受超过5000个值。

性能问题:

当我在Teradata SELECT IN查询中执行超过1000个值时,需要大约30秒。

示例查询;
从Test中选择field1,feld2,field3,其中ID在(1,... 1000)中:

如果我在WHERE子句中添加更多筛选条件,是否会改善性能

示例查询;
从Test中选择field1,feld2,field3,其中createdDate = 日期,而ID在(1,... 1000)中:

注意:这个表中有很多数据,比如过去5年的数据。

谢谢,
Panneer

英文:

I am looking for the max size that we can pass to a Teradata SELECT query with IN operator.

In ORACLE, it accepts upto 1000 values in the IN Query but in Teradata it accepting more than 5000 values.

Performance issue:

When I execute more than 1000 values in the Teradata SELECT IN query, it is taking around 30 seconds.

Sample query;
Select field1,feld2, field3 from Test where ID IN (1,...1000):

Will it improve the performance if I add more filters in the WHERE clause

Like

Sample query;
Select field1,feld2, field3 from Test where createdDate=some date AND ID IN (1,...1000):

Note: This table has lot of data like last 5 years of data.

Thanks,
Panneer

答案1

得分: 2

关于性能,在尝试添加额外筛选条件时会发生什么?

英文:

There is no limit to the number of values in an IN clause, only a limit on the overall size of the query.

Regarding performance, what happens when you try adding extra filters?

huangapple
  • 本文由 发表于 2023年7月23日 19:20:14
  • 转载请务必保留本文链接:https://go.coder-hub.com/76747964.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定