CODEWITHSUNDEEP
oracle-databasejoinoptimizationsubquerymax
Jan S.
Jan S.

Reputation: 331

Oracle mutliple left outer joins only max() rows

I have a statement that is used quiet often but does perform pretty poorly. So i want to optimize it as good as possible. The statement consist of various parts and unions.

One part that is pretty costly is the following.

select * 
from rc050 F
LEFT OUTER JOIN
    (SELECT fi_nr,
      fklz,
      rvc_status,
      lfdnr,
      txt
    FROM rc0531 temp
    WHERE lfdnr =
      (SELECT MAX(lfdnr) FROM rc0531 WHERE fi_nr = temp.fi_nr AND fklz = temp.fklz
      )
    ) SUB_TABLE1
  ON F.fklz   = SUB_TABLE1.fklz
  AND F.fi_nr = SUB_TABLE1.fi_nr
LEFT OUTER JOIN
    (SELECT fi_nr,
      fklz,
      rvc_status,
      lfdnr,
      txt
    FROM rc0532 temp
    WHERE lfdnr =
      (SELECT MAX(lfdnr) FROM rc0532 WHERE fi_nr = temp.fi_nr AND fklz = temp.fklz
      )
    ) SUB_TABLE2
  ON F.fklz   = SUB_TABLE2.fklz
  AND F.fi_nr = SUB_TABLE2.fi_nr
LEFT OUTER JOIN
    (SELECT fi_nr,
      fklz,
      rvc_status,
      lfdnr,
      txt
    FROM rc05311 temp
    WHERE lfdnr =
      (SELECT MAX(lfdnr)
      FROM rc05311
      WHERE fi_nr = temp.fi_nr
      AND fklz    = temp.fklz
      )
    ) SUB_TABLE11
  ON F.fklz   = SUB_TABLE11.fklz
  AND F.fi_nr = SUB_TABLE11.fi_nr
where F.fklz != ' '

This is a part where I have to load from these rc0531 ... rc05311 tables what there latest entry is. There are 11 of these tables so this is broken down. As you can see I'm currently joining each table via subquery and need only the latest entry so i need an additional subquery to get the max(lfdnr).

This all works good so far but i wanna know if there is a more efficient way to perform this.

In my main select I need to be able to address each column from each of these tables.

Do you guys have any suggestions ?

This currently runs in 1.3 sec on 13k rows to get a decent boost this should get down to 0.1 sec or so. Again this is only one problem in a bigger statement full of unefficient declarations.

Upvotes: 0

Views: 38

Answers (2)

Stefanos Zilellis
Stefanos Zilellis

Reputation: 611

replace the left joins from :

LEFT OUTER JOIN (
    SELECT fi_nr,fklz,rvc_status,lfdnr,txt FROM rc0531 temp
    WHERE lfdnr = (SELECT MAX(lfdnr) FROM rc0531 WHERE fi_nr = temp.fi_nr AND fklz = temp.fklz)
    ) SUB_TABLE1
  ON F.fklz   = SUB_TABLE1.fklz
  AND F.fi_nr = SUB_TABLE1.fi_nr

to this:

LEFT OUTER JOIN (

    SELECT fi_nr,fklz,rvc_status,lfdnr,txt FROM rc0531 inner join 
    (SELECT fi_nr, fklz, MAX(lfdnr) lfdnr FROM rc0531 group by fi_nr, fklz)x on x.fi_nr=rc0531.fi_nr and x.fklz=rc0531.fklz and x.lfdnr=rc0531.lfdnr

    ) SUB_TABLE1
  ON F.fklz   = SUB_TABLE1.fklz
  AND F.fi_nr = SUB_TABLE1.fi_nr

let me know if it got to 0.1 sec, i think it will go

Upvotes: 0

eaolson
eaolson

Reputation: 15094

It's not possible to optimize a SQL query. Oracle is going to take your query and determine an execution plan that it uses to determine the information you've asked for. You might completely rewrite the query and, if it leads to the same execution plan, you'll get exactly the same performance. To tune a query, you need to determine the execution plan.

You may well benefit from an index on each of these tables, on the (fi_nr, fklz) columns or possibly even (fi_nr, fklz, lfdnr), depending on how selective that gets. There's no way to tell in advance from this information, you'll just have to try.

You should also remove the select * and select only the columns you want. If it's possible to get just the needed information from an index, Oracle will not need to retrieve the actual table row.

Upvotes: 1

Related Questions