Oracle-分组集合和流水线表函数(预期NUMBER得到ROW)

Question

我正在使用GROUPING SETS编写一个具有摘要逻辑的报告，但我收到了以下错误：

SELECT c1, c2, c3, SUM(c4) AS MySum
FROM TABLE(get_data()) src
GROUP BY GROUPING SETS ((c1, c2, c3), (c1, c2), c1, c2, ());
-------------------------
ORA-00932: inconsistent datatypes: expected NUMBER got XXX.MYROW
00932. 00000 -  "inconsistent datatypes: expected %s got %s"

当我只单独包括c1或c2时，它工作得很好：

GROUP BY GROUPING SETS ((c1, c2, c3), (c1, c2), c1, ());  
GROUP BY GROUPING SETS ((c1, c2, c3), (c1, c2), c2, ());

当我直接从t1表获取查询时，它也能很好地工作：

SELECT c1, c2, c3, SUM(c4) AS MySum
FROM t1 src
GROUP BY GROUPING SETS ((c1, c2, c3), (c1, c2), c1, c2, ());

我错过了什么？我觉得这很简单。下面是我设置的一个简化示例：

-- Base table
CREATE TABLE t1 (c1 VARCHAR(10), c2 VARCHAR(10), c3 VARCHAR(10), c4 INTEGER);
-- Row type
CREATE TYPE myrow AS OBJECT (c1 VARCHAR(10), c2 VARCHAR(10), c3 VARCHAR(10), c4 INTEGER);
-- Table type
CREATE OR REPLACE TYPE mytable AS TABLE OF myrow;
-- Get data function
CREATE OR REPLACE FUNCTION get_mydata
RETURN mytable PIPELINED AS
BEGIN
FOR v_rec IN (
SELECT c1, c2, c3, c4
FROM t1
) LOOP
PIPE ROW (myrow(v_Rec.c1, v_Rec.c2, v_Rec.c3, v_Rec.c4));
END LOOP;

RETURN;    
END;

DB版本-12.1.0

更新
我在实际功能中遇到的不同错误(即使有"具体化"提示(：

ORA-22905: cannot access rows from a non-nested table item 22905. 
00000  -  "cannot access rows from a non-nested table item" 
*Cause: attempt to access rows of an item whose type is not known 
at parse time or that is not of a nested table type 
*Action: use CAST to cast the item to a nested table type

Answer 1

我不知道为什么它不起作用，但是-看看这个解决方法是否有帮助(使用CTE和materialize提示(：

SQL> with test as
2    (select /*+ materialize */
3      c1, c2, c3, c4
4     from table(get_mydata()) src
5    )
6  select c1, c2, c3, sum(c4) as mysum
7  from test
8  group by grouping sets ((c1, c2, c3), (c1, c2), c1, c2, ());
C1         C2         C3              MYSUM
---------- ---------- ---------- ----------
1          2          3                   4
1                                         4
2                              4
4
1          2                              4
SQL>

Answer 2

您可以使用非流水线函数和BULK COLLECT:来解决错误

CREATE OR REPLACE FUNCTION get_mydata2
RETURN mytable AS
v_data mytable;
BEGIN
SELECT myrow(c1, c2, c3, c4)
BULK COLLECT
INTO v_data
FROM t1;
RETURN v_data;    
END;
/

我能够在19c版本中重现您的错误，所以看起来您发现了一个相对较大的Oracle错误。根据我的经验，流水线函数通常比常规函数更容易出错，而且往往会被过度使用。流水线函数可以更快地返回数据，但由于查询正在聚合所有行，因此无论如何都不会看到性能的提高。

非流水线函数方法的主要问题是，会话需要足够的内存来同时存储BULK COLLECT的所有结果。如果您必须处理数百万个宽行，那么这可能是不可行的。