有人問這樣的sql該怎么實(shí)現(xiàn)：
表數(shù)據(jù)和結(jié)構(gòu)
?? ?CODE?NAME????B01????S01????B02????S02
????1??????????張三???????數(shù)學(xué)????80??
????1??????????張三????????????????????????????語文????75
????2??????????王五???????數(shù)學(xué)????70??
????2??????????王五????
????3??????????李四???????數(shù)學(xué)????50??
????3??????????李四???????????????????????????語文????88

希望查詢出如下結(jié)果：
?? ?CODE?SUM_STR(NAME)????B01????SUM_STR(S01)????B02????SUM_STR(S02)
????1????????????????張三????????????????????數(shù)學(xué)????????????????80?????????? 語文????????????75
????2????????????????王五????????????????????數(shù)學(xué)????????????????70??
????3????????????????李四????????????????????數(shù)學(xué)????????????????50?????????? 語文?????????????88
這個(gè)問題可以采用自定義的聚集函數(shù)來實(shí)現(xiàn)：

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

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

-------最后查詢語句：

在項(xiàng)目進(jìn)入性能測試階段，終于爆發(fā)了sql運(yùn)行緩慢，系統(tǒng)吞吐量下降，甚至一度出現(xiàn)oracle服務(wù)器cpu100%的情況。具體開發(fā)和測試人員報(bào)告情況，開始介入處理。

具體查找性能緩慢的過程略除。
發(fā)現(xiàn)一條sql運(yùn)行緩慢。通過跟蹤發(fā)現(xiàn)一下信息
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 行。

?

?

執(zhí)行計(jì)劃

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

?? 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)

?

?

?

?

統(tǒng)計(jì)信息

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

??????? 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

其中一致讀達(dá)到近3萬次，關(guān)聯(lián)調(diào)用出現(xiàn)314次。排序數(shù)值也非常多，顯然第一目標(biāo)是把這兩個(gè)數(shù)據(jù)降下來。
通過進(jìn)一步的分析。發(fā)現(xiàn)出現(xiàn)這些問題的主要原因是調(diào)用eam_db.concatassetname( alias_p.operationkey, alias_p.operationid )這個(gè)包。
開始考慮直接在sql外層做關(guān)聯(lián)，不用function來實(shí)現(xiàn)。利用聚集函數(shù)來合并數(shù)據(jù)。
著手建立：

聚集函數(shù)：?CREATE OR REPLACE FUNCTION F_ASSETLINK(P_STR VARCHAR2) RETURN VARCHAR2
AGGREGATE USING asset_link;

----------------------
創(chuàng)建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
)
------------------------------------------------------

創(chuàng)建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;
調(diào)整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
得到統(tǒng)計(jì)數(shù)據(jù)如下：
C:\Documents and Settings\ibm>sqlplus /nolog

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

Copyright (c) 1982, 2005, Oracle.? All rights reserved.

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行。

執(zhí)行計(jì)劃
----------------------------------------------------------
?? 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)

?

?

統(tǒng)計(jì)信息
----------------------------------------------------------
????????? 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變?yōu)?次
一致讀降為847。下降非常客觀
關(guān)聯(lián)調(diào)用僅有6次。
此sql性能優(yōu)化非常可觀。至此優(yōu)化結(jié)束：)

數(shù)據(jù)庫遷移過程中需要目標(biāo)數(shù)據(jù)庫和原數(shù)據(jù)庫結(jié)構(gòu)相同和數(shù)據(jù)是最新。
為了保持最新數(shù)據(jù)和快速切換就不可以利用exp/imp的方式，利用data guard則有平臺(tái)的問題。
在這種情況下，可以利用on prebuilt table選項(xiàng)創(chuàng)建mv。然后同步運(yùn)行一段時(shí)間。一次切換，刪除
mv，這種情況下可以保持同名的表。mv刪除。達(dá)到數(shù)據(jù)同步，切換的目標(biāo)。

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'));

