Problem: I am joining 5 tables together. Two of the joins are left joins. One of the left joined tables only has 2400 records and works fine. However the other left joined table contains 101,000 records and it is NOT joining.
I am only able to get it to work by disabling the optimizer by using the {NO REORDER} clause. The problem is this makes a the query go from 43 seconds to 438 seconds (over 7 minutes)
Select v.name
, pr.duedate
, rd.receiptdate
FROM ((((pub.podetail as pd inner join pub.porel as pr
on ( pd.company = pr.company
and pd.ponum = pr.ponum
and pd.poline = pr.poline
and pr.porelnum > 0
and pr.voidrelease = 0
and pd.ordernum = pr.ordernum
and pd.orderline = pr.orderline
)
) --1
inner join pub.poheader as ph
on ( pd.company = ph.company
and pd.ponum = ph.ponum
and pd.ordernum = ph.ordernum
and pd.vendornum = ph.vendornum
and ph.voidorder = 0
and pd.voidline = 0
)
) --2
left join pub.vendor as v
on (pd.vendornum = v.VendorNum
and pd.company = v.company
)
) --4
left join pub.rcvdtl as rd
on ( ph.company = rd.company
and pd.vendorNum = rd.VendorNum
and rd.purpoint <> 'xxx'
and rd.packslip <> 'xxx'
and rd.packline > 0
and pd.partnum = rd.PartNum
and pr.poline = rd.poline
and pr.porelnum = rd.porelnum
and pr.ponum = rd.PONum
)
) --3
{NO REORDER}
WHERE ph.company = 'ACME'
and ph.voidorder = 0
and pd.voidline = 0
AND (pr.duedate between to_date('1/1/2006') AND to_date('2/2/2009'))
ORDER BY pr.duedate desc, v.vendorid desc
_______________ BASIC Table Stats ___________
select count(*) from pub.rcvDtl rd -- 101,416 records
select count(*) from pub.vendor -- 3,346 records
select count(*) from pub.porel -- 99,901 records
select count(*) from pub.podetail -- 86,441 records
select count(*) from pub.poheader -- 45,119 records
I am only able to get it to work by disabling the optimizer by using the {NO REORDER} clause. The problem is this makes a the query go from 43 seconds to 438 seconds (over 7 minutes)
Select v.name
, pr.duedate
, rd.receiptdate
FROM ((((pub.podetail as pd inner join pub.porel as pr
on ( pd.company = pr.company
and pd.ponum = pr.ponum
and pd.poline = pr.poline
and pr.porelnum > 0
and pr.voidrelease = 0
and pd.ordernum = pr.ordernum
and pd.orderline = pr.orderline
)
) --1
inner join pub.poheader as ph
on ( pd.company = ph.company
and pd.ponum = ph.ponum
and pd.ordernum = ph.ordernum
and pd.vendornum = ph.vendornum
and ph.voidorder = 0
and pd.voidline = 0
)
) --2
left join pub.vendor as v
on (pd.vendornum = v.VendorNum
and pd.company = v.company
)
) --4
left join pub.rcvdtl as rd
on ( ph.company = rd.company
and pd.vendorNum = rd.VendorNum
and rd.purpoint <> 'xxx'
and rd.packslip <> 'xxx'
and rd.packline > 0
and pd.partnum = rd.PartNum
and pr.poline = rd.poline
and pr.porelnum = rd.porelnum
and pr.ponum = rd.PONum
)
) --3
{NO REORDER}
WHERE ph.company = 'ACME'
and ph.voidorder = 0
and pd.voidline = 0
AND (pr.duedate between to_date('1/1/2006') AND to_date('2/2/2009'))
ORDER BY pr.duedate desc, v.vendorid desc
_______________ BASIC Table Stats ___________
select count(*) from pub.rcvDtl rd -- 101,416 records
select count(*) from pub.vendor -- 3,346 records
select count(*) from pub.porel -- 99,901 records
select count(*) from pub.podetail -- 86,441 records
select count(*) from pub.poheader -- 45,119 records