1

I am currently working on a query for a front end application, which is experiencing weird performance issues when filters are applied on large sets of data. Here is a link to an SQLFiddle of the simplified query.

The query is executing in about 1-2 seconds when fetching all rows. (limited only by rownum) A similar performance can be observed when filtering by amounts above a certain threshold (e.g. total > 1000). However, when filtering below a threshold (e.g. available < 1000) the query is taking forever to calculate the results.

As a general note all related table columns are indexed.

Is there perhaps a better way to perform the calculations shown in the example?

1

1 Answer 1

0
SELECT ident_account,ident_instrument,
        nvl(available, 0) + nvl(reserved, 0) + nvl(restrctd,0) AS total,
        nvl(available, 0) AS available,nvl(reserved, 0) AS reserved,nvl(restrctd, 0) AS restrctd,
        validfrom, minvaliduntil as validuntil
   FROM (
         SELECT h.ident_account, h.ident_instrument, h.validfrom, h.validuntil,
                rank() over(partition by h.ident_account, h.ident_instrument,IDENT_BALANCEGROUP order by h.validfrom desc) as rnk,
                sum(case when IDENT_BALANCEGROUP = 1 
                         then h.amount
                         else 0 end) over(partition by h.ident_account, h.ident_instrument,IDENT_BALANCEGROUP order by h.validfrom desc
                                          range between current row
                                                    and (nvl(validuntil,validfrom+1) - validfrom) following) as available,
                sum(case when IDENT_BALANCEGROUP = 2 
                         then h.amount
                         else 0 end) over(partition by h.ident_account, h.ident_instrument,IDENT_BALANCEGROUP order by h.validfrom desc
                                          range between current row
                                                    and (nvl(validuntil,validfrom+1) - validfrom) following) as reserved,
                sum(case when IDENT_BALANCEGROUP = 3
                         then h.amount
                         else 0 end) over(partition by h.ident_account, h.ident_instrument,IDENT_BALANCEGROUP order by h.validfrom desc
                                          range between current row
                                                    and (nvl(validuntil,validfrom+1) - validfrom) following) as restrctd,
                min(validuntil) over(partition by h.ident_account, h.ident_instrument order by h.validfrom desc
                                          range between current row
                                                    and (nvl(validuntil,validfrom+1) - validfrom) following) as minvaliduntil,
                h.amount,b.ident_balancegroup
           FROM holdings h INNER JOIN balancetype b ON h.ident_balancetype = b.ident_balancetype
        )
  WHERE rnk = 1;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.