How can I solve this & reach better performance

We are using an ERP system, here is one sql query below – and it’s working:

(it’s using an old syntax)

(select last_name||' '||name||'   '||to_char(datum_p,'DD.MM.YYYY') 
from
  (
   select co.kod_id, bu.last_name, bu.name, cl.datum_p, ctl.rid, md.short_name 
   from ord_odb_l cl, ord_odb_o co, b_users bu, ct_l ctl, m_delivery md
   where co.rid_o = cl.rid and bu.id = cl.operator and cl.delivery_place = md.rid 
   and cl.operator not IN (161,245,46,120,43,184) order by cl.datum_p desc
  ) aa 
where aa.kod_id = orders.SK_ID and aa.rid = orders.rid_ct_a 
and aa.short_name = orders.md and aa.datum_p > orders.datum_ok and rownum = 1)

I want to use an NVL function to select names & datum between orders – distribution:

(but i think, it’s not possible with subquery or i failed, because i get error – missing right parenthesis)

(select last_name||' '||name||'   '||to_char(datum_p,'DD.MM.YYYY') 
from 
  (
   NVL(
    (select co.kod_id, bu.last_name, bu.name, cl.datum_p, ctl.rid, md.short_name
     from ord_odb_l cl, ord_odb_o co, b_users bu, ct_l ctl, m_delivery md 
     where co.rid_o = cl.rid and bu.id = cl.operator and cl.delivery_place = md.rid 
     and cl.operator not IN (161,245,46,120,43,184) order by cl.datum_p desc),

     (select vo.kod_id, bu.last_name, bu.name, vl.datum_p, ctl.rid, md.short_name 
     from distrb_l vl, distrb_o vo, b_users bu, ct_l ctl, m_delivery md
     where vo.rid_o = vl.rid and bu.id = cl.operator and cl.delivery_place = md.rid 
     and cl.operator not IN (161,245,46,120,43,184) order by vl.datum_p desc)
      )
   ) aa 
where aa.kod_id = orders.SK_ID and aa.rid = orders.rid_ct_a 
and aa.short_name = orders.md and aa.datum_p > orders.datum_ok and rownum = 1
)

If have to, we can join distrb_l and ord_odb_l, too:

(select * from ord_odb_l cl join distrb_l vl ON cl.rid = vl.rid_v)

for help:

We have tables: ord_odb_l (orders header) and ord_odb_o (orders contents), distrb_l (distribution header) and distrb_o (distribution contents), b_users (user table), ct_l (pricing table), m_delivery (delivery table)

Columns: kod_id (number), last.name - name (varchar2), datum_p (date), rid (number), short_name (varchar2), operator (number), delivery_place (number)

Actual table: orders

I know it’s bit a complex & hard to read, but how can i solve this and reach a better performance ? Any help would be greatly appreciated.

Answer

You can’t use nvl from two selects returning many columns.

You want to find newest date and matching names. Union all data from both selects then order by and rownum can do it.

Rows from ord_odb_l have higher priority, rows from distrb_l are secondary data source. So, only if there is no data in first select query will return something from second:

(select last_name||' '||name||'   '||to_char(datum_p,'DD.MM.YYYY') 
  from (
    select 1 src, co.kod_id, bu.last_name, bu.name, cl.datum_p, ctl.rid, md.short_name
      from ord_odb_l  cl
      join ord_odb_o  co on co.rid_o = cl.rid 
      join b_users    bu on bu.id = cl.operator 
      cross join ct_l ctl
      join m_delivery md on cl.delivery_place = md.rid
      where cl.operator not IN (161,245,46,120,43,184) 
    union all 
    select 2 src, vo.kod_id, bu.last_name, bu.name, vl.datum_p, ctl.rid, md.short_name 
      from distrb_l   vl
      join distrb_o   vo on vo.rid_o = vl.rid
      join b_users    bu on bu.id = cl.operator
      cross join ct_l ctl
      join m_delivery md on cl.delivery_place = md.rid
      where cl.operator not IN (161,245,46,120,43,184) 
      order by src, datum_p desc) aa 
  where aa.kod_id = orders.SK_ID and aa.rid = orders.rid_ct_a 
    and aa.short_name = orders.md and aa.datum_p > orders.datum_ok and rownum = 1)

Your query is the part of bigger one, perhaps subselect, because we don’t see here what is orders etc.. Probably you can simplify this task, but without the whole image it’s hard to say more. Anyway this should work. Notice that I changed syntax to join form. Table ct_l has no joining condition in your queries, so used cross join, is this correct? There was also derlivery instead of delivery.

One more thing. I assumed that rows from first table have higher priority because this suggests your code. If you want the row with newest date, no matter which table, then use order by datum_p desc instead of order by src, datum_p desc.

Also, in Oracle 12 you can use fetch first row only, but question is tagged as Oracle 11g.

As you noticed it’s hard to answer such question without data access, structure (and without 100% knowing expectations), but maybe this will help.