Pipelined Table Functions

TABLE函数

table function是一个用户自定义的PL/SQL函数，它能返回一个结果集（关联数组、嵌套表），你能像查询普通表一样查询它，通过SELECT语句里的TABLE表达式

1	SELECT * FROM TABLE(table_function_name(parameter_list))

table函数能将行的集合作为入参，也就是说它的入参可以是嵌套表、数组或者游标等，因此table函数tf1的结果集可以作为tf2的入参，同理函数tf2的结果集也可以作为tf3的入参，以此类推。

由于传统的table function必须要填充完所有的数据以后才能返回结果，而结果又保存在内存当中，所以因为这个原因导致table function并不适合数据量较大的操作。

先构建一个table function例子

DROP TYPE t_tf_type;
DROP TYPE t_tf_obj;

CREATE TYPE t_tf_obj AS OBJECT (
  id           NUMBER,
  name  VARCHAR2(50)
);
/

CREATE TYPE t_tf_type IS TABLE OF t_tf_obj;
/

CREATE OR REPLACE FUNCTION get_tab (cnt IN NUMBER) RETURN t_tf_type AS
  l_tab  t_tf_type := t_tf_type();
BEGIN
  FOR i IN 1 .. cnt LOOP
    l_tab.extend;
    l_tab(l_tab.last) := t_tf_obj(i, 'Name is ' || i);
  END LOOP;

  RETURN l_tab;
END;
/

XB@ora12c> SELECT * FROM   TABLE(get_tab(10));

        ID NAME
---------- -------------------------
         1 Name is 1
         2 Name is 2
         3 Name is 3
         4 Name is 4
         5 Name is 5
         6 Name is 6
         7 Name is 7
         8 Name is 8
         9 Name is 9
        10 Name is 10

10 rows selected.

如果版本是12.2及以上，可以连关键字TABLE都省掉

XB@ora12c> SELECT * FROM   get_tab(10);

        ID NAME
---------- --------------------------------
         1 Name is 1
         2 Name is 2
         3 Name is 3
         4 Name is 4
         5 Name is 5
         6 Name is 6
         7 Name is 7
         8 Name is 8
         9 Name is 9
        10 Name is 10

10 rows selected.

Pipelined Table Functions

为了提高table function的性能，你可以

通过PARALLEL_ENABLE选项开启并行
通过oracle streams将表函数的结果集直接传给下一个进程
通过PIPELINED选项管道化函数结果

这篇重点说一下最后一个。

pipelined table function当获取到行时会立刻返回给调用它的进程，然后继续下一行数据的获取。因为不需要等到全部的数据集合获取完毕，所以能很大程度上的提高响应时间。同时也能节省内存，因为不需要缓存整个结果集对象。

pipelined table function包含PIPELINED子句，并调用PIPE ROW在函数的结果生成时就立刻返回,并不需要等数据构建完。

CREATE OR REPLACE FUNCTION get_tab_ptf (cnt IN NUMBER) RETURN t_tf_type PIPELINED AS
BEGIN
  FOR i IN 1 .. cnt LOOP
    PIPE ROW(t_tf_obj(i, 'Name is ' || i));   
  END LOOP;

  RETURN;
END;
/

XB@ora12c> SELECT * FROM   get_tab_ptf(10);

        ID NAME
---------- ------------------------------------------------------------------------------------------------------------------------------------------------------
         1 Name is 1
         2 Name is 2
         3 Name is 3
         4 Name is 4
         5 Name is 5
         6 Name is 6
         7 Name is 7
         8 Name is 8
         9 Name is 9
        10 Name is 10

10 rows selected.

当你在操作大数据量时，这个性能上的优势就会非常明显了。

NO_DATA_NEEDED异常

你必须理解NO_DATA_NEEDED异常的几种场景

包含有PIPE ROW语句的块中必须存在OTHERS异常处理
你输入的PIPE ROW代码伴随清理的过程

一般情况下，清理过程主要是用来释放那些代码不需要的资源。

如果pipelined table function函数返回的结果集超过了语句所需要的，pipelined table function函数的调用则会停止但是调用的语句却还在执行，如果这时有NO_DATA_NEEDED的异常处理过程，则能释放出不再需要的资源。

