2007年7月17日随笔档案 - 背着手扇扇子的人

2007年7月17日

有人问这样的sql该怎么实现：
表数据和结构
   CODE NAME    B01    S01    B02    S02
    1          张三       数学    80
    1          张三                            语文    75
    2          王五       数学    70
    2          王五
    3          李四       数学    50
    3          李四                           语文    88

希望查询出如下结果：
   CODE SUM_STR(NAME)    B01    SUM_STR(S01)    B02    SUM_STR(S02)
    1                张三                    数学                80           语文            75
    2                王五                    数学                70
    3                李四                    数学                50           语文             88
这个问题可以采用自定义的聚集函数来实现：

create or replace type strcat_type as object (

cat_string varchar2 ( 4000 ),

static function ODCIAggregateInitialize(cs_ctx In Out strcat_type) return number ,

member function ODCIAggregateIterate(self In Out strcat_type,value in varchar2 ) return

number ,

member function ODCIAggregateMerge(self In Out strcat_type,ctx2 In Out strcat_type)

return number ,

member function ODCIAggregateTerminate(self In Out strcat_type,returnValue Out

varchar2 ,flags in number ) return number

)

------------------------------------

create or replace type body strcat_type is

static function ODCIAggregateInitialize(cs_ctx IN OUT strcat_type) return number

begin

cs_ctx : = strcat_type( null );

return ODCIConst.Success;

end ;

member function ODCIAggregateIterate(self IN OUT strcat_type,

value IN varchar2 )

return number

begin

if self.cat_string is null then

self.cat_string : = value;

end if ;

return ODCIConst.Success;

end ;

member function ODCIAggregateTerminate(self IN Out strcat_type,

returnValue OUT varchar2 ,

flags IN number )

return number

begin

returnValue : = self.cat_string;

return ODCIConst.Success;

end ;

member function ODCIAggregateMerge(self IN OUT strcat_type,

ctx2 IN Out strcat_type)

return number

begin

       if self.cat_string is null then
                   self.cat_string := ctx2.cat_string;
          end if;

return ODCIConst.Success;

end ;

-------------------

CREATE OR REPLACE FUNCTION sum_str(input varchar2 )

RETURN varchar2

PARALLEL_ENABLE AGGREGATE USING strcat_type;

-------最后查询语句：

select code,sum_str(name), sum_str(b01) b01,sum_str(s01) ,sum_str(b02) b02,sum_str(s02)

from javaeye group by code order by code

posted @ 2009-01-05 21:55 kebo 阅读(967) | 评论 (4) | 编辑收藏

openlayers如何利用vector和style作出marker

定义标注的样式，这个决定标注显示的方式，必须定义好
1 $package("com.bct.map");
2 com.bct.map.EncoderMarkerStyle = {
3     'bigEncoder':{
4         graphicWidth:24,
5         graphicHeight : 24,
6         graphicXOffset : -12,
7         graphicYOffset : -24,
8         externalGraphic : "scripts/map/img/channel2.png"
9     },
10     'smallEncoder':{
11         graphicWidth:16,
12         graphicHeight : 16,
13         graphicXOffset : -8,
14         graphicYOffset : -16,
15         externalGraphic : "scripts/map/img/channel.gif"
16     },
17     'selectStyle':{
18         pointerEvents: "visiblePainted",
19         border:"border:25 outset #ff88ff",
20         cursor: "pointer",
21         graphicWidth:24,
22         graphicHeight : 24,
23         graphicXOffset : -12,
24         graphicYOffset : -24,
25         externalGraphic : "scripts/map/img/channel2.png"
26     },
27     styleMap: new OpenLayers.StyleMap({
28                     "select": new OpenLayers.Style({pointRadius: 24})
29     })
30 }

marker层，扩展vector层，通过point和style达到marker的效果

