How can I optimize this complex query?

Question

I'm running complex query with a lot of conditions. on big database and the query is taking over 2 minutes. I am using Django for my project. Can I optimize this query? Or should I use Django ORM query? Which one is faster? LN_ACCOUNT table is over 3 mln rows long. Any advice and help would be appreciated. Thank you so much.

SELECT COUNT(*), SUM((select sum(acc.SALDO_EQUIVAL_OUT)
  from LN_ACCOUNT lna, ACCOUNTS acc
  where lna.LOAN_ID = L.LOAN_ID
  and lna.LOAN_TYPE_ACCOUNT in ('1','4','5','8')
  and lna.DATE_NEXT > '21.11.2020'
  and lna.DATE_VALIDATE <= '20.11.2020'
  and acc.CODE = lna.ACCOUNT_CODE
  and (substr(lna.ACCOUNT_CODE, -20, 3) in ('163','915')
      or (
              select 'Y' from
              V_REP_LN_BAL v
              where v.BAL_ACC like substr(lna.ACCOUNT_CODE, -20, 3)||'%'
              and ROWNUM = 1
          ) = 'Y'
      )))/-100
FROM LN_CARD L
and
    L.LOAN_ID IN (
                  select distinct (LOAN_ID) LID
                  from (
                           select (
                                      select LOAN_ID
                                      from LN_ACCOUNT s
                                      where s.ACCOUNT_CODE = ac.CODE
                                        and s.DATE_NEXT > '21.11.2020'
                                        and s.DATE_VALIDATE <= '21.11.2020'
                                        and rownum = 1) as LOAN_ID
                           from ACCOUNTS ac,
                                V_REP_LN_BAL vr
                           where vr.BAL_ACC = ac.CODE_COA
                             and vr.TYPE_BAL in (1, 2, 3, 4)
                       ) lnIDS
                  where LOAN_ID is not null)