比如下面这个没有异常处理的例子

CREATE OR REPLACE FUNCTION get_tab_ptf (cnt IN NUMBER) RETURN t_tf_type PIPELINED AS
BEGIN
  FOR i IN 1 .. cnt LOOP
    PIPE ROW(t_tf_obj(i, 'Name is ' || i));   
  END LOOP;
  RETURN;
END;
/


Function created.

XB@ora12c> XB@ora12c> SELECT * FROM   get_tab_ptf(10) where rownum>=5;

        ID NAME
---------- ------------------------------------------------------------------------------------------------------------------------------------------------------
         1 Name is 1
         2 Name is 2
         3 Name is 3
         4 Name is 4
         5 Name is 5

看结果显示语句并没有报错提示，而且只返回了5行结果，如果加上异常处理的判断，是能获取到异常情况的

set serveroutput on

CREATE OR REPLACE FUNCTION get_tab_ptf (cnt IN NUMBER) RETURN t_tf_type PIPELINED AS
BEGIN
  FOR i IN 1 .. cnt LOOP
    PIPE ROW(t_tf_obj(i, 'Name is ' || i));   
  END LOOP;
  RETURN;
  EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.put_line('EXCEPTION');
    RAISE;
END;
/

XB@ora12c> SELECT * FROM   get_tab_ptf(10) where rownum>=5;

        ID NAME
---------- ------------------------------------------------------------------------------------------------------------------------------------------------------
         1 Name is 1
         2 Name is 2
         3 Name is 3
         4 Name is 4
         5 Name is 5

EXCEPTION

可以看到最后有EXCEPTION的输出，说明异常是真实存在的，只不过并没有显示的提醒。

指定获取NO_DATA_NEEDED的异常

CREATE OR REPLACE FUNCTION get_tab_ptf (cnt IN NUMBER) RETURN t_tf_type PIPELINED AS
BEGIN
  FOR i IN 1 .. cnt LOOP
    PIPE ROW(t_tf_obj(i, 'Name is ' || i));   
  END LOOP;
  RETURN;
  EXCEPTION
  WHEN NO_DATA_NEEDED THEN
    DBMS_OUTPUT.put_line('NO_DATA_NEEDED');
    RAISE;
  WHEN OTHERS THEN
    DBMS_OUTPUT.put_line('EXCEPTION');
    RAISE;
END;
/


Function created.

XB@ora12c> XB@ora12c> SELECT * FROM   get_tab_ptf(10) where rownum>=5;

        ID NAME
---------- ------------------------------------------------------------------------------------------------------------------------------------------------------
         1 Name is 1
         2 Name is 2
         3 Name is 3
         4 Name is 4
         5 Name is 5

NO_DATA_NEEDED

可以看到最后的结果表示异常确实是NO_DATA_NEEDED

内存使用情况比较

主要是要对比一下普通的表函数和pipelined表函数在内存的使用情况有什么区别，二者都是将数据缓存到pga内存里，所以直接比较session pga memory值的大小

因为需要在匿名块里计算，所以新建一个简单的函数

CREATE OR REPLACE FUNCTION get_stat RETURN NUMBER AS
  l_sum  NUMBER;
BEGIN
  SELECT a.value
  INTO   l_sum
  FROM   v$mystat a,
         v$statname b
  WHERE  a.statistic# = b.statistic#
  AND    b.name = 'session pga memory';
  RETURN l_sum;
END get_stat;
/

Function created.

首先看普通表函数

set serveroutput on;

declare
v_begin number;
v_end number;
BEGIN
  v_begin:=sys.get_stat;
  FOR cur IN (SELECT *
                  FROM   TABLE(get_tab(10000)))
  LOOP
    NULL;
 END LOOP;
 v_end:=sys.get_stat;
  DBMS_OUTPUT.put_line('Begin memory is: '||v_begin);
  DBMS_OUTPUT.put_line('End memory is: '||v_end);
  DBMS_OUTPUT.put_line('Total memory used: '||(v_end-v_begin));
END;
 16  /
