数据不多,但速度奇慢,有什么问题了,看我的PL/SQL
Stime:=to_char(sysdate-to_number(to_char(sysdate - 1, 'D '))+1, 'YYYY-MM-DD ');
Etime:=to_char(sysdate-to_number(to_char(sysdate - 1, 'D '))+1, 'YYYY-MM-DD ');
Open TEMP_CURSOR For select IBITM,IBLITM,IBMCU,IBVEND,
(select ABALKY from F0101 where A.IBVEND=F0101.ABAN8 and rownum <2),
(select IMLITM from F4101 where A.IBITM=F4101.IMITM and rownum <2),
(select IMDSC1 from F4101 where A.IBITM=F4101.IMITM and rownum <2),IBLTLV,
(select BPUPRC from F4106 where A.IBITM=F4106.BPITM and rownum <2),
(select ABALPH from F0101 where A.IBBUYR=F0101.ABAN8 and rownum <2),
(select ABALPH from F0101 where A.IBANPL=F0101.ABAN8 and rownum <2),IBSAFE,
(select sum(LIPQOH) from F41021 where A.IBITM=LIITM and A.IBMCU=LIMCU),
(select Sum(F4311.PDUOPN) from F4311 where A.IBVEND=F4311.PDAN8 and A.IBMCU=F4311.PDMCU and A.IBITM=F4311.PDITM and F4311.PDUOPN> 0),
Nvl((select sum(SDUORG) from F4211 where trim(SDITM)=trim(A.IBITM) and trim(SDMCU)=trim(A.IBMCU) and SDNXTR < '560 ' and to_char(to_date(to_char(SDDRQJ+1900000), 'YYYYDDD '), 'YYYY-MM-DD ')> =to_char(to_Date(Stime, 'yyyy/mm/dd '), 'YYYY-MM-DD ') and to_char(to_date(to_char(SDDRQJ+1900000), 'YYYYDDD '), 'YYYY-MM-DD ') <to_char(to_Date(Etime, 'yyyy/mm/dd ')+1 *7, 'YYYY-MM-DD ')),0),
Nvl((select sum(SDUORG) from F4211 where trim(SDITM)=trim(A.IBITM) and trim(SDMCU)=trim(A.IBMCU) and SDNXTR < '560 ' and to_char(to_date(to_char(SDDRQJ+1900000), 'YYYYDDD '), 'YYYY-MM-DD ')> =to_char(to_Date(Stime, 'yyyy/mm/dd ')+1*7 , 'YYYY-MM-DD ') and to_char(to_date(to_char(SDDRQJ+1900000), 'YYYYDDD '), 'YYYY-MM-DD ') <to_char(to_Date(Etime, 'yyyy/mm/dd ')+2*7, 'YYYY-MM-DD ')),0),
Nvl((select sum(SDUORG) from F4211 where trim(SDITM)=trim(A.IBITM) and trim(SDMCU)=trim(A.IBMCU) and SDNXTR < '560 ' and to_char(to_date(to_char(SDDRQJ+1900000), 'YYYYDDD '), 'YYYY-MM-DD ')> =to_char(to_Date(Stime, 'yyyy/mm/dd ')+2*7 , 'YYYY-MM-DD ') and to_char(to_date(to_char(SDDRQJ+1900000), 'YYYYDDD '), 'YYYY-MM-DD ') <to_char(to_Date(Etime, 'yyyy/mm/dd ')+3*7, 'YYYY-MM-DD ')),0),
Nvl((select sum(SDUORG) from F4211 where trim(SDITM)=trim(A.IBITM) and trim(SDMCU)=trim(A.IBMCU) and SDNXTR < '560 ' and to_char(to_date(to_char(SDDRQJ+1900000), 'YYYYDDD '), 'YYYY-MM-DD ')> =to_char(to_Date(Stime, 'yyyy/mm/dd ')+3*7 , 'YYYY-MM-DD ') and to_char(to_date(to_char(SDDRQJ+1900000), 'YYYYDDD '), 'YYYY-MM-DD ') <to_char(to_Date(Etime, 'yyyy/mm/dd ')+4*7, 'YYYY-MM-DD ')),0),
Nvl((select sum(SDUORG) from F4211 where trim(SDITM)=trim(A.IBITM) and trim(SDMCU)=trim(A.IBMCU) and SDNXTR < '560 ' and to_char(to_date(to_char(SDDRQJ+1900000), 'YYYYDDD '), 'YYYY-MM-DD ')> =to_char(to_Date(Stime, 'yyyy/mm/dd ')+4*7 , 'YYYY-MM-DD ') and to_char(to_date(to_char(SDDRQJ+1900000), 'YYYYDDD '), 'YYYY-MM-DD ') <to_char(to_Date(Etime, 'yyyy/mm/dd ')+5*7, 'YYYY-MM-DD ')),0),
Nvl((select sum(SDUORG) from F4211 where trim(SDITM)=trim(A.IBITM) and trim(SDMCU)=trim(A.IBMCU) and SDNXTR < '560 ' and to_char(to_date(to_char(SDDRQJ+1900000), 'YYYYDDD '), 'YYYY-MM-DD ')> =to_char(to_Date(Stime, 'yyyy/mm/dd ')+5*7 , 'YYYY-MM-DD ') and to_char(to_date(to_char(SDDRQJ+1900000), 'YYYYDDD '), 'YYYY-MM-DD ') <to_char(to_Date(Etime, 'yyyy/mm/dd ')+6*7, 'YYYY-MM-DD ')),0),
Nvl((select sum(SDUORG) from F4211 where trim(SDITM)=trim(A.IBITM) and trim(SDMCU)=trim(A.IBMCU) and SDNXTR < '560 ' and to_char(to_date(to_char(SDDRQJ+1900000), 'YYYYDDD '), 'YYYY-MM-DD ')> =to_char(to_Date(Stime, 'yyyy/mm/dd ')+6*7 , 'YYYY-MM-DD ') and to_char(to_date(to_char(SDDRQJ+1900000), 'YYYYDDD '), 'YYYY-MM-DD ') <to_char(to_Date(Etime, 'yyyy/mm/dd ')+7*7, 'YYYY-MM-DD ')),0),
Nvl((select sum(SDUORG) from F4211 where trim(SDITM)=trim(A.IBITM) and trim(SDMCU)=trim(A.IBMCU) and SDNXTR < '560 ' and to_char(to_date(to_char(SDDRQJ+1900000), 'YYYYDDD '), 'YYYY-MM-DD ')> =to_char(to_Date(Stime, 'yyyy/mm/dd ')+7*7 , 'YYYY-MM-DD ') and to_char(to_date(to_char(SDDRQJ+1900000), 'YYYYDDD '), 'YYYY-MM-DD ') <to_char(to_Date(Etime, 'yyyy/mm/dd ')+8*7, 'YYYY-MM-DD ')),0),
Nvl((select sum(SDUORG) from F4211 where trim(SDITM)=trim(A.IBITM) and trim(SDMCU)=trim(A.IBMCU) and SDNXTR < '560 ' and to_char(to_date(to_char(SDDRQJ+1900000), 'YYYYDDD '), 'YYYY-MM-DD ')> =to_char(to_Date(Stime, 'yyyy/mm/dd ')+8*7 , 'YYYY-MM-DD ') and to_char(to_date(to_char(SDDRQJ+1900000), 'YYYYDDD '), 'YYYY-MM-DD ') <to_char(to_Date(Etime, 'yyyy/mm/dd ')+9*7, 'YYYY-MM-DD ')),0),
Nvl((select sum(SDUORG) from F4211 where trim(SDITM)=trim(A.IBITM) and trim(SDMCU)=trim(A.IBMCU) and SDNXTR < '560 ' and to_char(to_date(to_char(SDDRQJ+1900000), 'YYYYDDD '), 'YYYY-MM-DD ')> =to_char(to_Date(Stime, 'yyyy/mm/dd ')+9*7 , 'YYYY-MM-DD ') and to_char(to_date(to_char(SDDRQJ+1900000), 'YYYYDDD '), 'YYYY-MM-DD ') <to_char(to_Date(Etime, 'yyyy/mm/dd ')+10*7, 'YYYY-MM-DD ')),0),
Nvl((select sum(SDUORG) from F4211 where trim(SDITM)=trim(A.IBITM) and trim(SDMCU)=trim(A.IBMCU) and SDNXTR < '560 ' and to_char(to_date(to_char(SDDRQJ+1900000), 'YYYYDDD '), 'YYYY-MM-DD ')> =to_char(to_Date(Stime, 'yyyy/mm/dd ')+10*7, 'YYYY-MM-DD ') and to_char(to_date(to_char(SDDRQJ+1900000), 'YYYYDDD '), 'YYYY-MM-DD ') <to_char(to_Date(Etime, 'yyyy/mm/dd ')+11*7, 'YYYY-MM-DD ')),0),
Nvl((select sum(SDUORG) from F4211 where trim(SDITM)=trim(A.IBITM) and trim(SDMCU)=trim(A.IBMCU) and SDNXTR < '560 ' and to_char(to_date(to_char(SDDRQJ+1900000), 'YYYYDDD '), 'YYYY-MM-DD ')> =to_char(to_Date(Stime, 'yyyy/mm/dd ')+11*7, 'YYYY-MM-DD ') and to_char(to_date(to_char(SDDRQJ+1900000), 'YYYYDDD '), 'YYYY-MM-DD ') <to_char(to_Date(Etime, 'yyyy/mm/dd ')+12*7, 'YYYY-MM-DD ')),0),
Nvl((select sum(SDUORG) from F4211 where trim(SDITM)=trim(A.IBITM) and trim(SDMCU)=trim(A.IBMCU) and SDNXTR < '560 ' and to_char(to_date(to_char(SDDRQJ+1900000), 'YYYYDDD '), 'YYYY-MM-DD ')> =to_char(add_months(to_Date(Stime, 'yyyy/mm/dd ')+11*7,1), 'YYYY-MM ')|| '-1 ' and to_char(to_date(to_char(SDDRQJ+1900000), 'YYYYDDD '), 'YYYY-MM-DD ') <to_char(add_months(to_Date(Stime, 'yyyy/mm/dd ')+11*7,2), 'YYYY-MM ')|| '-01 '),0),
Nvl((select sum(SDUORG) from F4211 where trim(SDITM)=trim(A.IBITM) and trim(SDMCU)=trim(A.IBMCU) and SDNXTR < '560 ' and to_char(to_date(to_char(SDDRQJ+1900000), 'YYYYDDD '), 'YYYY-MM-DD ')> =to_char(add_months(to_Date(Stime, 'yyyy/mm/dd ')+11*7,2), 'YYYY-MM ')|| '-1 ' and to_char(to_date(to_char(SDDRQJ+1900000), 'YYYYDDD '), 'YYYY-MM-DD ') <to_char(add_months(to_Date(Stime, 'yyyy/mm/dd ')+11*7,3), 'YYYY-MM ')|| '-01 '),0),
Nvl((select sum(SDUORG) from F4211 where trim(SDITM)=trim(A.IBITM) and trim(SDMCU)=trim(A.IBMCU) and SDNXTR < '560 ' and to_char(to_date(to_char(SDDRQJ+1900000), 'YYYYDDD '), 'YYYY-MM-DD ')> =to_char(add_months(to_Date(Stime, 'yyyy/mm/dd ')+11*7,3), 'YYYY-MM ')|| '-1 ' and to_char(to_date(to_char(SDDRQJ+1900000), 'YYYYDDD '), 'YYYY-MM-DD ') <to_char(add_months(to_Date(Stime, 'yyyy/mm/dd ')+11*7,4), 'YYYY-MM ')|| '-01 '),0),
Nvl((Select MFFQT From VIEW55CH126 Where trim(MFLITM)=trim(A.IBLITM) and rownum <2),0),
Nvl((Select SSVSPC from F43211 where trim(SSMCU)=trim(A.IBMCU) and trim(SSITM)=trim(A.IBITM) and trim(SSAN8)=trim(A.IBVEND) and rownum <2),0)
from F4102 A;
F4102 总共才一万多数据,但处理起来几个小时都完不成,有什么办法精简一下吗?
[解决办法]
你的SQL写的太复杂了吧，一个表一万条数据，几个一万条数据关联起来需要很长时间也不惊怪，你把这个复杂的语句拆成小的语句，通过中间变量来转换值，这样可能就会提高效率了。
[解决办法]
你帖出執行計劃來看看
看代碼頭痛
[解决办法]
太多nvl trim to_char
也太多列子查迅了 設計方面估計存在問題
Nvl((Select SSVSPC from F43211 where trim(SSMCU)=trim(A.IBMCU) and trim(SSITM)=trim(A.IBITM) and trim(SSAN8)=trim(A.IBVEND) and rownum <2),0)
不要對條件字段加函數
Nvl((Select SSVSPC from F43211 where SSMCU=A.IBMCU and SSITM=A.IBITM and SSAN8=A.IBVEND and rownum <2),0)