eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« Oracle Database 12c 新特性 - Pluggable Database(转) | Blog首页 | ACOUG 2012 Oracle技术嘉年华活动结束 »

警示:通过 wmsys.wm_concat 实现行列转换

在网上流传很多文章,描述了使用wmsys.wm_concat对象实现行列转换的方法,这种方法不被Oracle所推荐,因为WMSYS用户用于Workspace Manager,其函数对象可能因版本而不同,这种变化在11.2.0.3及10.2.0.5中体现出来。原本WM_CONCAT函数返回值为VARCHAR2变更为CLOB。这一变化导致了很多程序的异常。

该函数可以实现行列转换:
SQL> select wmsys.wm_concat(username) from dba_users;

WMSYS.WM_CONCAT(USERNAME)
--------------------------------------------------------------------------------
SYS,SYSTEM,YANGTK,TEST,OUTLN,MGMT_VIEW,FLOWS_FILES,MDSYS,ORDSYS,EXFSYS,DBSNMP,WM
在11.2.0.3中,其返回值类型变更为CLOB:
SQL> desc wmsys.wm_concat
FUNCTION wmsys.wm_concat RETURNS CLOB
 Argument Name Type In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P1 VARCHAR2 IN

Oracle建议用户使用自定义函数来实现该功能,而不是使用WorkSpace的这个内部函数。
这个函数包含一个Type、Type Body、Function,可以参考Oracle的实现方式来实现这个函数。

请注意,在将数据库从其他版本升级到10.2.0.5和11.2.0.3中时,必须注意到,这个函数的返回值类型变化。

例如如下一个系列的函数,可以帮助用户构建自有的行列转换函数:
SQL> create or replace TYPE en_concat_im
  2  AUTHID CURRENT_USER AS OBJECT
  3  (
  4    CURR_STR VARCHAR2(32767),
  5    STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT en_concat_im) RETURN NUMBER,
  6    MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT en_concat_im,
  7      P1 IN VARCHAR2) RETURN NUMBER,
  8    MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN en_concat_im,
  9        RETURNVALUE OUT VARCHAR2,
 10        FLAGS IN NUMBER)
 11    RETURN NUMBER,
 12    MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT en_concat_im,
 13   SCTX2 IN  en_concat_im) RETURN NUMBER
 14  );
 15  /

Type created.

SQL> 
SQL> create or replace TYPE BODY en_concat_im
  2  IS
  3    STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT en_concat_im)
  4    RETURN NUMBER
  5    IS
  6    BEGIN
  7   SCTX := en_concat_im(NULL) ;
  8   RETURN ODCICONST.SUCCESS;
  9    END;
 10    MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT en_concat_im,
 11         P1 IN VARCHAR2)
 12    RETURN NUMBER
 13    IS
 14    BEGIN
 15   IF(CURR_STR IS NOT NULL) THEN
 16     CURR_STR := CURR_STR || ';' || P1;
 17   ELSE
 18     CURR_STR := P1;
 19   END IF;
 20   RETURN ODCICONST.SUCCESS;
 21    END;
 22    MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN en_concat_im,
 23        RETURNVALUE OUT VARCHAR2,
 24        FLAGS IN NUMBER)
 25   RETURN NUMBER
 26    IS
 27    BEGIN
 28   RETURNVALUE := CURR_STR ;
 29   RETURN ODCICONST.SUCCESS;
 30    END;
 31    MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT en_concat_im,
 32   SCTX2 IN en_concat_im)
 33    RETURN NUMBER
 34    IS
 35    BEGIN
 36   IF(SCTX2.CURR_STR IS NOT NULL) THEN
 37     SELF.CURR_STR := SELF.CURR_STR || ';' || SCTX2.CURR_STR ;
 38   END IF;
 39   RETURN ODCICONST.SUCCESS;
 40    END;
 41  END;
 42  /

Type body created.


