Skip to Main Content

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Chris Saxon

Thanks for the question, Ciro .

Asked: July 13, 2020 - 6:48 pm UTC

Answered by: Chris Saxon - Last updated: July 14, 2020 - 1:49 pm UTC

Category: SQL - Version: 11g Release 11.2.0.4.0

Viewed 100+ times

You Asked

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


with LiveSQL Test Case:

and we said...

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.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.