SELECT NVL( RTRIM( activity ) , '-' ) activity ,
NVL( RTRIM( lineid ) , '-' ) lineid ,
NVL( RTRIM( lottype ) , '-' ) lottype ,
NVL( RTRIM( process ) , '-' ) process ,
NVL( RTRIM( product ) , '-' ) product ,
NVL( RTRIM( stepseq ) , '-' ) stepseq ,
NVL( RTRIM( eqptype ) , '-' ) eqptype ,
NVL( RTRIM( area ) , '-' ) area ,
NVL( RTRIM( finalstkid ) , '-' ) finalstkid ,
SUM( decode( activity , 'Ship' , quantity , 0 ) ) shipqty ,
SUM( decode( activity , 'Ship' , subcompqty , 0 ) ) shipsubqty ,
SUM( decode( activity , 'UnShip' , quantity , 0 ) ) unshipqty ,
SUM( decode( activity , 'UnShip' , subcompqty , 0 ) ) unshipsubqty ,
SUM( decode( activity , 'StoreShip' , quantity , 0 ) ) storeshipqty ,
SUM( decode( activity , 'StoreShip' , subcompqty , 0 ) ) storeshipsubqty ,
SUM(( ABS( eventtime- pretkouttime- time1 ) *quantity ) ) cycletimeqty ,
SUM(( ABS( eventtime- pretkouttime ) *subcompqty ) ) cycletimesubqty
FROM (
SELECT /*+ use_merge( a b)*/
activity ,
lineid ,
lottype ,
process ,
product ,
stepseq ,
eqptype ,
area ,
finalstkid ,
quantity ,
subcompqty ,
a.pretkouttime ,
a.eventtime ,
NVL( SUM( b.endtime - b.begintime ) , 0 ) time1 ,
a.txnsequence
FROM lotevent a ,
workday b
WHERE a.eventtime > :b2
AND a.eventtime <= :b1
AND RTRIM( a.activity ) IN ( 'Ship' , 'UnShip' , 'StoreShip' )
AND a.pretkouttime < b.begintime( + )
AND a.eventtime > b.endtime( + )
GROUP BY activity ,
lineid ,
lottype ,
process ,
product ,
stepseq ,
eqptype ,
area ,
finalstkid ,
quantity ,
subcompqty ,
pretkouttime ,
eventtime ,
txnsequence
)
GROUP BY activity ,
lineid ,
lottype ,
process ,
product ,
stepseq ,
eqptype ,
area ,
finalstkid
这个语句占用资源忒大了 我是菜鸟 找不出原因 请帮忙~~~
NVL( RTRIM( lineid ) , '-' ) lineid ,
NVL( RTRIM( lottype ) , '-' ) lottype ,
NVL( RTRIM( process ) , '-' ) process ,
NVL( RTRIM( product ) , '-' ) product ,
NVL( RTRIM( stepseq ) , '-' ) stepseq ,
NVL( RTRIM( eqptype ) , '-' ) eqptype ,
NVL( RTRIM( area ) , '-' ) area ,
NVL( RTRIM( finalstkid ) , '-' ) finalstkid ,
SUM( decode( activity , 'Ship' , quantity , 0 ) ) shipqty ,
SUM( decode( activity , 'Ship' , subcompqty , 0 ) ) shipsubqty ,
SUM( decode( activity , 'UnShip' , quantity , 0 ) ) unshipqty ,
SUM( decode( activity , 'UnShip' , subcompqty , 0 ) ) unshipsubqty ,
SUM( decode( activity , 'StoreShip' , quantity , 0 ) ) storeshipqty ,
SUM( decode( activity , 'StoreShip' , subcompqty , 0 ) ) storeshipsubqty ,
SUM(( ABS( eventtime- pretkouttime- time1 ) *quantity ) ) cycletimeqty ,
SUM(( ABS( eventtime- pretkouttime ) *subcompqty ) ) cycletimesubqty
FROM (
SELECT /*+ use_merge( a b)*/
activity ,
lineid ,
lottype ,
process ,
product ,
stepseq ,
eqptype ,
area ,
finalstkid ,
quantity ,
subcompqty ,
a.pretkouttime ,
a.eventtime ,
NVL( SUM( b.endtime - b.begintime ) , 0 ) time1 ,
a.txnsequence
FROM lotevent a ,
workday b
WHERE a.eventtime > :b2
AND a.eventtime <= :b1
AND RTRIM( a.activity ) IN ( 'Ship' , 'UnShip' , 'StoreShip' )
AND a.pretkouttime < b.begintime( + )
AND a.eventtime > b.endtime( + )
GROUP BY activity ,
lineid ,
lottype ,
process ,
product ,
stepseq ,
eqptype ,
area ,
finalstkid ,
quantity ,
subcompqty ,
pretkouttime ,
eventtime ,
txnsequence
)
GROUP BY activity ,
lineid ,
lottype ,
process ,
product ,
stepseq ,
eqptype ,
area ,
finalstkid
这个语句占用资源忒大了 我是菜鸟 找不出原因 请帮忙~~~
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货