SQL> create or replace FUNCTION en_concat(P1 VARCHAR2) RETURN VARCHAR2 AGGREGATE USING en_concat_im ;
  2  /

Function created.

SQL> select en_concat(username) from dba_users;

EN_CONCAT(USERNAME)
--------------------------------------------------------------------------------
SYS;SYSTEM;YANGTK;TEST;OUTLN;MGMT_VIEW;FLOWS_FILES;MDSYS;ORDSYS;EXFSYS;DBSNMP;WM
SYS;APPQOSSYS;APEX_030200;OWBSYS_AUDIT;ORDDATA;CTXSYS;ANONYMOUS;SYSMAN;XDB;ORDPL
UGINS;OWBSYS;SI_INFORMTN_SCHEMA;OLAPSYS;ORACLE_OCM;XS$NULL;MDDATA;DIP;APEX_PUBLI
C_USER;SPATIAL_CSW_ADMIN_USR;SPATIAL_WFS_ADMIN_USR

以上改变还存在一个潜在的性能风险,那就是使用CLOB时会用到临时段,在高压力系统下,临时文件的大量占用会导致临时表空间的迅速增长。使用10046事件跟踪,可以清晰的看到这些变化,以下是使用10046事件跟踪两个查询:
SQL> alter session set events '10046 trace name context forever,level 12';

Session altered.

SQL> select wmsys.wm_concat(username) from dba_users;

WMSYS.WM_CONCAT(USERNAME)
--------------------------------------------------------------------------------
SYS,SYSTEM,YANGTK,TEST,OUTLN,MGMT_VIEW,FLOWS_FILES,MDSYS,ORDSYS,EXFSYS,DBSNMP,WM

SQL> select en_concat(username) from dba_users;