1 $package("com.bct.map");
 2 $import("com.bct.map.EncoderMarkerStyle");
 3 com.bct.map.MarkerVectorLayer = OpenLayers.Class(OpenLayers.Layer.Vector,{
 4 /**
 5 * parameters
 6 * attribute filer对象
 7 */
 8 getFeatureByAttribute :function(attributes){
 9 var feature = null;
10 for(var i=0;i<this.features.length; ++i){
11 var attri = this.features[i].attributes;
12 var find = false;
13 for(var j in attributes){
14 if(attributes[j] == attri[j]){
15 find = true;
16 }
17 }
18 if(find){
19 return this.features[i];
20 }
21 }
22
23 },
24 addEncorderFeature:function(encNode,location){
25 if(encNode&&this.repetitiveCheck(encNode.id)){
26 return;
27 }
28 var attributes = OpenLayers.Util.extend({}, encNode.attributes);
29 var enc_point = new OpenLayers.Geometry.Point(location.lon,location.lat);
30 var enc_Feature = new OpenLayers.Feature.Vector(enc_point,attributes,com.bct.map.EncoderMarkerStyle['smallEncoder']);
31 this.addFeatures([enc_Feature]);
32 if(encNode.attributes['lon']&&encNode.attributes['lat']&&encNode.attributes['lon'].length>0){
33 return;
34 }
35 this.updateChannel(encNode.id,location.lon,location.lat);
36 },
37 addDeptFeature:function(deptNode,location){
38 if(deptNode&&this.repetitiveCheck(deptNode.id)){
39 return;
40 }
41 var attributes = OpenLayers.Util.extend({}, deptNode.attributes);
42 var enc_point = new OpenLayers.Geometry.Point(location.lon,location.lat);
43 var enc_Feature = new OpenLayers.Feature.Vector(enc_point,attributes,com.bct.map.EncoderMarkerStyle['smallEncoder']);
44
45 this.addFeatures([enc_Feature]);
46
47 },
48 repetitiveCheck:function(entity_id){
49 if(this.getFeatureByAttribute({id:entity_id})){
50 return true;
51 }
52 return false;
53 },
54 updateChannel:function(channel_id,lon,lat){
55 Ext.Ajax.request({
56 url: 'deviceVideoEncoder.do?method=updateLonlat&id='+channel_id+"&lon="+lon+"&lat="+lat
57 });
58 },
59 channelMarkerClick:function() {
60 var features = this.selectedFeatures;
61 if(features.length >=0&&features[0]) {
62 feature = features[0];
63 var treeNodeAttribute = feature.attributes;
64 var vedioPopForm = new Ext.FormPanel({
65 frame:true,
66 labelAlign: 'top',
67 bodyStyle:'padding:5px',
68 width: 400,
69 height:200,
70 layout: 'fit',
71 items:[{
72 xtype:'fieldset',
73 title: '摄像头信息',
74 autoHeight:true,
75 autoWidth:true,
76 html:"名称:"+treeNodeAttribute['text']
77 +"通道号:"+treeNodeAttribute['channelNumber']
78 +"设备名称:"+treeNodeAttribute['deviceunitName']
79 +"所属部门:"+treeNodeAttribute['deptName']
80 +"经纬度:"+treeNodeAttribute['lon']+","+treeNodeAttribute['lat']
81 }]
82 });
83 var win = new Ext.Window({
84 width : 420,
85 height: 220,
86 items : vedioPopForm
87 });
88 win.show();
89 }
90 },
91 cartoonFeature :function(feature){
92 this.drawFeature(feature,com.bct.map.EncoderMarkerStyle['bigEncoder']);
93 var runner = new Ext.util.TaskRunner(1000);
94 var task = {
95 run:this.drawFeature,
96 scope:this,
97 args:[feature,com.bct.map.EncoderMarkerStyle['smallEncoder']],
98 interval: 1000
99 }
100 runner.start(task);
101 },
102 removeSelectFeature:function(){
103 var features = this.selectedFeatures;
104 for(var i=features.length-1; i>=0; i--) {
105 feature = features[i];
106 this.updateChannel(feature.attributes['id'],"","");
107 }
108 this.destroyFeatures(this.selectedFeatures);
109 },
110 monitorSelectFeature:function(){
111 var features = this.selectedFeatures;
112 if(features.length >=0&&features[0]) {
113 feature = features[0];
114 var treeNodeAttribute = feature.attributes;
115 var objId="mapAVShow"+treeNodeAttribute['id'];
116 var win = new Ext.Window({
117 width : 420,
118 height: 420,
119 html:"<div id='mapEncoder' width='100%' height='100%'><object width='100%' height='100%' id='"+objId+"' classid='clsid:574B47E8-A366-4AB9-B2EA-57F145CA3780'></object></div>"
120 });
121 win.show();
122 Ext.lib.Ajax.request('GET','channel.do?method=getSiteId&accept=json&id='+treeNodeAttribute['id'],
123 {success: function(o){
124 var encoderObj;
125 encoderObj=Ext.util.JSON.decode(o.responseText);
126 $import("com.bct.monitor.mapAVShow");
127 var avshowObj=document.getElementById(objId);
128 var avshow=new com.bct.monitor.mapAVShow(avshowObj,
129 encoderObj[0].siteId,encoderObj[0].enCoderId,encoderObj[0].diveceUnitTypeId,'');
130 avshow.startVideo();
131 win.on("destroy",function del(){
132 avshow.stopVideo();
133 });
134 }
135 });
136 }
137 }
138 });