Begin memory is: 4753336
End memory is: 9013176
Total memory used: 4259840

接下来换成pipelined table function

set serveroutput on;
declare
v_begin number;
v_end number;
BEGIN
  v_begin:=sys.get_stat;
  FOR cur IN (SELECT *
                  FROM   TABLE(get_tab_ptf(10000)))
  LOOP
    NULL;
 END LOOP;
 v_end:=sys.get_stat;
  DBMS_OUTPUT.put_line('Begin memory is: '||v_begin);
  DBMS_OUTPUT.put_line('End memory is: '||v_end);
  DBMS_OUTPUT.put_line('Total memory used: '||(v_end-v_begin));
END;
 16  /
Begin memory is: 4032440
End memory is: 4032440
Total memory used: 0

结果令人震惊，居然是0。重新试了多次结果依然都是0

这是由于pipelined table function不需要将全部结果集缓存到内存当中，所以数据在内存当中停留的时间非常短

Cardinality

如果你优化过table function的sql的话你会发现oracle估算其基数时总是不准的。主要取决于db block size，如果是默认的8k，那么估算的结果总是8196

XB@ora12c> 
SELECT *
  2  FROM   TABLE(get_tab_ptf(10));

        ID NAME
---------- ------------------------------------------------------------------------------------------------------------------------------------------------------
         1 Name is 1
         2 Name is 2
         3 Name is 3
         4 Name is 4
         5 Name is 5
         6 Name is 6
         7 Name is 7
         8 Name is 8
         9 Name is 9
        10 Name is 10

10 rows selected.

XB@ora12c> @x

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  2gpbj20w5kmbn, child number 0
-------------------------------------
SELECT * FROM   TABLE(get_tab_ptf(10))

Plan hash value: 822655197

---------------------------------------------------------------------------------------
| Id  | Operation                         | Name        | E-Rows |E-Bytes| Cost (%CPU)|
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |             |        |       |    29 (100)|
|   1 |  COLLECTION ITERATOR PICKLER FETCH| GET_TAB_PTF |   8168 | 16336 |    29   (0)|    >>>>====估算8168行
---------------------------------------------------------------------------------------

通常针对这种问题有几种处理办法

cardinality hint告诉优化器我会返回多少行
dynamica_sampling hint动态采样
Cardinality Feedback 就是sql实际执行完毕后会进行基数的修正，在之后的执行时会采用正确的基数。但是这个反馈值是存储在shared pool里，就面临到被age out的因素

动态采样是个不错的办法

XB@ora12c> alter system flush shared_pool;

System altered.

XB@ora12c> select /*+ DYNAMIC_SAMPLING(2)*/ * FROM   TABLE(get_tab_ptf(10));

        ID NAME
---------- ------------------------------------------------------------------------------------------------------------------------------------------------------
         1 Name is 1
         2 Name is 2
         3 Name is 3
         4 Name is 4
         5 Name is 5
         6 Name is 6
         7 Name is 7
         8 Name is 8
         9 Name is 9
        10 Name is 10

10 rows selected.

XB@ora12c> @x
Display execution plan for last statement for this session from library cache...

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  76sppwrcf832x, child number 0
-------------------------------------
select /*+ DYNAMIC_SAMPLING(2)*/ * FROM   TABLE(get_tab_ptf(10))

Plan hash value: 822655197

---------------------------------------------------------------------------------------
| Id  | Operation                         | Name        | E-Rows |E-Bytes| Cost (%CPU)|
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |             |        |       |    11 (100)|
|   1 |  COLLECTION ITERATOR PICKLER FETCH| GET_TAB_PTF |     10 |    20 |    11   (0)|          >>>>====估算的值就要准确多了
---------------------------------------------------------------------------------------

   - dynamic statistics used: dynamic sampling (level=2)


15 rows selected.

PL/SQL

Pipelined Table Functions

https://www.xbdba.com/2018/10/04/oracle-pipelined-table-function/

作者

xbdba

发布于

2018年10月4日

许可协议

Oracle Block Checksum 上一篇

11gR2 Clusterware and Grid Home – What You Need to Know 下一篇