最基本的把SQL写好

今天捞取数据要关联BKPF,BSIS,BSAK 3张表。。。。 折腾了

1)
select a.belnr,a.bktxt,a.budat,b.lifnr,c.sgtxt,c.hkont,c.kostl from
bkpf a,bsik b,bsis c
    where     a.mandt = '300'
    and a.gjahr = '2008'
    and a.bukrs = 'CC'
    and a.bktxt like 'APVO%'
    and a.budat >= '20081001'
    and a.budat <= '20081231'
    and a.stblg = ' '
    and b.mandt = a.mandt
    and b.gjahr = a.gjahr
    and b.belnr = a.belnr
    and b.bukrs = a.bukrs
    and c.mandt = a.mandt
    and c.gjahr = a.gjahr
    and c.bukrs = a.bukrs
    and c.belnr = a.belnr
    and c.kostl <> ' '   
很慢取数据取不出来，有了
2)
select a.belnr,a.bktxt,a.budat,b.lifnr,c.sgtxt,c.hkont,c.kostl from
(select a.mandt,a.bukrs,a.gjahr,a.belnr,a.bktxt,a.budat from bkpf a
    where     a.mandt = '300'
    and a.gjahr = '2008'
    and a.bukrs = 'CC'
    and a.bktxt like 'APVO%'
    and a.budat >= '20081001'
    and a.budat <= '20081231'
    and a.stblg = ' ') a,bsik b,bsis c
    where b.mandt = a.mandt
    and b.gjahr = a.gjahr
    and b.belnr = a.belnr
    and b.bukrs = a.bukrs
    and c.mandt = a.mandt
    and c.gjahr = a.gjahr
    and c.bukrs = a.bukrs
    and c.belnr = a.belnr
    and c.kostl <> ' '
比上头快，但还是很慢，有了
3)
select /*+ use_hash(a b c)*/a.belnr,a.bktxt,a.budat,b.lifnr,c.sgtxt,c.hkont,c.kostl from
(select a.mandt,a.bukrs,a.gjahr,a.belnr,a.bktxt,a.budat from bkpf a
    where   a.mandt = '300'
    and a.gjahr = '2008'
    and a.bktxt like 'APVO%'
    and a.budat >= '20081001'
    and a.bukrs ='CC'
    and a.budat <= '20081231'
    and a.stblg = ' ') a,bsis c,bsik b
    where b.mandt = a.mandt
    and b.gjahr = a.gjahr
    and b.belnr = a.belnr
    AND B.bukrs = A.bukrs
    and c.mandt = a.mandt
    and c.gjahr = a.gjahr
    and c.belnr = a.belnr
    AND C.bukrs = A.bukrs
    and c.kostl <> ' '
union all
select /*+ use_hash(a b c)*/a.belnr,a.bktxt,a.budat,b.lifnr,c.sgtxt,c.hkont,c.kostl from
(select a.mandt,a.bukrs,a.gjahr,a.belnr,a.bktxt,a.budat from bkpf a
    where   a.mandt = '300'
    and a.gjahr = '2008'
    and a.bktxt like 'APVO%'
    and a.budat >= '20081001'
    and a.bukrs ='CC'
    and a.budat <= '20081231'
    and a.stblg = ' ') a,bsis c,bsak b
    where b.mandt = a.mandt
    and b.gjahr = a.gjahr
    and b.belnr = a.belnr
    AND B.bukrs = A.bukrs
    and c.mandt = a.mandt
    and c.gjahr = a.gjahr
    and c.belnr = a.belnr
    AND C.bukrs = A.bukrs
    and c.kostl <> ' '

a
很慢的时候去查看一下表的index分析一下

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8628891/viewspace-553581/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8628891/viewspace-553581/