- 浏览: 1086399 次
- 性别:
- 来自: 南京
博客专栏
-
Oracle管理和开发
浏览量:349442
最新评论
-
Simon.Ezer:
请问对于“如果非主键字段值发生改变,则不会同步过去”这种情况, ...
创建增量同步Oracle物化视图问题 -
dahai639:
挺好的,支持一下
Oracle的pipelined函数实现高性能大数据处理 -
zealotpz:
不错,原来是用户oracle 的所属组的问题
以sysdba身份登录oracle报ORA-1031权限不足错误之完美分析 -
mikixiyou:
zhangyuslam 写道如果担心全局索引失效,可以使用如下 ...
Oracle分区表的分区交互技术实现数据快速转移 -
sea0108:
...
Oracle sql loader使用速成
在plsql开发中,会涉及到一些大数据量表的数据处理,如将某记录数超亿的表的记录经过处理转换插入到另外一张或几张表。
常规的操作方法固然可以实现,但时间、磁盘IO、redo日志等等都非常大。Oracle 提供了一种高级函数,可以将这种数据处理的性能提升到极限。这种函数称为管道函数。
在实际项目中,管道函数会和表函数、数据流函数(即表函数和CURSOR结合)、数据集合、并行度一起使用,达到大数据处理的性能顶峰。
(miki西游 @mikixiyou 原文链接: http://mikixiyou.iteye.com/blog/1673672 )
下面是一个例子,将表t_ss_normal的记录插入到表t_target中,插入过程中有部分转换操作。
我分成四个方法来实现这个数据处理操作。
第一个方法,也是最常规的方法,代码如下:
create table T_SS_NORMAL ( owner VARCHAR2(30), object_name VARCHAR2(128), subobject_name VARCHAR2(30), object_id NUMBER, data_object_id NUMBER, object_type VARCHAR2(19), created DATE, last_ddl_time DATE, timestamp VARCHAR2(19), status VARCHAR2(7), temporary VARCHAR2(1), generated VARCHAR2(1), secondary VARCHAR2(1) ); / create table T_TARGET ( owner VARCHAR2(30), object_name VARCHAR2(128), comm VARCHAR2(10) );
这是源表和目标表的表结构。现在源表有200W条,其数据来自dba_objects视图。
create or replace package pkg_test is procedure load_target_normal; end pkg_test; create or replace package body pkg_test is procedure load_target_normal is begin insert into t_target (owner, object_name, comm) select owner, object_name, 'xxx' from t_ss_normal; commit; end; begin null; end pkg_test;
一个insert into select语句搞定这个数据处理,简单。
第二方法,采用管道函数实现这个数据处理。
create type obj_target as object( owner VARCHAR2(30), object_name VARCHAR2(128), comm varchar2(10) ); / create or replace type typ_array_target as table of obj_target; / create or replace package pkg_test is function pipe_target(p_source_data in sys_refcursor) return typ_array_target pipelined; procedure load_target; end pkg_test;
首先创建两个自定义的类型。obj_target的定义和t_target的表结构一致,用于存储每一条目标表记录。typ_array_target用于管道函数的返回值。
接着定义一个管道函数。
普通函数的结尾加一个pipelined关键字,就是管道函数。这个函数的返回参数类型为集合,这是为了使其能作为表函数使用。表函数就是在from子句中以table(v_resultset)调用的,v_resultset就是一个集合类型的参数。
最后定义一个调用存储过程。
在包体中定义该管道函数和调用存储过程。管道函数pipe_target的传入参数一个sys_refcursor类型。这是一个游标,可以理解为使用select * from table才能得到的结果集。
你也可以不用这个传入的游标,取而代之,在函数中定义一个游标,也一样使用。
function pipe_target(p_source_data in sys_refcursor) return typ_array_target pipelined is r_target_data obj_target := obj_target(null, null, null); r_source_data t_ss%rowtype; begin loop fetch p_source_data into r_source_data; exit when p_source_data%notfound; r_target_data.owner := r_source_data.owner; r_target_data.object_name := r_source_data.object_name; r_target_data.comm := 'xxx'; pipe row(r_target_data); end loop; close p_source_data; return; end; procedure load_target is begin insert into t_target (owner, object_name, comm) select owner, object_name, comm from table(pipe_target(cursor(select * from t_ss_normal))); commit; end;
关键字 pipe row 的作用是将obj_target插入到typ_array_target类型的数组中,管道函数自动返回这些数据。
因为源表的数据量会非常大,所以在fetch取值时会使用bulk collect ,实现批量取值。这样做可以减少plsql引擎和sql引擎的控制转换次数。这种转换称为上下文切换。
function pipe_target_array(p_source_data in sys_refcursor, p_limit_size in pls_integer default c_default_limit) return typ_array_target pipelined is r_target_data obj_target := obj_target(null, null, null); type typ_source_data is table of t_ss%rowtype index by pls_integer; aa_source_data typ_source_data; begin loop fetch p_source_data bulk collect into aa_source_data; exit when aa_source_data.count = 0; for i in 1 .. aa_source_data.count loop r_target_data.owner := aa_source_data(i).owner; r_target_data.object_name := aa_source_data(i).object_name; r_target_data.comm := 'xxx'; pipe row(r_target_data); end loop; end loop; close p_source_data; return; end; procedure load_target_array is begin insert into t_target (owner, object_name, comm) select owner, object_name, comm from table(pipe_target_array(cursor (select * from t_ss_normal), 100)); commit; end;
还可以使用并行度,使得管道函数可以多进程同时执行。并行度还有一个好处,就是将数据插入方式从常规路径转换为直接路径。直接路径可以大量减少redo日志的生成量。
function pipe_target_parallel(p_source_data in sys_refcursor, p_limit_size in pls_integer default c_default_limit) return typ_array_target pipelined parallel_enable(partition p_source_data by any) is r_target_data obj_target := obj_target(null, null, null); type typ_source_data is table of t_ss%rowtype index by pls_integer; aa_source_data typ_source_data; begin loop fetch p_source_data bulk collect into aa_source_data; exit when aa_source_data.count = 0; for i in 1 .. aa_source_data.count loop r_target_data.owner := aa_source_data(i).owner; r_target_data.object_name := aa_source_data(i).object_name; r_target_data.comm := 'xxx'; pipe row(r_target_data); end loop; end loop; close p_source_data; return; end; procedure load_target_parallel is begin execute immediate 'alter session enable parallel dml'; insert /*+parallel(t,4)*/ into t_target t (owner, object_name, comm) select owner, object_name, comm from table(pipe_target_array(cursor (select /*+parallel(s,4)*/ * from t_ss_normal s), 100)); commit; end;
在测试过程中,我测试200W记录的操作,时间从24秒降到到8秒,重做日志也降低更多。
在此数据处理操作中,涉及到集合(collection)、表函数、管道函数、流函数、bulk collect、游标等知识点。
PLSQL集合类型的使用总结
使用bulk collect insert实现大数据快速迁移
Oracle的pipelined函数提升数据输出性能
以前写的这三篇文章,都详细介绍了这些知识点,更多可以去参考oracle官方文档。
发表评论
-
Oracle分区表的分区交互技术实现数据快速转移
2013-01-22 11:48 14665有一个需求,将某业务表的某个时间点之前的记录转移到它的历史 ... -
等待事件enq TX row lock contention分析
2013-01-17 17:16 33207在Oracle数据库性能报告AWRRPT分析时,发现top ... -
Oracle的UNDO表空间管理总结
2013-01-14 15:06 12878UNDO是Oracle中的一个很 ... -
Oracle在不同windows系统中的迁移
2013-01-09 15:41 3700在Windows操作系统环境下 ... -
Oracle 10.2.0.1在windows 2008上安装失败经历
2013-01-07 10:29 8622这两天遇到这个一个项目上的数据库迁移的问题。原来的环境是数据库 ... -
所有的物化视图刷新脚本
2012-12-28 13:59 0select 'execute dbms_mview.refr ... -
Oracle数据库的SQL性能问题分析
2012-12-27 15:31 5225在Oracle 10.2.0.4数据库中,有一个SQL执行缓慢 ... -
db block gets和consistent gets的分析
2012-12-26 18:09 0在Oracle的文档中有这样一段解释: db block g ... -
创建增量同步Oracle物化视图问题
2012-12-25 14:07 15179我们采用Oracle的物化视图增量刷新机制定时将数据库A上的某 ... -
查归档日志文件每小时生成量
2012-12-18 16:13 10721在O racle数据库中,通过v$archived_lo ... -
如何删掉临时表空间的文件
2012-12-18 15:40 0Unlike Oracle datafiles which m ... -
Oracle sql性能诊断暨event 10046和10053使用
2012-12-17 10:24 0早上em grid control监控显示数据库的负载增加,其 ... -
查LOB字段占用的空间大小
2012-12-13 16:00 4486查询Oracle表中LOB字段的占用空间大小。表中每一个LOB ... -
Oracle Data Guard的重做日志传输和应用状况监控
2012-12-04 14:05 4020这是一个用来监控Oracle Data Guard环境下从主库 ... -
Oracle sql loader使用速成
2012-11-30 14:14 4827Oracle SQL LOADER是Oracle的 ... -
Oracle 11g2的监听器配置
2012-11-29 14:34 4308Oracle的监听器服务注册 ... -
Deleting archivelog on physical standby with RMAN in Oracle 10g
2012-11-28 13:25 0Turns out to be quite easy ... -
to_char将number转成string的小技巧
2012-11-27 14:14 10565很多数据转换处理操作时,会遇到将0.007007040000转 ... -
to_date转成字符串时ORA-01843 not a valid month 问题分析
2012-11-26 16:38 29138(注,本文三度易稿) 在开发Oracle SQL或PL/SQL ... -
Data Guard 10g 的保护级别为RESYNCHRONIZATION问题
2012-11-25 11:01 1874Oracle的data guard创建完成 ...
相关推荐
zeromq的parallel-pipeline并行处理模式的jave实现,Eclipse下的maven工程,相关引用已在pom文件引入,可以直接运行。
如果需要在客户端实时的输出函数执行过程中的一些信息,在oracle9i以后可以使用管道函数(pipeline function)。 关键字PIPELINED表明这是一个oracle管道函数,oracle管道函数的返回值类型必须为集合 --创建一个集合...
Jenkins高级篇之Pipeline技巧篇-2-如何处理多个参数化变量.rar
管道模式的python实现,包括配置文件的解析,使用networkx进行processor的管理等
Doris PipeLine的设计文档.pdf
同时 Pipeline 为一组函数,和普通 Hooks 的区别在于,Pipeline 整个流程中的函数数据可以相互传递,实现工业流水线一样的效果。这种设计模式,可以使得开发者的自定义函数更加模块化,便于管理。Authing Pipeline ...
pipeline engineering
构建机器学习Pipeline,Architecting a Machine Learning Pipeline 。
赛诺菲 Pipeline.pdf
强生 Pipeline.pdf
高通QCOM camera Pipeline可视化工具 1.4版本
Jenkins高级篇之Pipeline技巧篇-3-JSON文件处理多个参数进一步优化.rar
Jenkins高级篇之Pipeline技巧篇-1-小白搭建Pipeline项目开发环境.rar
HTML::Pipeline - HTML处理过滤器和工具类
OpenCV图像拼接课件,包括可编辑ppt,示例图片、源程序
方便gltf-pipeline相关人员下载使用
pipeline ADC的设计指南
主要介绍了scrapy自定义pipeline类实现将采集数据保存到mongodb的方法,涉及scrapy采集及操作mongodb数据库的技巧,具有一定参考借鉴价值,需要的朋友可以参考下
digital high speed pipeline ADC
This book describes the Direct3D graphics pipeline, from presentation of scene data to pixels appearing on the screen. The book is organized sequentially following the data °ow through the pipeline ...