程序博客网 > mysql数据库去重复

oracle 高效处理数据

来源：互联网 发布：mysql数据库去重复 编辑：程序博客网 时间：2024/05/29 12:26

Oracle的pipelined函数实现高性能大数据处理

在plsql开发中，会涉及到一些大数据量表的数据处理，如将某记录数超亿的表的记录经过处理转换插入到另外一张或几张表。

常规的操作方法固然可以实现，但时间、磁盘IO、redo日志等等都非常大。Oracle 提供了一种高级函数，可以将这种数据处理的性能提升到极限。这种函数称为管道函数。

在实际项目中，管道函数会和表函数、数据流函数（即表函数和CURSOR结合）、数据集合、并行度一起使用，达到大数据处理的性能顶峰。

 

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

 

下面是一个例子，将表t_ss_normal的记录插入到表t_target中，插入过程中有部分转换操作。

我分成四个方法来实现这个数据处理操作。

 

第一个方法，也是最常规的方法，代码如下：

 

Sql代码 复制代码 收藏代码
  1. create table T_SS_NORMAL   
  2. (   
  3.   owner          VARCHAR2(30),   
  4.   object_name    VARCHAR2(128),   
  5.   subobject_name VARCHAR2(30),   
  6.   object_id      NUMBER,   
  7.   data_object_id NUMBER,   
  8.   object_type    VARCHAR2(19),   
  9.   created        DATE,   
  10.   last_ddl_time  DATE,   
  11.   timestamp      VARCHAR2(19),   
  12.   status         VARCHAR2(7),   
  13.   temporary      VARCHAR2(1),   
  14.   generated      VARCHAR2(1),   
  15.   secondary      VARCHAR2(1)   
  16. );   
  17. /   
  18.   
  19. create table T_TARGET   
  20. (   
  21.   owner       VARCHAR2(30),   
  22.   object_name VARCHAR2(128),   
  23.   comm        VARCHAR2(10)   
  24. );  
 

这是源表和目标表的表结构。现在源表有200W条，其数据来自dba_objects视图。

 

Sql代码 复制代码 收藏代码
  1. create or replace package pkg_test is  
  2.   procedure load_target_normal;   
  3. end pkg_test;   
  4.   
  5. create or replace package body pkg_test is  
  6.   procedure load_target_normal is  
  7.   begin     
  8.     insert into t_target (owner, object_name, comm)   
  9.       select owner, object_name, 'xxx' from t_ss_normal;     
  10.     commit;     
  11.   end;   
  12. begin  
  13.   null;   
  14. end pkg_test;   
 

 

一个insert into select语句搞定这个数据处理，简单。

第二方法，采用管道函数实现这个数据处理。

 

Sql代码 复制代码 收藏代码
  1. create type obj_target as object(   
  2. owner VARCHAR2(30), object_name VARCHAR2(128), comm varchar2(10)   
  3. );   
  4. /   
  5. create or replace type typ_array_target as table of obj_target;   
  6. /   
  7.   
  8. create or replace package pkg_test is  
  9.   
  10.   function pipe_target(p_source_data in sys_refcursor) return typ_array_target   
  11.     pipelined;   
  12.   
  13.   procedure load_target;   
  14. end pkg_test;  

 

首先创建两个自定义的类型。obj_target的定义和t_target的表结构一致，用于存储每一条目标表记录。typ_array_target用于管道函数的返回值。

接着定义一个管道函数。

普通函数的结尾加一个pipelined关键字，就是管道函数。这个函数的返回参数类型为集合，这是为了使其能作为表函数使用。表函数就是在from子句中以table(v_resultset)调用的，v_resultset就是一个集合类型的参数。

最后定义一个调用存储过程。

 

在包体中定义该管道函数和调用存储过程。管道函数pipe_target的传入参数一个sys_refcursor类型。这是一个游标，可以理解为使用select * from table才能得到的结果集。

你也可以不用这个传入的游标，取而代之，在函数中定义一个游标，也一样使用。

 

Sql代码 复制代码 收藏代码
  1.   function pipe_target(p_source_data in sys_refcursor) return typ_array_target   
  2.     pipelined is  
  3.     r_target_data obj_target := obj_target(null, null, null);   
  4.     r_source_data t_ss%rowtype;   
  5.   
  6.  begin  
  7.      
  8.     loop   
  9.       fetch p_source_data   
  10.         into r_source_data;   
  11.       exit when p_source_data%notfound;       
  12.          
  13.       r_target_data.owner       := r_source_data.owner;   
  14.       r_target_data.object_name := r_source_data.object_name;   
  15.       r_target_data.comm        := 'xxx';       
  16.       pipe row(r_target_data);   
  17.        
  18.     end loop;   
  19.      
  20.     close p_source_data;   
  21.     return;   
  22.      
  23.   end;   
  24.   
  25.   procedure load_target is  
  26.   begin     
  27.     insert into t_target   
  28.       (owner, object_name, comm)   
  29.       select owner, object_name, comm   
  30.         from table(pipe_target(cursor(select * from t_ss_normal)));     
  31.     commit;     
  32.   end;   
  33.    

 

关键字 pipe row 的作用是将obj_target插入到typ_array_target类型的数组中，管道函数自动返回这些数据。

 

因为源表的数据量会非常大，所以在fetch取值时会使用bulk collect ，实现批量取值。这样做可以减少plsql引擎和sql引擎的控制转换次数。这种转换称为上下文切换。

 

 