posted @ 2008-09-04 14:12 kebo 阅读(3941) | 评论 (1) | 编辑收藏

一次sql调优历程

在项目进入性能测试阶段，终于爆发了sql运行缓慢，系统吞吐量下降，甚至一度出现oracle服务器cpu100%的情况。具体开发和测试人员报告情况，开始介入处理。

具体查找性能缓慢的过程略除。
发现一条sql运行缓慢。通过跟踪发现一下信息
select alias_p2.pendingid, alias_p2.workitemid, alias_p2.operationid, alias_p2.operationkey,

2 alias_p2.title, alias_p2.sendercn, alias_p2.operatedes, alias_p2.pendingstate,

3 alias_p2.parameter, alias_p2.createdate, alias_p2.deptname, alias_p2.completeddate ,

4 alias_p2.openstate , alias_p2.name, alias_p2.processinstanceid, alias_p2.asset

5 from ( select alias_p1.pendingid, alias_p1.workitemid, alias_p1.operationid,

6 alias_p1.operationkey, alias_p1.title, alias_p1.sendercn, alias_p1.operatedes,

7 alias_p1.pendingstate, alias_p1.parameter, alias_p1.createdate, alias_p1.deptname,

8 alias_p1.completeddate , alias_p1.openstate , alias_p1.name, alias_p1.processinstanceid ,

9 alias_p1.asset , rownum rn from(select alias_p.pendingid, alias_p.workitemid, alias_p.operationid,

10 alias_p.operationkey, alias_p.title, alias_p.sendercn, alias_p.operatedes, alias_p.pendingstate,

11 alias_p.parameter, alias_p.createdate, alias_p.deptname, alias_p.completeddate , alias_p.openstate ,

12 pd.name, w.processinstanceid , eam_db.concatassetname( alias_p.operationkey, alias_p.operationid )

13 asset from WF_Pending alias_p, WF_WorkItem w, WF_ProcessDefinition pd, WF_ProcessInstance pi

14 where alias_p.ownerid='qinxue' and alias_p.pendingstate in(0,3,5,7,9,10,11,12)

15 and (alias_p.deptname=' 审控部信息处 ' or alias_p.deptname='' or alias_p.deptname is null)

16 and w.workitemid = alias_p.workitemid and pi.processinstanceid = w.processinstanceid

17 and pi.completeddate is null and pd.processdefinitionid = w.processdefinitionid order by alias_p.createdate desc) alias_p1 where rownum <=10)