and (
  select sum(abs(acc.SALDO_EQUIVAL_OUT))
  from LN_ACCOUNT lna, ACCOUNTS acc
  where lna.LOAN_ID = l.LOAN_ID
  and lna.LOAN_TYPE_ACCOUNT in ('1','4','5','8')
  and lna.DATE_NEXT > '21.11.2020'
  and lna.DATE_VALIDATE <= '21.11.2020'
  and acc.CODE = lna.ACCOUNT_CODE
  and (substr(lna.ACCOUNT_CODE, -20, 3) in ('163','915')
      or (
              select 'Y' from
              V_REP_LN_BAL v
              where v.BAL_ACC like substr(lna.ACCOUNT_CODE, -20, 3)||'%'
              and ROWNUM = 1
          ) = 'Y'
      )
  )
      + abs(
          NVL((select sum(acc.SALDO_EQUIVAL_OUT)
                  from LN_ACCOUNT lna, ACCOUNTS acc
                  where lna.LOAN_ID = l.LOAN_ID
                  and lna.LOAN_TYPE_ACCOUNT = 3
                  and lna.DATE_NEXT > '21.11.2020'
                  and lna.DATE_VALIDATE <= '21.11.2020'
                  and acc.CODE = lna.ACCOUNT_CODE
                  and (substr(lna.ACCOUNT_CODE, -20, 3) in ('163','915')
                  or  (select 'Y' from
                     V_REP_LN_BAL v
                      where v.BAL_ACC like substr(lna.ACCOUNT_CODE, -20, 3)||'%'
                      and ROWNUM = 1) = 'Y'
                  )),0) +
          NVL((select sum(acc.SALDO_EQUIVAL_OUT)
                  from LN_ACCOUNT lna, ACCOUNTS acc
                  where lna.LOAN_ID = l.LOAN_ID
                  and lna.LOAN_TYPE_ACCOUNT = 7
                  and lna.DATE_NEXT > '21.11.2020'
                  and lna.DATE_VALIDATE <= '21.11.2020'
                 and acc.CODE = lna.ACCOUNT_CODE
                  and (substr(lna.ACCOUNT_CODE, -20, 3) in ('163','915')
                  or  (select 'Y' from
                      V_REP_LN_BAL v
                      where v.BAL_ACC like substr(lna.ACCOUNT_CODE, -20, 3)||'%'
                      and ROWNUM = 1) = 'Y'
                  )),0) +
          NVL((select sum(acc.SALDO_EQUIVAL_OUT)
                  from LN_ACCOUNT lna, ACCOUNTS acc
                  where lna.LOAN_ID = l.LOAN_ID
                  and lna.LOAN_TYPE_ACCOUNT = 79
                  and lna.DATE_NEXT > '21.11.2020'
                  and lna.DATE_VALIDATE <= '21.11.2020'
                  and acc.CODE = lna.ACCOUNT_CODE
                  and (substr(lna.ACCOUNT_CODE, -20, 3) in ('163','915')
                  or  (select 'Y' from
                      V_REP_LN_BAL v
                      where v.BAL_ACC like substr(lna.ACCOUNT_CODE, -20, 3)||'%'
                      and ROWNUM = 1) = 'Y'
                  )),0)
          )
      + abs(NVL((select sum(acc.SALDO_EQUIVAL_OUT)
                  from LN_ACCOUNT lna, ACCOUNTS acc
                  where lna.LOAN_ID = l.LOAN_ID
                  and lna.LOAN_TYPE_ACCOUNT = 46
                  and lna.DATE_NEXT > '21.11.2020'
                  and lna.DATE_VALIDATE <= '21.11.2020'
                  and acc.CODE = lna.ACCOUNT_CODE
                  and (substr(lna.ACCOUNT_CODE, -20, 3) in ('163','915')
                  or  (select 'Y' from
                      V_REP_LN_BAL v
                      where v.BAL_ACC like substr(lna.ACCOUNT_CODE, -20, 3)||'%'
                      and ROWNUM = 1) = 'Y'
                  )),0))
      + abs(
          NVL((select sum(acc.SALDO_EQUIVAL_OUT)
                  from LN_ACCOUNT lna, ACCOUNTS acc
                  where lna.LOAN_ID = l.LOAN_ID
                  and lna.LOAN_TYPE_ACCOUNT = 6
                  and lna.DATE_NEXT > '21.11.2020'
                  and lna.DATE_VALIDATE <= '21.11.2020'
                  and acc.CODE = lna.ACCOUNT_CODE
                  and (substr(lna.ACCOUNT_CODE, -20, 3) in ('163','915')
                  or  (select 'Y' from
                      V_REP_LN_BAL v
                      where v.BAL_ACC like substr(lna.ACCOUNT_CODE, -20, 3)||'%'
                      and ROWNUM = 1) = 'Y'
                  )),0) +
          NVL((select sum(acc.SALDO_EQUIVAL_OUT)
                  from LN_ACCOUNT lna, ACCOUNTS acc
                  where lna.LOAN_ID = l.LOAN_ID
                  and lna.LOAN_TYPE_ACCOUNT = 52
                  and lna.DATE_NEXT > '21.11.2020'
                  and lna.DATE_VALIDATE <= '21.11.2020'
                  and acc.CODE = lna.ACCOUNT_CODE
                  and (substr(lna.ACCOUNT_CODE, -20, 3) in ('163','915')
                  or  (select 'Y' from
                      V_REP_LN_BAL v
                      where v.BAL_ACC like substr(lna.ACCOUNT_CODE, -20, 3)||'%'
                      and ROWNUM = 1) = 'Y'
                  )),0) +
          NVL((select sum(acc.SALDO_EQUIVAL_OUT)
                  from LN_ACCOUNT lna, ACCOUNTS acc
                  where lna.LOAN_ID = l.LOAN_ID
                  and lna.LOAN_TYPE_ACCOUNT = 53
                 and lna.DATE_NEXT > '21.11.2020'
                  and lna.DATE_VALIDATE <= '21.11.2020'
                  and acc.CODE = lna.ACCOUNT_CODE
                  and (substr(lna.ACCOUNT_CODE, -20, 3) in ('163','915')
                  or  (select 'Y' from
                      V_REP_LN_BAL v
                      where v.BAL_ACC like substr(lna.ACCOUNT_CODE, -20, 3)||'%'
                      and ROWNUM = 1) = 'Y'
                  )),0) +
          NVL((select sum(acc.SALDO_EQUIVAL_OUT)
                  from LN_ACCOUNT lna, ACCOUNTS acc
                  where lna.LOAN_ID = l.LOAN_ID
                  and lna.LOAN_TYPE_ACCOUNT = 54
                  and lna.DATE_NEXT > '21.11.2020'
                  and lna.DATE_VALIDATE <= '21.11.2020'
                  and acc.CODE = lna.ACCOUNT_CODE
                  and (substr(lna.ACCOUNT_CODE, -20, 3) in ('163','915')
                  or  (select 'Y' from
                      V_REP_LN_BAL v
                      where v.BAL_ACC like substr(lna.ACCOUNT_CODE, -20, 3)||'%'
                      and ROWNUM = 1) = 'Y'
                  )),0)
          ) <> 0;