查詢結(jié)果xml化： select dbms_xmlquery.getXML(' select * from test')from dual; 表的歷史記錄：執(zhí)行：begin dbms_wm.enableversioning('tablename','VIEW_WO_OVERWRITE') 則對(duì)這個(gè)表的cud操作都會(huì)記錄歷史，這個(gè)在系統(tǒng)中做歷史再好不過了。 還有終于被tom說明：分析函數(shù)原來就是矩陣運(yùn)算，呵呵，終于知道這類函數(shù)的數(shù)學(xué)原理了，呵呵，真爽，總算知道怎么理解了。 還有寶貝兒遇到新項(xiàng)目，需要很深的會(huì)計(jì)知識(shí)了，還被老板亂說，導(dǎo)致不好工作，希望她不要煩惱，開心工作

配置:server.modules?
server.modules????????????? = (
??????????????????????????????? "mod_rewrite",
??????????????????????????????? "mod_redirect",
??????????????????????????????? "mod_access",
??????????????????????????????? "mod_status",
??????????????????????????????? "mod_scgi",
??????????????????????????????? "mod_accesslog" )
配置
index-file.names??????????? = ( "index.php", "index.html",
??????????????????????????????????????????? "index.htm", "default.htm" )
#### accesslog module
accesslog.filename????????? = "c:/depot/log/access.log"
static-file.exclude-extensions = ( ".php", ".pl", ".fcgi",".scgi" )?????? 標(biāo)紅的需要加上

## bind to port (default: 80)
server.port??????????????? = 8080------------------訪問端口,我設(shè)置8080

## error-handler for status 404
server.error-handler-404?? = "/dispatch.scgi"
-----------------------------------------------
scgi.server = ("dispatch.scgi" => ((
"host" => "127.0.0.1",
"port" => 9999,
"check-local" => "disable"
)) )

scgi.debug=3

status.status-url = "/server-status"
status.config-url = "/server-config"

-------------------------------------
## server.virtual-* options
server.document-root??????? = "c:/depot/public"
記得這個(gè)需要設(shè)置到public目錄,不然按默認(rèn)的rails生成的文檔一些東西訪問不來的
-----------------------------------------------------------------------------------------------------
需要注意的是你開發(fā)的程序需要放在c盤下,不然找不到config/scgi.yaml這個(gè)文件
然后lighttpd必須裝在c盤下(當(dāng)前版本下1.4.11)

今天給數(shù)據(jù)庫執(zhí)行@spcreate.sql老是出
SP2-0734:unknown command beginning "spcreate.s..." - rest of line ignored.
郁悶壞了。經(jīng)過一番折騰原來是
solution Description:
?=====================
You need to enter a valid SQL*Plus command.
In this case, you cannot start svrmgrl from within SQL*Plus,
you have to start svrmgrl from the command prompt.
This error will also occur when trying to execute a
the @ symbol is mapped to the key 'Kill' from the user's keyboard.
The way to find out the current keyboard mapping in a unix environment is using the command 'stty -a'.
The way to correct problem is to map 'Kill' to some other keyboard symbol.
The command example would be 'stty kill ^U'.
Having remapped the key you would then log into SQL*Plus and execute script.
?-------------------------------紀(jì)念一下
如果出現(xiàn)空行報(bào)錯(cuò)的話 記得執(zhí)行:
SET SQLBLANKLINES ON

呵呵，最近吵架比較多，我們項(xiàng)目和另公司合作，經(jīng)常出現(xiàn)兩家吵架的事件，小弟不信參與其中。通過具體實(shí)踐，發(fā)現(xiàn)我現(xiàn)在很容易發(fā)現(xiàn)別人說話的漏洞。然后常常質(zhì)問對(duì)方大哥無法可說，呵呵，痛快。 我想這是 我比較最近比較喜歡高手們爭論話題的帖子，建議想提高吵架水平的人多學(xué)學(xué)(別用在mm身上^^)