alias_p2 where rn>=1;

已选择 10 行。

执行计划

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=1 Bytes=2507

)

1 0 VIEW (Cost=10 Card=1 Bytes=2507)

2 1 COUNT (STOPKEY)

3 2 VIEW (Cost=10 Card=1 Bytes=2494)

4 3 SORT (ORDER BY STOPKEY) (Cost=10 Card=1 Bytes=167)

5 4 NESTED LOOPS (Cost=8 Card=1 Bytes=167)

6 5 NESTED LOOPS (Cost=7 Card=1 Bytes=162)

7 6 NESTED LOOPS (Cost=6 Card=1 Bytes=134)

8 7 TABLE ACCESS (FULL) OF 'WF_PENDING' (Cost=5

Card=1 Bytes=111)

9 7 TABLE ACCESS (BY INDEX ROWID) OF 'WF_WORKITE

M' (Cost=1 Card=3 Bytes=69)

10 9 INDEX (UNIQUE SCAN) OF 'SYS_C003694' (UNIQ

UE)

11 6 TABLE ACCESS (BY INDEX ROWID) OF 'WF_PROCESSDE

FINITION' (Cost=1 Card=1 Bytes=28)

12 11 INDEX (UNIQUE SCAN) OF 'SYS_C003684' (UNIQUE

)

13 5 TABLE ACCESS (BY INDEX ROWID) OF 'WF_PROCESSINST

ANCE' (Cost=1 Card=1 Bytes=5)

14 13 INDEX (UNIQUE SCAN) OF 'SYS_C003662' (UNIQUE)

统计信息

----------------------------------------------------------

314 recursive calls

0 db block gets

29433 consistent gets

0 physical reads

0 redo size

2153 bytes sent via SQL*Net to client

372 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

101 sorts (memory)

0 sorts (disk)

10 rows processed

其中一致读达到近3万次，关联调用出现314次。排序数值也非常多，显然第一目标是把这两个数据降下来。
通过进一步的分析。发现出现这些问题的主要原因是调用eam_db.concatassetname( alias_p.operationkey, alias_p.operationid )这个包。
开始考虑直接在sql外层做关联，不用function来实现。利用聚集函数来合并数据。
着手建立：

聚集函数： CREATE OR REPLACE FUNCTION F_ASSETLINK(P_STR VARCHAR2) RETURN VARCHAR2
AGGREGATE USING asset_link;

----------------------
创建type：CREATE OR REPLACE TYPE ASSET_LINK AS OBJECT (
STR VARCHAR2(30000),
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT ASSET_LINK) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT ASSET_LINK, VALUE IN VARCHAR2) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN ASSET_LINK, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT ASSET_LINK, CTX2 IN ASSET_LINK) RETURN NUMBER
)
------------------------------------------------------