EN_CONCAT(USERNAME)
--------------------------------------------------------------------------------
SYS;SYSTEM;YANGTK;TEST;OUTLN;MGMT_VIEW;FLOWS_FILES;MDSYS;ORDSYS;EXFSYS;DBSNMP;WM
SYS;APPQOSSYS;APEX_030200;OWBSYS_AUDIT;ORDDATA;CTXSYS;ANONYMOUS;SYSMAN;XDB;ORDPL
UGINS;OWBSYS;SI_INFORMTN_SCHEMA;OLAPSYS;ORACLE_OCM;XS$NULL;MDDATA;DIP;APEX_PUBLI
C_USER;SPATIAL_CSW_ADMIN_USR;SPATIAL_WFS_ADMIN_USR
查询日志输出如下,其中Disk file operations I/O 操作了文件201,即临时文件:
=====================
PARSING IN CURSOR #139839047786312 len=47 dep=0 uid=0 oct=3 lid=0 tim=1346138299723909 hv=2250098298 ad='b855e660' sqlid='75n6afa31vjmu'
select wmsys.wm_concat(username) from dba_users
END OF STMT
PARSE #139839047786312:c=12999,e=12924,p=0,cr=107,cu=0,mis=1,r=0,dep=0,og=1,plh=4128086143,tim=1346138299723908
EXEC #139839047786312:c=0,e=68,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4128086143,tim=1346138299724042
WAIT #139839047786312: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=539 tim=1346138299724094
WAIT #139839047786312: nam='SQL*Net message from client' ela= 126 driver id=1650815232 #bytes=1 p3=0 obj#=539 tim=1346138299724260
WAIT #139839047786312: nam='Disk file operations I/O' ela= 41 FileOperation=2 fileno=201 filetype=2 obj#=539 tim=1346138299726980
WAIT #139839047786312: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=539 tim=1346138299727257
FETCH #139839047786312:c=2999,e=3001,p=0,cr=28,cu=17,mis=0,r=1,dep=0,og=1,plh=4128086143,tim=1346138299727281
STAT #139839047786312 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=28 pr=0 pw=0 time=2996 us)'
STAT #139839047786312 id=2 cnt=31 pid=1 pos=1 obj=0 op='HASH JOIN  (cr=28 pr=0 pw=0 time=1903 us cost=22 size=1764 card=21)'
STAT #139839047786312 id=3 cnt=31 pid=2 pos=1 obj=0 op='HASH JOIN  (cr=26 pr=0 pw=0 time=1815 us cost=20 size=1701 card=21)'
STAT #139839047786312 id=4 cnt=31 pid=3 pos=1 obj=0 op='HASH JOIN  (cr=20 pr=0 pw=0 time=1622 us cost=16 size=1638 card=21)'
STAT #139839047786312 id=5 cnt=31 pid=4 pos=1 obj=0 op='HASH JOIN OUTER (cr=14 pr=0 pw=0 time=1442 us cost=13 size=1575 card=21)'
STAT #139839047786312 id=6 cnt=31 pid=5 pos=1 obj=0 op='HASH JOIN  (cr=12 pr=0 pw=0 time=810 us cost=10 size=966 card=21)'
STAT #139839047786312 id=7 cnt=31 pid=6 pos=1 obj=0 op='HASH JOIN  (cr=10 pr=0 pw=0 time=486 us cost=8 size=924 card=21)'
STAT #139839047786312 id=8 cnt=1 pid=7 pos=1 obj=0 op='MERGE JOIN CARTESIAN (cr=4 pr=0 pw=0 time=83 us cost=4 size=16 card=1)'
STAT #139839047786312 id=9 cnt=1 pid=8 pos=1 obj=281 op='TABLE ACCESS FULL PROFILE$ (cr=2 pr=0 pw=0 time=40 us cost=2 size=8 card=1)'
STAT #139839047786312 id=10 cnt=1 pid=8 pos=2 obj=0 op='BUFFER SORT (cr=2 pr=0 pw=0 time=32 us cost=2 size=8 card=1)'
STAT #139839047786312 id=11 cnt=1 pid=10 pos=1 obj=281 op='TABLE ACCESS FULL PROFILE$ (cr=2 pr=0 pw=0 time=15 us cost=2 size=8 card=1)'
STAT #139839047786312 id=12 cnt=31 pid=7 pos=2 obj=22 op='TABLE ACCESS FULL USER$ (cr=6 pr=0 pw=0 time=41 us cost=3 size=784 card=28)'
STAT #139839047786312 id=13 cnt=1 pid=6 pos=2 obj=282 op='TABLE ACCESS FULL PROFNAME$ (cr=2 pr=0 pw=0 time=11 us cost=2 size=2 card=1)'
STAT #139839047786312 id=14 cnt=2 pid=5 pos=2 obj=298 op='TABLE ACCESS FULL RESOURCE_GROUP_MAPPING$ (cr=2 pr=0 pw=0 time=16 us cost=2 size=29 card=1)'
STAT #139839047786312 id=15 cnt=5 pid=4 pos=2 obj=16 op='TABLE ACCESS FULL TS$ (cr=6 pr=0 pw=0 time=80 us cost=3 size=15 card=5)'
STAT #139839047786312 id=16 cnt=5 pid=3 pos=2 obj=16 op='TABLE ACCESS FULL TS$ (cr=6 pr=0 pw=0 time=10 us cost=3 size=15 card=5)'
STAT #139839047786312 id=17 cnt=9 pid=2 pos=2 obj=293 op='TABLE ACCESS FULL USER_ASTATUS_MAP (cr=2 pr=0 pw=0 time=16 us cost=2 size=27 card=9)'
WAIT #139839047786312: nam='SQL*Net message from client' ela= 123 driver id=1650815232 #bytes=1 p3=0 obj#=539 tim=1346138299727940
WAIT #0: nam='SQL*Net message to client' ela= 0 driver id=1650815232 #bytes=1 p3=0 obj#=539 tim=1346138299727989
LOBREAD: c=0,e=36,p=0,cr=2,cu=0,tim=1346138299728000
WAIT #0: nam='SQL*Net message from client' ela= 156 driver id=1650815232 #bytes=1 p3=0 obj#=539 tim=1346138299728176
LOBTMPFRE: c=0,e=44,p=0,cr=0,cu=5,tim=1346138299728297
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=539 tim=1346138299728328
WAIT #0: nam='SQL*Net message from client' ela= 29 driver id=1650815232 #bytes=1 p3=0 obj#=539 tim=1346138299728367
FETCH #139839047786312:c=0,e=2,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=4128086143,tim=1346138299728385
WAIT #139839047786312: nam='SQL*Net message to client' ela= 0 driver id=1650815232 #bytes=1 p3=0 obj#=539 tim=1346138299728399

