DigOps

关于ORA-01795的简单分析

 数据库  1166  3 分钟

最近听开发反馈说跑某批量时报ORA-01795，很早以前是遇到过这个报错，但也没有深究，跟研发说这是Oracle限制，所以也没当回事。时隔几年再遇到时，我发现还是有必要去细致了解一下，并且能够有合理的应对方法。

1. 为什么会出现ORA-01795

ORA-01795

maximum number of expressions in a list is string

Cause

Number of expressions in the query exceeded the maximum limit. Note that unused column/expressions are also counted towards this limit.

Action

Reduce the number of expressions in the list and resubmit.

imgPrevious Releases

Release 21c, 19c, 18c, 12c, 11g

maximum number of expressions in a list is 1000

Cause

Number of expressions in the query exceeded than 1000. Note that unused column/expressions are also counted Maximum number of expressions that are allowed are 1000.

Action

Reduce the number of expressions in the list and resubmit.

Oracle MOS上文章说，当尝试打开一个超过1000个条目的[分项计分卡](a sub-item scorecard)时，就会触发这个报错。简单理解就是在一个表达式中清单条目超过1000时。

1

ORA-01795: maximum number of expressions in a list is 1000

这里举两个例子，如常见的in(1..1000)函数，包裹了1000个对象，那么就会触发。而between 1 and 1000，这种则不会。一定是枚举的情况。

先说说为什么会触发这个报错：从Oracle官方网站可知：Expression Lists (oracle.com)， list表达式不能超过1000个是Oracle内部一个限制，这并不是什么bug，也没有参数可调整。就像表的列值默认最大是1000列一样。

1

A comma-delimited list of expressions can contain no more than 1000 expressions. A comma-delimited list of sets of expressions can contain any number of sets, but each set can contain no more than 1000 expressions.

2. 处理超限的一些方法

Oracle MOS上也给了一些可规避的办法，如用OR、UNION ALL、comma-delimited list of sets或者子查询去等价转换。

  • OR:

    1

     expr  in ('expr1','expr2',...,'exprn') or expr  in ('exprn','exprn+1',...,'exprn+m')

  • UNION ALL:

    1
2
3

    select * from where expr  in ('expr1','expr2'...'exprn') 
union all
select * from where expr  in ('exprn','exprn+1'...'exprn+m');

  • comma-delimited list of sets:

    1

    (expra,exprb) in ((expra1,exprb1),(expra2,exprb2),...,(expran,exprbn));

    如果expra是一个常量，可以写做（1,exprb）in ((1,exprb1),(1,exprb2),...,(1,exprbn)). n可以超过1000，且exprb也会走索引。这个缺点就是SQL语句IN值越多，解析的时间会越长，可能会花费几百毫秒，同时也会造成短暂几秒的CPU攀升。

  • 子查询:

    1

    select ...from a where a1 in (select b1 from b);

对于表达式是一些可连续的number类型的，可以考虑用between ... and替代。

3. 一个特殊的ORA-01795

在MOS上看相关报错时，看到一篇关于分区表会引起ORA-01795的CASE：Simple Select For Partitioned Table Fails With ORA-01795 (Doc ID 2716140.1)。这是一篇分区表分区数超过1000的内存表会触发bug。我把相关信息摘录出来以备用。

APPLIES TO:

Oracle Database - Enterprise Edition - Version 11.2.0.4 to 12.1.0.2 [Release 11.2 to 12.1] Information in this document applies to any platform.

SYMPTOMS

1
2
3

A simple select (e.g. select * from table) for partitioned table fails with ORA-1795

 ORA-01795: "maximum number of expressions in a list is 1000"

CHANGES

none.

CAUSE

This issue was investigated by Bug:19670592 ORA-1795 ON QUERYING AN IN MEMORY TABLE WITH MORE THAN 1000 PARTITIONS and is a duplicate of

Bug:19910254 ORA-01795 FOR A QUERY WITH TABLE EXPANSION

SOLUTION

Apply interim Patch:19910254, if available for your platform and Oracle version. (If no patch exists for your version/platform, please contact Oracle Support).

OR

Upgrade to 12.2 onward

OR

Use workaround "_optimizer_table_expansion"=false

4. 一个扩展的知识点

目前可知，Oracle 23c已经将这个限制扩展为65535但官方文档并未更新。21c及以下版本统一都是1000的限制。

1
2

--23c 的报错信息：
ORA-01795: maximum number of expressions in a list is 65535

5. 参考文档：

Limit and conversion very long IN list : WHERE x IN ( ,,,... - Ask TOM (oracle.com)

Expression Lists (oracle.com)

Document 2770051.1 (oracle.com)

updatedupdated2024-01-302024-01-30

相关文章：