创建type body：CREATE OR REPLACE TYPE BODY ASSET_LINK IS
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT ASSET_LINK) RETURN NUMBER IS
BEGIN
SCTX := ASSET_LINK(NULL);
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT ASSET_LINK, VALUE IN VARCHAR2) RETURN NUMBER IS
BEGIN
SELF.STR := SELF.STR ||','|| VALUE;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN ASSET_LINK, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER IS
BEGIN
RETURNVALUE := SELF.STR;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT ASSET_LINK, CTX2 IN ASSET_LINK) RETURN NUMBER IS
BEGIN
NULL;
RETURN ODCICONST.SUCCESS;
END;
END;
调整sql如下：
select alias_p.pendingid, alias_p.workitemid, alias_p.operationid,
   alias_p.operationkey, alias_p.title, alias_p.sendercn, alias_p.operatedes, alias_p.pendingstate,
   alias_p.parameter, alias_p.createdate, alias_p.deptname, alias_p.completeddate , alias_p.openstate ,
   pd.name, w.processinstanceid
   --,T.ASSETCLASS3 ASSET
   ,f_assetlink(d3.typename) ASSET
   --,eam_db.concatassetname( alias_p.operationkey, alias_p.operationid ) asset
   from WF_Pending alias_p, WF_WorkItem w,
   WF_ProcessDefinition pd, WF_ProcessInstance pi
   , tb_asset_dizhiyihao T,dic_app_wfconfig wfc,dic_app_assettype3 d3
   where alias_p.ownerid='qinxue'
   and alias_p.pendingstate in(0,3,5,7,9,10,11,12)
   and (alias_p.deptname='审控部信息处' or alias_p.deptname='' or alias_p.deptname is null)
   and w.workitemid = alias_p.workitemid
   and pi.processinstanceid = w.processinstanceid
   and pi.completeddate is null
   and pd.processdefinitionid = w.processdefinitionid
   AND    t.pk_businessid = alias_p.operationid
          and alias_p.operationkey = wfc.memo_1
          and wfc.wfconfig_code = t.wfconfig_code
   and t.assetclass3 = d3.assettype3_id
   group by alias_p.pendingid, alias_p.workitemid, alias_p.operationid,
   alias_p.operationkey, alias_p.title, alias_p.sendercn, alias_p.operatedes, alias_p.pendingstate,
   alias_p.parameter, alias_p.createdate, alias_p.deptname, alias_p.completeddate , alias_p.openstate ,
   pd.name, w.processinstanceid
   order by alias_p.createdate desc
得到统计数据如下：
C:\Documents and Settings\ibm>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 9月 10 19:27:33 2007

SQL> conn jic/jic@name
已连接。
SQL> set autotrace traceonly
SQL> select alias_p.pendingid, alias_p.workitemid, alias_p.operationid,
2     alias_p.operationkey, alias_p.title, alias_p.sendercn, alias_p.operatedes, alias_p.pendingstate,
3     alias_p.parameter, alias_p.createdate, alias_p.deptname, alias_p.completeddate , alias_p.openstate ,
4     pd.name, w.processinstanceid
5     --,T.ASSETCLASS3 ASSET
6     ,f_assetlink(d3.typename) ASSET
7     --,eam_db.concatassetname( alias_p.operationkey, alias_p.operationid ) asset
8     from WF_Pending alias_p, WF_WorkItem w,
9     WF_ProcessDefinition pd, WF_ProcessInstance pi
10     , tb_asset_dizhiyihao T,dic_app_wfconfig wfc,dic_app_assettype3 d3
11     where alias_p.ownerid='qinxue'
12     and alias_p.pendingstate in(0,3,5,7,9,10,11,12)
13     and (alias_p.deptname='审控部信息处' or alias_p.deptname='' or alias_p.deptname is null)
14     and w.workitemid = alias_p.workitemid
15     and pi.processinstanceid = w.processinstanceid
16     and pi.completeddate is null
17     and pd.processdefinitionid = w.processdefinitionid
18     AND    t.pk_businessid = alias_p.operationid
19            and alias_p.operationkey = wfc.memo_1
20            and wfc.wfconfig_code = t.wfconfig_code
21     and t.assetclass3 = d3.assettype3_id
22     group by alias_p.pendingid, alias_p.workitemid, alias_p.operationid,
23     alias_p.operationkey, alias_p.title, alias_p.sendercn, alias_p.operatedes, alias_p.pendingstate,
24     alias_p.parameter, alias_p.createdate, alias_p.deptname, alias_p.completeddate , alias_p.openstate ,
25     pd.name, w.processinstanceid
26     order by alias_p.createdate desc;

已选择30行。

