Oracle 关于函数表成本的问题 (....)

嗨，团队，
有一个名为SPLIT的函数，用于将我们的变量数据拆分为多行 (字段由 “|” 终止)，例如将11 | 22 | 33转换为3行: 11、22和33。当我们尝试使用JOIN查询该表时，费用高达131K，我们的客户认为我们应该优化SQL语句。

这是演示:

SQL> create or replace type type_split is table of varchar2(30000);  PLZ NOTICE THAT THE LENGTH IS 30000
  2  /

Type created.

SQL> create or replace function split
  2  (
  3     p_list varchar2,
  4     p_sep varchar2 := ','
  5  )  return type_split pipelined
  6   is
  7     l_idx  pls_integer;
  8     v_list  varchar2(4000) := p_list;
  9  begin
 10     loop
 11        l_idx := instr(v_list,p_sep);
 12        if l_idx > 0 then
 13            pipe row(substr(v_list,1,l_idx-1));
 14            v_list := substr(v_list,l_idx+length(p_sep));
 15        else
 16            pipe row(v_list);
 17            exit;
 18        end if;
 19     end loop;
 20     return;
 21  end split;
 22  /

Function created.

SQL> set linesize 160
SQL> SELECT SUM(SUM_QTY), MAX(SUM_QTY)
  2    FROM (SELECT A.EC_NAME, B.POSITION_NAME, SUM(C.ECQ_NAME) SUM_QTY
  3            FROM (SELECT ROWNUM EC_NUM, A.COLUMN_VALUE EC_NAME
  4                    FROM TABLE(SPLIT('11|22|33|44', '|')) A) A
  5            LEFT JOIN (SELECT ROWNUM POSITION_NUM, B.COLUMN_VALUE POSITION_NAME
  6                        FROM TABLE(SPLIT('11|22|33|44', '|')) B) B
  7              ON A.EC_NUM = B.POSITION_NUM
  8            LEFT JOIN (SELECT ROWNUM ECQ_NUM, C.COLUMN_VALUE ECQ_NAME
  9                        FROM TABLE(SPLIT('11|22|33|44', '|')) C) C
 10              ON A.EC_NUM = C.ECQ_NUM
 11           GROUP BY A.EC_NAME, B.POSITION_NAME);


Execution Plan
----------------------------------------------------------
Plan hash value: 2535028843

----------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |       |     1 |    13 |       |   131K  (2)| 00:00:06 |
|   1 |  SORT AGGREGATE                          |       |     1 |    13 |       |            |          |
|   2 |   VIEW                                   |       |    54M|   675M|       |   131K  (2)| 00:00:06 |
|   3 |    HASH GROUP BY                         |       |    54M|   306G|       |   131K  (2)| 00:00:06 |
|*  4 |     HASH JOIN RIGHT OUTER                |       |    54M|   306G|    15M|   130K  (1)| 00:00:06 |
|   5 |      VIEW                                |       |  8168 |    15M|       |    29   (0)| 00:00:01 |
|   6 |       COUNT                              |       |       |       |       |            |          |
|   7 |        COLLECTION ITERATOR PICKLER FETCH | SPLIT |  8168 | 16336 |       |    29   (0)| 00:00:01 |
|*  8 |      HASH JOIN OUTER                     |       |   667K|  2564M|    15M|  1627   (1)| 00:00:01 |
|   9 |       VIEW                               |       |  8168 |    15M|       |    29   (0)| 00:00:01 |
|  10 |        COUNT                             |       |       |       |       |            |          |
|  11 |         COLLECTION ITERATOR PICKLER FETCH| SPLIT |  8168 | 16336 |       |    29   (0)| 00:00:01 |
|  12 |       VIEW                               |       |  8168 |    15M|       |    29   (0)| 00:00:01 |
|  13 |        COUNT                             |       |       |       |       |            |          |
|  14 |         COLLECTION ITERATOR PICKLER FETCH| SPLIT |  8168 | 16336 |       |    29   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("A"."EC_NUM"="C"."ECQ_NUM"(+))
   8 - access("A"."EC_NUM"="B"."POSITION_NUM"(+))

Note
-----
   - 'PLAN_TABLE' is old version


Statistics
----------------------------------------------------------
        161  recursive calls
          0  db block gets
        377  consistent gets
          0  physical reads
          0  redo size
        624  bytes sent via SQL*Net to client
        607  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

这是我的问题:
1) oracle如何生成执行计划？表 (SPLIT()) 不是堆表，我猜没有直方图，表 (SPLIT()) 的结果是在执行期间常驻PGA (我发现事件 'PGA内存操作 ')。顺便说一句，如果我在这种情况下减少名为 “type_split” 的类型的长度 (例如从30000到3000)，则成本会以某种方式降低。
2) 自适应光标共享可用吗？
3) 在这种情况下，你们建议优化我们的查询吗？费用似乎让我很沮丧。

看看这个链接

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9449717100346245170

谈论这个问题。