Hello,
I am unconfortable with the query below because it takes about 42 seconds to run and bring about 40000 records.
select
ss.sup_in ,
nvl(lb.lastbank,0)
from suppliers ss
left join (
select
bs.sup_tab,
bs.sup_pad,
bs.sup_in,
first_value(bk.bank_in) over (partition by bs.sup_in order by ps.pay_day desc) as lastbank
from
bills bs
inner join bills_payments bp
on bs.bill_tab = bp.bill_tab
and bs.bill_pad = bp.bill_pad
and bs.bill_in = bp.bill_in
and bs.org_tab = bp.org_tab
and bs.org_pad = bp.org_pad
and bs.org_in = bp.org_in
inner join payments ps
on bp.pay_tab = ps.pay_tab
and bp.pay_pad = ps.pay_pad
and bp.pay_in = ps.pay_in
and bp.org_tab = ps.org_tab
and bp.org_pad = ps.org_pad
and bp.org_in = ps.org_in
inner join banks bk
on ps.bank_tab = bk.bank_tab
and ps.bank_pad = bk.bank_pad
and ps.bank_in = bk.bank_in ) lb
on ss.sup_tab = lb.sup_tab
and ss.sup_pad = lb.sup_pad
and ss.sup_in = lb.sup_in
group by
ss.sup_in ,
nvl(lb.lastbank,0)
Is there other way to do this? I want to get all suppliers with the last bank used to pay their bills. If there is no payment, show 0.
I noticed that the subquery below lists all payments for each supplier and for me, it´s not necessary:
select
bs.sup_tab,
bs.sup_pad,
bs.sup_in,
first_value(bk.bank_in) over (partition by bs.sup_in order by ps.pay_day desc) as lastbank
from
bills bs
inner join bills_payments bp
on bs.bill_tab = bp.bill_tab
and bs.bill_pad = bp.bill_pad
and bs.bill_in = bp.bill_in
and bs.org_tab = bp.org_tab
and bs.org_pad = bp.org_pad
and bs.org_in = bp.org_in
inner join payments ps
on bp.pay_tab = ps.pay_tab
and bp.pay_pad = ps.pay_pad
and bp.pay_in = ps.pay_in
and bp.org_tab = ps.org_tab
and bp.org_pad = ps.org_pad
and bp.org_in = ps.org_in
inner join banks bk
on ps.bank_tab = bk.bank_tab
and ps.bank_pad = bk.bank_pad
and ps.bank_in = bk.bank_in
The commands to create tables and records are available on live sql link.
Best Regards,
Ciro Stahlschmidt
Thanks for providing all the scripts. But to help with slow queries, there's something else we need to see:
The execution plan!
Get this by running:
set serveroutput off
alter session set statistics_level = all;
<your query>
select *
from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));
Make sure this plan includes E-rows, A-rows, and buffers columns. Once you have it, post it here.
I noticed that the subquery below lists all payments for each supplier and for me, it´s not necessary:If it's not necessary... then why is it in the query?!
The outer query uses lastbank, which comes from the banks table. Looking at the joins, this goes from suppliers though all the tables to banks. So it seems to me it is necessary. If you think otherwise, you'll need to explain why.
It may be that you can make this quicker by left joining all the tables in the subquery.
And replacing last_value with:
max ( bk.bank_in ) keep ( dense_rank first order by ps.pay_day desc )
To give something like:
select
ss.sup_in
,nvl ( max ( bk.bank_in ) keep ( dense_rank first order by ps.pay_day desc ), 0 )
from suppliers ss
left join bills bs
on ss.sup_tab = bs.sup_tab
and ss.sup_pad = bs.sup_pad
and ss.sup_in = bs.sup_in
left join bills_payments bp
on bs.bill_tab = bp.bill_tab
and bs.bill_pad = bp.bill_pad
and bs.bill_in = bp.bill_in
and bs.org_tab = bp.org_tab
and bs.org_pad = bp.org_pad
and bs.org_in = bp.org_in
left join payments ps
on bp.pay_tab = ps.pay_tab
and bp.pay_pad = ps.pay_pad
and bp.pay_in = ps.pay_in
and bp.org_tab = ps.org_tab
and bp.org_pad = ps.org_pad
and bp.org_in = ps.org_in
left join banks bk
on ps.bank_tab = bk.bank_tab
and ps.bank_pad = bk.bank_pad
and ps.bank_in = bk.bank_in
group by ss.sup_in ;
But really we need to the execution plan to see what options there are here.