*** 2012-08-28 15:18:25.570
WAIT #139839047786312: nam='SQL*Net message from client' ela= 5842346 driver id=1650815232 #bytes=1 p3=0 obj#=539 tim=1346138305570755
CLOSE #139839047786312:c=0,e=11,dep=0,type=0,tim=1346138305570850
=====================
PARSING IN CURSOR #139839047799848 len=37 dep=1 uid=0 oct=3 lid=0 tim=1346138305571205 hv=1398610540 ad='c183e438' sqlid='grwydz59pu6mc'
select text from view$ where rowid=:1
END OF STMT
PARSE #139839047799848:c=0,e=24,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=1346138305571204
BINDS #139839047799848:
 Bind#0
  oacdty=11 mxl=16(16) mxlc=00 mal=00 scl=00 pre=00
  oacflg=18 fl2=0001 frm=00 csi=00 siz=16 off=0
  kxsbbbfp=7f2ed0d08410  bln=16  avl=16  flg=05
  value=00002294.0002.0001
EXEC #139839047799848:c=1000,e=56,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=1346138305571319
FETCH #139839047799848:c=0,e=19,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=3684871272,tim=1346138305571351
STAT #139839047799848 id=1 cnt=1 pid=0 pos=1 obj=69 op='TABLE ACCESS BY USER ROWID VIEW$ (cr=1 pr=0 pw=0 time=13 us cost=1 size=15 card=1)'
CLOSE #139839047799848:c=0,e=30,dep=1,type=0,tim=1346138305571394
=====================
PARSING IN CURSOR #139839047786312 len=41 dep=0 uid=0 oct=3 lid=0 tim=1346138305582448 hv=4228787766 ad='b8517a08' sqlid='75b6jnvy0wbjq'
select en_concat(username) from dba_users
END OF STMT
PARSE #139839047786312:c=10999,e=11557,p=0,cr=98,cu=0,mis=1,r=0,dep=0,og=1,plh=4128086143,tim=1346138305582447
EXEC #139839047786312:c=0,e=49,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4128086143,tim=1346138305582560
WAIT #139839047786312: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=539 tim=1346138305582592
FETCH #139839047786312:c=2000,e=2480,p=0,cr=28,cu=0,mis=0,r=1,dep=0,og=1,plh=4128086143,tim=1346138305585093
STAT #139839047786312 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=28 pr=0 pw=0 time=2493 us)'
STAT #139839047786312 id=2 cnt=31 pid=1 pos=1 obj=0 op='HASH JOIN  (cr=28 pr=0 pw=0 time=1979 us cost=22 size=1764 card=21)'
STAT #139839047786312 id=3 cnt=31 pid=2 pos=1 obj=0 op='HASH JOIN  (cr=26 pr=0 pw=0 time=1842 us cost=20 size=1701 card=21)'
STAT #139839047786312 id=4 cnt=31 pid=3 pos=1 obj=0 op='HASH JOIN  (cr=20 pr=0 pw=0 time=1669 us cost=16 size=1638 card=21)'
STAT #139839047786312 id=5 cnt=31 pid=4 pos=1 obj=0 op='HASH JOIN OUTER (cr=14 pr=0 pw=0 time=1517 us cost=13 size=1575 card=21)'
STAT #139839047786312 id=6 cnt=31 pid=5 pos=1 obj=0 op='HASH JOIN  (cr=12 pr=0 pw=0 time=839 us cost=10 size=966 card=21)'
STAT #139839047786312 id=7 cnt=31 pid=6 pos=1 obj=0 op='HASH JOIN  (cr=10 pr=0 pw=0 time=490 us cost=8 size=924 card=21)'
STAT #139839047786312 id=8 cnt=1 pid=7 pos=1 obj=0 op='MERGE JOIN CARTESIAN (cr=4 pr=0 pw=0 time=110 us cost=4 size=16 card=1)'
STAT #139839047786312 id=9 cnt=1 pid=8 pos=1 obj=281 op='TABLE ACCESS FULL PROFILE$ (cr=2 pr=0 pw=0 time=69 us cost=2 size=8 card=1)'
STAT #139839047786312 id=10 cnt=1 pid=8 pos=2 obj=0 op='BUFFER SORT (cr=2 pr=0 pw=0 time=35 us cost=2 size=8 card=1)'
STAT #139839047786312 id=11 cnt=1 pid=10 pos=1 obj=281 op='TABLE ACCESS FULL PROFILE$ (cr=2 pr=0 pw=0 time=17 us cost=2 size=8 card=1)'
STAT #139839047786312 id=12 cnt=31 pid=7 pos=2 obj=22 op='TABLE ACCESS FULL USER$ (cr=6 pr=0 pw=0 time=40 us cost=3 size=784 card=28)'
STAT #139839047786312 id=13 cnt=1 pid=6 pos=2 obj=282 op='TABLE ACCESS FULL PROFNAME$ (cr=2 pr=0 pw=0 time=9 us cost=2 size=2 card=1)'
STAT #139839047786312 id=14 cnt=2 pid=5 pos=2 obj=298 op='TABLE ACCESS FULL RESOURCE_GROUP_MAPPING$ (cr=2 pr=0 pw=0 time=14 us cost=2 size=29 card=1)'
STAT #139839047786312 id=15 cnt=5 pid=4 pos=2 obj=16 op='TABLE ACCESS FULL TS$ (cr=6 pr=0 pw=0 time=28 us cost=3 size=15 card=5)'
STAT #139839047786312 id=16 cnt=5 pid=3 pos=2 obj=16 op='TABLE ACCESS FULL TS$ (cr=6 pr=0 pw=0 time=13 us cost=3 size=15 card=5)'
STAT #139839047786312 id=17 cnt=9 pid=2 pos=2 obj=293 op='TABLE ACCESS FULL USER_ASTATUS_MAP (cr=2 pr=0 pw=0 time=8 us cost=2 size=27 card=9)'
WAIT #139839047786312: nam='SQL*Net message from client' ela= 193 driver id=1650815232 #bytes=1 p3=0 obj#=539 tim=1346138305585818
FETCH #139839047786312:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=4128086143,tim=1346138305585841
WAIT #139839047786312: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=539 tim=1346138305585855

*** 2012-08-28 15:18:34.648
WAIT #139839047786312: nam='SQL*Net message from client' ela= 9062490 driver id=1650815232 #bytes=1 p3=0 obj#=539 tim=1346138314648354
CLOSE #139839047786312:c=0,e=10,dep=0,type=0,tim=1346138314648443
=====================


以上问题在版本升级时需要注意。




历史上的今天...
    >> 2011-10-31文章:
    >> 2010-10-31文章:
    >> 2008-10-31文章:
    >> 2007-10-31文章:
    >> 2006-10-31文章:
    >> 2005-10-31文章:
    >> 2004-10-31文章:

无觅

By eygle on 2012-10-31 19:13 | Comments (0) | SQL.PLSQL | 3040 |


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com