在oracles嵌套表中最大行数是多少？

Question

CREATE TYPE nums_list AS TABLE OF NUMBER;

在oracle的嵌套表中，最大可能的行数是多少？

更新

CREATE TYPE nums_list  AS TABLE OF NUMBER;

CREATE OR REPLACE  FUNCTION  generate_series(from_n NUMBER, to_n NUMBER)
RETURN nums_list AS
ret_table nums_list := nums_list();
BEGIN

  FOR i IN from_n..to_n LOOP
    ret_table.EXTEND;
    ret_table(i) := i;
  END LOOP;
  RETURN ret_table;

END;


SELECT count(*)   FROM TABLE ( generate_series(1,4555555) );

这会导致错误：ORA-22813 operand value exceeds system limits, Object or Collection value was too large

Answer 1

使您可以在集合中包含2**31元素的31。这一限制从至少8.1.6开始就没有改变过，当然，未来可能会有所改变。

Answer 2

作为一个额外的观察，嵌套表本身不是太大，也不是使用太多内存。使用异常处理程序，您可以看到函数没有抛出错误。您可以在匿名块中填充相同的内容：

DECLARE
  ret_table nums_list := nums_list();
BEGIN
  FOR i IN 1..4555555 LOOP
    ret_table.EXTEND;
    ret_table(i) := i;
  END LOOP;
  dbms_output.put_line(ret_table.count);
END;
/

anonymous block completed
4555555

您也可以从块调用函数：

DECLARE
  ret_table nums_list;
BEGIN
  ret_table := generate_series(1,4555555);
  dbms_output.put_line(ret_table.count);
END;
/

anonymous block completed
4555555

只有当您将它用作表集合表达式时，才会得到一个错误：

SQL Error: ORA-22813: operand value exceeds system limits
22813. 00000 -  "operand value exceeds system limits"
*Cause:    Object or Collection value was too large. The size of the value
           might have exceeded 30k in a SORT context, or the size might be
           too big for available memory.
*Action:   Choose another value and retry the operation.

原因文本引用排序上下文，查询正在执行排序：

------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                 |     1 |     2 |    29   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE                    |                 |     1 |     2 |            |          |
|   2 |   COLLECTION ITERATOR PICKLER FETCH| GENERATE_SERIES |  8168 | 16336 |    29   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

正如@a_horse_with_no_name建议的那样，您可以通过将函数流水线化来避免这个问题：

CREATE OR REPLACE  FUNCTION  generate_series(from_n NUMBER, to_n NUMBER)
RETURN nums_list PIPELINED AS
BEGIN

  FOR i IN from_n..to_n LOOP
    PIPE ROW (i);
  END LOOP;
  RETURN;

END;
/

SELECT count(*)   FROM TABLE ( generate_series(1,4555555) );

  COUNT(*)
----------
   4555555 

这仍然是一个SORT AGGREGATE，但它似乎不再介意了。不太清楚在这两种情况下它为什么会这样做；也许其他人能够解释它在做什么。(顺便说一句，我是在一个11gR2实例中这样做的；我没有一个12c实例来验证行为是否相同，但是你的症状表明它是这样的)。或者不是排序上下文才是问题所在，也许是可用内存。在我的环境中，您的版本似乎一直工作到4177918元素--这似乎不是一个重要的数字，所以很可能与环境有关？

但这取决于您打算如何使用集合；从PL/SQL上下文来看，您的原始版本可能更合适。