Sql代码 复制代码 收藏代码
  1. function pipe_target_array(p_source_data in sys_refcursor,   
  2.                            p_limit_size  in pls_integer default c_default_limit)   
  3.   return typ_array_target   
  4.   pipelined is     
  5.   r_target_data obj_target := obj_target(null, null, null);    
  6.       
  7.   type typ_source_data is table of t_ss%rowtype index by pls_integer;   
  8.   aa_source_data typ_source_data;   
  9.   
  10. begin  
  11.   
  12.   loop   
  13.     fetch p_source_data bulk collect   
  14.       into aa_source_data;   
  15.     exit when aa_source_data.count = 0;   
  16.      
  17.     for i in 1 .. aa_source_data.count loop   
  18.        
  19.       r_target_data.owner       := aa_source_data(i).owner;   
  20.       r_target_data.object_name := aa_source_data(i).object_name;   
  21.       r_target_data.comm        := 'xxx';   
  22.        
  23.       pipe row(r_target_data);   
  24.        
  25.     end loop;   
  26.      
  27.   end loop;   
  28.   
  29.   close p_source_data;   
  30.   return;   
  31.   
  32. end;   
  33.   
  34.   
  35. procedure load_target_array is  
  36. begin  
  37.   insert into t_target   
  38.     (owner, object_name, comm)   
  39.     select owner, object_name, comm   
  40.       from table(pipe_target_array(cursor (select * from t_ss_normal),   
  41.                                    100));     
  42.   commit;     
  43. end;  

 

还可以使用并行度，使得管道函数可以多进程同时执行。并行度还有一个好处，就是将数据插入方式从常规路径转换为直接路径。直接路径可以大量减少redo日志的生成量。

 

 

Sql代码 复制代码 收藏代码
  1. function pipe_target_parallel(p_source_data in sys_refcursor,   
  2.                               p_limit_size  in pls_integer default c_default_limit)   
  3.   return typ_array_target   
  4.   pipelined   
  5.   parallel_enable(partition p_source_data by any) is  
  6.   
  7.   r_target_data obj_target := obj_target(null, null, null);   
  8.   
  9.   type typ_source_data is table of t_ss%rowtype index by pls_integer;     
  10.   aa_source_data typ_source_data;   
  11.   
  12. begin     
  13.   loop   
  14.     fetch p_source_data bulk collect   
  15.       into aa_source_data;   
  16.     exit when aa_source_data.count = 0;       
  17.     for i in 1 .. aa_source_data.count loop         
  18.       r_target_data.owner       := aa_source_data(i).owner;   
  19.       r_target_data.object_name := aa_source_data(i).object_name;   
  20.       r_target_data.comm        := 'xxx';         
  21.       pipe row(r_target_data);         
  22.     end loop;       
  23.   end loop;     
  24.   close p_source_data;   
  25.   return;   
  26.   
  27. end;   
  28.   
  29.   
  30. procedure load_target_parallel is  
  31. begin  
  32.   execute immediate 'alter session enable parallel dml';     
  33.   insert /*+parallel(t,4)*/   
  34.   into t_target t   
  35.     (owner, object_name, comm)   
  36.     select owner, object_name, comm   
  37.       from table(pipe_target_array(cursor (select /*+parallel(s,4)*/   
  38.                                      *   
  39.                                       from t_ss_normal s),   
  40.                                    100));     
  41.   commit;   
  42. end;  

 

 

 

在测试过程中，我测试200W记录的操作，时间从24秒降到到8秒，重做日志也降低更多。

 

 

 

 

在此数据处理操作中，涉及到集合(collection)、表函数、管道函数、流函数、bulk collect、游标等知识点。

PLSQL集合类型的使用总结  

使用bulk collect insert实现大数据快速迁移

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

以前写的这三篇文章，都详细介绍了这些知识点，更多可以去参考oracle官方文档。

 

mysql数据库去重复 mysql数据库去重复
原创粉丝点击
热门IT博客
linux superviselinux supervise
mysql db表mysql db表
环比和同比的算法
穿衣搭配技巧软件
mac os 切换输入法
宿州安广网络客服电话
北京大数据电话公司
算法设计与分析基础
魅族pro5nfc软件
我的淘宝卖家版
java断点上传文件
js ajax complete
nessus默认端口
京东云服务器开放端口
自学书法 知乎
山东全球网络春晚
java私塾在线
navicat sql语句
买了大平层后悔了 知乎
mac杀毒软件推荐
热门问题 老师的惩罚 人脸识别 我在镇武司摸鱼那些年 重生之率土为王 我在大康的咸鱼生活 盘龙之生命进化 天生仙种 凡人之先天五行 春回大明朝 姑娘不必设防，我是瞎子 云存储管理 微软云存储 高速云存储 云存储客户端 云存储数据库 做云存储的公司 国产云存储 云存储申请 云存储优盘 照片云存储 网件云存储 学而思云学习 云教学习app 学习云 云学习 白月光她只想学习沉云香 学堂云3 江苏云学堂网络科技有限公司 学堂云答案公众号 安利云学堂下载 云学堂下载 学堂云pro下载 云学堂app下载 云学堂登陆 云学堂app 萃忆学堂 云职教 云学校 云安全 交通安全云课堂app下载 云安全中心 云闪付怎么样安全吗 安全云抱枕 山东省安全教育云平台 云钱包安全吗 山东安全教育云平台登录入口 云存储 安全 云安全软件 安全生产模拟考试云平台 叶安傅云深 陆云湛言小安

程序博客网，程序员的互联网技术博客家园。csdn论坛精品 msdn技术资料都在这里