`
mikixiyou
  • 浏览: 1098438 次
  • 性别: Icon_minigender_1
  • 来自: 南京
博客专栏
C3c8d188-c0ab-3396-821d-b68331e21226
Oracle管理和开发
浏览量:352968
社区版块
存档分类
最新评论

Oracle的pipelined函数提升数据输出性能

阅读更多

       从Oracle 8开始,我们就可以从一个collection类型的数据集合中查询出数据,这个集合称之为“虚拟表“。它的方法是“SELECT FROM TABLE(CAST(plsql_function AS collection_type))”,据说该方法在处理大数据量时会有内存方面的限制。到了Oracle 9i之后,一个称为pipelined表函数的技术被推出来。他和普通的表函数很类似,但还有有一些显著的差别。
第一,pipelined函数处理的数据,是以管道的方式,或者说是流的方式从预先准备的小数组中展现给用户,而普通表函数将数据全部准备好再展现给用户。
第二,pipelined函数可以并发,这意味着PLSQL可以同一时间在多个进程上执行。
第三,pipelined函数可以很容易将存储过程转换成用bulk操作的行,有利于实现的复杂转换逻辑的SQL。

 

(miki西游 @mikixiyou 原文链接: http://mikixiyou.iteye.com/blog/1628397 )

 

了解pipelined函数的最佳方法是看一个简单的例子。对于任何一个pipelined函数,都必须有两点要求。
1、一个定义在模式中或者包中collection类型;
2、一个单独的PL/SQL函数或一个包中的函数,函数的返回类型后面必须加pipelined关键字;


在下面的例子中,我们将创建一个简单的pipelined函数,输出若干行记录。首先需要一个collection类型,用于格式化输出。

CREATE OR REPLACE TYPE number_ntt AS TABLE OF NUMBER;
 


Oracle会使用这个类型缓存少量的记录作为pipelined函数调用时的输出。我们创建一个简单的pipelined函数。

CREATE OR REPLACE FUNCTION row_generator(rows_in IN PLS_INTEGER)
  RETURN number_ntt  PIPELINED
IS
BEGIN
  FOR i IN 1 .. rows_in LOOP
    PIPE ROW(i);
  END LOOP;
  RETURN;
END;
 

在这个SQL中:
在函数定义部分的关键字pipelined是pipelined函数定义的关键,返回的类型必须是事先定义的collection类型,如这里是number_tt。
在函数主体部分的”PIPE ROW”是将一个单行记录写入到collection流中。记录中所有字段的类型必须和collection类型中所有字段匹配。
在函数主体部分的“return“的值是一个空值,而不是有任何符合collection类的值。
这些就是pipelined函数定义时需要严格遵守的规则。

 


现在已经创建好一个pipelined函数,我们可以测试一下。

SQL> select * from TABLE( row_generator(10) );
 
COLUMN_VALUE
------------
           1
           2
           3
           4
           5
           6
           7
           8
           9
          10
 
10 rows selected
 


将pipelined函数row_generator放到一个“TABLE”操作符中,虚拟成一个数据源,类似表或视图。这里虚拟表只有一个字段,名称“COLUMN_VALUE“是其默认值。更复杂的输出则需要将collection定义得更复杂些,使用到object或者record。


我们通过一个例子比较一下pipelined函数或普通的表函数在返回collection时有何差异。


第一步,创建普通的表函数,返回colletion类型。

CREATE OR REPLACE FUNCTION table_function RETURN number_ntt AS
  nt number_ntt := number_ntt();
BEGIN
  FOR i IN 1 .. 500000 LOOP
    if (mod(i, 10000) = 0) then
      nt.EXTEND;
      nt(nt.LAST) := i;
    end if;
 
  END LOOP;
  RETURN nt; --<-- return whole collection
END table_function;

 


第二步,创建pipelined函数,返回的也是collection类型

CREATE OR REPLACE FUNCTION pipelined_function RETURN number_ntt
  PIPELINED AS
BEGIN
  FOR i IN 1 .. 500000 LOOP
    if (mod(i, 10000) = 0) then
      PIPE ROW(i); --<-- send row to consumer
    end if;
  END LOOP;
  RETURN;
END pipelined_function;
 



函数的功能都是将能和1000整除的数字输出出来。
再创建一个输出时间到毫秒的函数,用于测试两个函数的输出特点。

CREATE FUNCTION get_time RETURN TIMESTAMP IS
BEGIN
   RETURN LOCALTIMESTAMP;
END get_time;
/
 


第三步,测试两个函数


测试普通函数如下:

 

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'HH24:MI:SS.FF3';
SELECT get_time() AS ts FROM DUAL;
SELECT column_value, get_time() AS ts FROM TABLE(table_function);
SQL>
 
TS
--------------------------------------------------------------------------------
15:27:26.031
 
COLUMN_VALUE TS
------------ --------------------------------------------------------------------------------
      100000 15:27:26.218
      200000 15:27:26.218
      300000 15:27:26.218
      400000 15:27:26.218
      500000 15:27:26.218
 
SQL>

 

结果显示,所有记录都是同一时间输出。

 

 

测试pipelined函数如下:

 

SELECT get_time() AS ts FROM DUAL;
SELECT column_value, get_time() AS ts FROM TABLE(pipelined_function);
TS
--------------------------------------------------------------------------------
15:27:26.265
 
COLUMN_VALUE TS
------------ --------------------------------------------------------------------------------
      100000 15:27:26.312
      200000 15:27:26.343
      300000 15:27:26.390
      400000 15:27:26.421
      500000 15:27:26.453
 


 
结果显示,所有记录都是逐次输出。
这点对于用户的UI太重要了。试想,如果执行一个查询,过了10秒钟才显示出所有的结果好,还是还是每秒都显示一些记录,知道10秒钟显示完毕好?

如果这个输出的结果集再放到到百万记录,两个函数对PGA内存的消耗又完全不一样,这点更重要。

分享到:
评论

相关推荐

    oracle管道函数用法

    Oracle管道函数是一种特殊类型的函数,它能够返回一个数据集合,并且能够在函数执行的过程中逐步返回结果,而不仅仅是最后的结果。这种特性使得管道函数非常适合于处理大量数据或者需要逐步展示处理进度的场景。 ##...

    详解oracle管道函数的用法(一行拆为多行)

    如果需要在客户端实时的输出函数执行过程中的一些信息,在oracle9i以后可以使用管道函数(pipeline function)。 关键字PIPELINED表明这是一个oracle管道函数,oracle管道函数的返回值类型必须为集合 --创建一个集合...

    Oracle - In Database Map-Reduce

    通过使用Oracle数据库的Pipeline Table Functions,我们不仅能够实现在数据库内部的Map-Reduce处理,还能够充分利用Oracle数据库的强大功能和性能优势。这种方式不仅可以简化大数据处理的工作流程,还能显著提高处理...

    利用PB实现DBF文件到Oracle基表的数据转换.pdf

    第一种是通过DataWindow的import-file()函数,这种方法要求源和目标数据表格式一致,适用于源和目标表结构相同的场景。第二种是使用PipeLine对象,虽然效率较高,但也需要预先设定字段对应关系。 10. 实现方法:...

    Oracle数据库面试题及答案

    知识点:advisory statistics 是数据库中的一种统计数据,用于优化数据库的性能。 7. Audit trace 存放在哪个 oracle 目录结构中? 答案:unix $ORACLE_HOME/rdbms/audit Windows the event viewer 知识点:Audit ...

    Streamsets Data Collector管道详细配置文档(组件、事件、函数表达式、记录头属性)

    Streamsets Data Collector (SDC) 是一种高性能、可扩展的数据集成工具,用于构建可靠的数据管道。它支持多种数据源和目标,能够处理结构化与非结构化的数据。本文档旨在深入探讨SDC管道配置的各个方面,包括但不...

    orai18n.zip

    orai18n.jar文件很可能是Oracle为数据库提供的本地化资源和函数,包含了处理日期、时间、货币格式、排序规则等与地域文化相关的功能。 具体到"orai18n-19.3.0.0.jar"这个文件,版本号19.3.0.0暗示了这是Oracle ...

    数据库经常出的面试题

    这类函数通常用于需要动态生成数据源的场景,比如在数据管道(Pipeline)和ETL过程中非常有用。 - **应用场景**:例如,在ETL过程中,可能需要根据不同的条件动态生成不同的数据集,这时`TABLE Function`就可以派...

    北京中科信软PowerBuilder培训01基础

    7. 增强了库画板功能,网络功能提升,并提供对J2EE、***和XML的支持,能够开发Web应用程序,数据窗口组件新增对JavaScript的支持,可生成JSP应用。 二、PowerBuilder开发环境 1. 应用程序对象是应用程序的入口点。 ...

    pb7参考手册1_3

    这包括对更多类型数据库的支持,例如Oracle、SQL Server等,并且改进了数据访问的速度和稳定性。此外,新版还提供了更高级的数据绑定机制,从而简化了数据处理过程中的复杂度。 ##### 0.4 其它新颖特性 除了上述...

    Java:jdk1.8

    通过pipeline(流水线)操作,如filter、map、reduce等,可以方便地进行数据过滤、转换和聚合。 4. **默认方法**: 在接口中,JDK 1.8允许添加具有实现的方法,称为默认方法。这解决了接口升级时避免破坏已有实现...

    etlpy:基于 Python 的 ETL 工具

    **ETLpy:Python中的数据提取、转换与加载利器** ETLpy 是一个基于 Python 的高效数据处理库,专门用于执行数据集成过程中的提取...通过深入学习和实践 ETLpy,可以提升数据处理能力,更好地利用数据驱动业务决策。

    Netty5.0架构剖析和源码解读

    3. **Grizzly**:Oracle JavaFX项目的一部分,用于构建高性能的服务器端应用。 这些框架通过封装底层复杂的网络通信细节,提供了简单易用的API,使得开发者能够更加专注于业务逻辑的开发。 #### 2. NIO入门 #####...

    jdk-1.8-64.exe.zip

    JDK 8是Java的一个重要版本,它在2014年发布,引入了许多创新功能,极大地提升了开发者的工作效率和程序性能。对于移动端和后台开发,JDK 8都扮演着至关重要的角色。 1. **Java语言新特性**: - **Lambda表达式**...

    JDK 7确定B计划 部分特性延迟到JDK 8

    6. **安全性与加密技术**:增加了对TLS 1.2的支持,以及ECC(Elliptic Curve Cryptography)等现代加密算法的支持,提升了Java平台的安全性和数据保护能力。 7. **国际化与本地化**:增强了Unicode 6.0的支持,并...

    程序员考试刷题-ocp:指令

    程序员考试刷题OCP:Oracle Certified Professional Java SE 8 Programmer SE 8 II 这是一个基于 Eclipse 的项目,其中包含 OCP Oracle Certified Professional Java SE8 程序员 II (1Z0-809) 考试中涵盖的主要主题...

    jdk1.5 and tomcat5.5

    2. **更好的性能**:优化了内存管理和线程池,提升了服务器响应速度。 3. **安全性增强**:支持更强大的认证机制和权限管理。 4. **更好的管理界面**:提供了一个更直观的管理控制台,便于配置和管理应用。 5. **...

Global site tag (gtag.js) - Google Analytics