Answer 1

You need to display you query plan.

For this you can use SQL Developper and click on the "Execution plan" icon in the top toolbar (shortcut F10), or you can do it directly with SQL PLus:

To explain a plan:

explain plan 
for 
select * from table_name where ...;

To display the plan:

select * from table(dbms_xplan.display);

Here take a look at FULL TABLE SCAN lines. Thoses one display table that a completly read by you query. So If a table has over 3M rows, this can take a long time. To avoid FULL TABLE SCAN create index on the column you specify in you where clause.

Answer 2

You are doing multiple nested subqueries on:

+ abs(
  NVL((select sum(acc.SALDO_EQUIVAL_OUT)
       from   LN_ACCOUNT lna, ACCOUNTS acc
       where lna.LOAN_ID = l.LOAN_ID
       and   lna.LOAN_TYPE_ACCOUNT = 3
       and   lna.DATE_NEXT > '21.11.2020'
       and   lna.DATE_VALIDATE <= '21.11.2020'
       and   acc.CODE = lna.ACCOUNT_CODE
       and   ( substr(lna.ACCOUNT_CODE, -20, 3) in ('163','915')
               or  (select 'Y'
                    from   V_REP_LN_BAL v
                    where  v.BAL_ACC like substr(lna.ACCOUNT_CODE, -20, 3)||'%'
                    and    ROWNUM = 1
                   ) = 'Y'
             )
     ),
     0
   )

Where the only difference appears to be the lna.LOAN_TYPE_ACCOUNT you are filtering on. Concatenate all of these so that you only need to query the table once:

+ COALESCE(
    (
      SELECT SUM( t.abs_total )
      FROM   (
        SELECT lna.LOAN_ID,
               lna.LOAN_TYPE_ACCOUNT,
               ABS( SUM( acc.SALDO_EQUIVAL_OUT ) ) AS abs_total
        FROM   LN_ACCOUNT lna
               INNER JOIN ACCOUNTS acc
               ON ( acc.CODE = lna.ACCOUNT_CODE )
        WHERE  lna.LOAN_TYPE_ACCOUNT IN ( 3, 7, 79, 46, 6, 52, 53, 54 )
        AND    lna.DATE_NEXT > DATE '2020-11-21'
        AND    lna.DATE_VALIDATE <= DATE '2020-11-21'
        AND    (    substr(lna.ACCOUNT_CODE, -20, 3) in ('163','915')
                 OR EXISTS( SELECT 1
                            FROM   V_REP_LN_BAL v
                            WHERE  v.BAL_ACC like substr(lna.ACCOUNT_CODE, -20, 3)||'%'
                          )
               )
        GROUP BY
               lna.LOAN_ID,
               lna.LOAN_ACCOUNT_TYPE
      ) t
      WHERE    t.LOAN_ID = l.LOAN_ID
    ),
    0
  )

You need to check to ensure that you still get the same result as you seem to be aggregating multiple LOAN_TYPE_ACCOUNT before using ABS; however, if this is the case then, instead of using LOAN_TYPE_ACCOUNT in the inner-most SELECT and GROUP BY (and totalling each individually), you could use:

CASE 
WHEN LOAN_TYPE_ACCOUNT IN (  3,  7, 79     ) THEN 'Group1'
WHEN LOAN_TYPE_ACCOUNT IN ( 46             ) THEN 'Group2'
WHEN LOAN_TYPE_ACCOUNT IN (  6, 52, 53, 54 ) THEN 'Group3'
END