Join to 1 row table takes too much time

Question

I am trying to read all product codes (mal_no columns) and quantity data (adet columns) from a table ( which is "hso" table and indexed by mal_no, all the tables below have indexes for mal_no) but even if it has only 1 row of data it keeps running and never ends.

Query without this join (by adding only commented out part below) is immediate.

Do you have any suggestion for this ?

Thanks,

select mt.mal_no,hso.adet siparis,
  mot.birim_no,round((mbs.eldeki_stok_miktar*0.8),0) duzelts,
  mot.oncelik,
  SUM(round((mbs.eldeki_stok_miktar*0.8),0)) OVER(ORDER BY 
    mot.oncelik desc ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) kumule_stok
from mal_birim_ambar_detay@live mbs
  ,mal_tanim@live mt
  ,mss_temin_yeri_oncelik@live mot
  ,web_hso hso
where 1=1
and hso.mal_no=mbs.mal_no
and mbs.mal_no=mt.mal_no
and mbs.birim_no=mot.birim_no
and mt.mal_grup_no=mot.mal_grup_no
and mt.mal_altgrup_no=mot.mal_altgrup_no
--and mt.mal_no in ('1035541001') 
and mbs.eldeki_stok_miktar>0
and mot.oncelik>0
and mbs.ambar_no='01'
order by mot.oncelik desc

The joins seem to be okkay , can you perhaps provide us with the DDL/Structure of these tables. — Jayesh Mulwani, Jul 20 '17 at 07:39

APC · Answer 1 · 2017-07-21T06:54:36.007

You are joining three tables from a remote database to one in a local database. Distributed queries like this are notoriously slow because data has to be sent across the network between the two databases.

The local database will send subqueries to the remote database ; but the joining and filtering happens locally, so there is the potential for a great deal of redundant data to be transmitted from the remote database. So you need to provide the optimizer with enough information so it can craft clever subqueries.

For instance, isolate the remote tables in an inline view:

select rmt.mal_no,
      hso.adet siparis,
      rmt.birim_no,
      rmt.duzelts,
      rmt.oncelik,
      rmt.kumule_stok
from web_hso hso
     join 
      ( select mt.mal_no,
              mot.birim_no,
              round((mbs.eldeki_stok_miktar*0.8),0) duzelts,
              mot.oncelik,
              SUM(round((mbs.eldeki_stok_miktar*0.8),0)) 
                OVER(ORDER BY mot.oncelik desc ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) kumule_stok
from mal_birim_ambar_detay@live mbs
     join mal_tanim@live mt
       on mbs.mal_no=mt.mal_no
     join mss_temin_yeri_oncelik@live mot
      on mbs.birim_no=mot.birim_no
      and mt.mal_grup_no=mot.mal_grup_no
      and mt.mal_altgrup_no=mot.mal_altgrup_no
      where mbs.eldeki_stok_miktar>0
      and mot.oncelik>0
      and mbs.ambar_no='01' ) rmt
    on hso.mal_no=rmt.mal_no
    order by rmt.oncelik desc

Obviously this is only a guess because I don't understand your data model or your data. You need to apply your domain knowledge here to craft a good query. Inline views alone may not deliver all the speed you need. For instance if most of the work involves the joins on the three remote tables, and likely it does, then as @BriteSponge suggests you may find using the driving_site hint could improve the performance dramatically. So, using the inline view from above, the code would start

select  /*+DRIVING_SITE(rmt)*/ 
      rmt.mal_no,
      hso.adet siparis,
      rmt.birim_no,
      rmt.duzelts,
      rmt.oncelik,
      rmt.kumule_stok
from web_hso hso
     join ( ... ) rmt
     on hso.mal_no=rmt.mal_no

The Oracle document has further guidance. Find out more.

I don't like hints much but it looks like using the DRIVING_SITE hint might also help out here. — BriteSponge, Jul 20 '17 at 08:52
@BriteSponge - the DRIVING_SITE hint is generally considered one of the safer hints to use. It's a good suggest and I've incorporated it in my answer, — APC, Jul 20 '17 at 08:57

score 0 · Answer 2 · answered Jul 20 '17 at 08:15

It is hard to tell from this much of information.

But I would suggest you to follow this steps:

If you have any database admin or if you have an appropriate tool, check the execution plan. (For example TOAD shows execution plan for Oracle databases.)
If you see a "full scan" in the execution plan within nested loops, it means the database is scanning the table over and over again.

For an overall approach, you can use these methods:

Among the where condition items find the ones which reduce the set first.
(Oracle) use index hints and use_nl hint to explicitly define the database, how it should go to the tables.

And finally, for a principle try to avoid big joins. Divide and conquer your data. Try to write nested loops and use as much as indexes while retrieving your data.

Join to 1 row table takes too much time

2 Answers2