执行计划
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=19 Card=1 Bytes=205)
   1    0   SORT (GROUP BY) (Cost=19 Card=1 Bytes=205)
   2    1     NESTED LOOPS (Cost=17 Card=1 Bytes=205)
   3    2       HASH JOIN (Cost=16 Card=1 Bytes=191)
   4    3         HASH JOIN (Cost=11 Card=1 Bytes=183)
   5    4           NESTED LOOPS (Cost=8 Card=1 Bytes=167)
   6    5             NESTED LOOPS (Cost=7 Card=1 Bytes=139)
   7    6               NESTED LOOPS (Cost=6 Card=1 Bytes=134)
   8    7                 TABLE ACCESS (FULL) OF 'WF_PENDING' (Cost=5
          Card=1 Bytes=111)

9 7 TABLE ACCESS (BY INDEX ROWID) OF 'WF_WORKITE
M' (Cost=1 Card=1 Bytes=23)

10 9 INDEX (UNIQUE SCAN) OF 'SYS_C004347' (UNIQ
UE)

11 6 TABLE ACCESS (BY INDEX ROWID) OF 'WF_PROCESSIN
STANCE' (Cost=1 Card=1 Bytes=5)

12 11 INDEX (UNIQUE SCAN) OF 'SYS_C004334' (UNIQUE
)

13 5 TABLE ACCESS (BY INDEX ROWID) OF 'WF_PROCESSDEFI
NITION' (Cost=1 Card=1 Bytes=28)

14   13               INDEX (UNIQUE SCAN) OF 'SYS_C004329' (UNIQUE)
15    4           TABLE ACCESS (FULL) OF 'DIC_APP_WFCONFIG' (Cost=2
          Card=24 Bytes=384)

16 3 TABLE ACCESS (FULL) OF 'TB_ASSET_DIZHIYIHAO' (Cost=4
Card=310 Bytes=2480)

17 2 TABLE ACCESS (BY INDEX ROWID) OF 'DIC_APP_ASSETTYPE3'
(Cost=1 Card=1 Bytes=14)

18 17 INDEX (UNIQUE SCAN) OF 'PK_DIC_APP_ASSETTYPE3' (UNIQ
UE)

统计信息
----------------------------------------------------------
          6 recursive calls
          0 db block gets
        847 consistent gets
          0 physical reads
          0 redo size
       4102 bytes sent via SQL*Net to client
        383 bytes received via SQL*Net from client
          3 SQL*Net roundtrips to/from client
          1 sorts (memory)
          0 sorts (disk)

其中排序由101变为1次
一致读降为847。下降非常客观
关联调用仅有6次。
此sql性能优化非常可观。至此优化结束：)

posted @ 2007-09-10 19:35 kebo 阅读(461) | 评论 (0) | 编辑收藏

利用mv做跨

数据库迁移过程中需要目标数据库和原数据库结构相同和数据是最新。
为了保持最新数据和快速切换就不可以利用exp/imp的方式，利用data guard则有平台的问题。
在这种情况下，可以利用on prebuilt table选项创建mv。然后同步运行一段时间。一次切换，删除
mv，这种情况下可以保持同名的表。mv删除。达到数据同步，切换的目标。

posted @ 2007-08-14 15:32 kebo 阅读(227) | 评论 (0) | 编辑收藏

分析函数一例

select z.a,z.b,z.c from (select lag(t.a,2)over(order by t.a) pp_val, lag(t.a,1)over(order by t.a) p_val, t.a, lead(t.a,1)over(order by t.a) n_val, lead(t.a,2)over(order by t.a) nn_val, t.b,t.c from test2 t) z where z.a = '1' and ((z.p_val = '1' and z.pp_val = '1') or (z.p_val = '1' and z.n_val = '1') or (z.n_val = '1' and z.nn_val = '1'));

posted @ 2007-07-17 16:55 kebo 阅读(230) | 评论 (0) | 编辑收藏

常用链接

留言簿(1)

随笔分类

随笔档案

文章档案

相册

收藏夹

朋友

搜索

积分与排名

最新评论

阅读排行榜

评论排行榜