Oracle query modificaton for optimization

Question

 select distinct 
    bsa.bsac customer_number,
    fli.id lineitemid,
    NVL(
  (SELECT *
  FROM
    (SELECT TO_CHAR(hiterm.Quantity)
    FROM NUfliT hiterm
    WHERE hiterm.fli_id = fli.Id
    AND (hiterm.EndDate             IS NULL
    OR hiterm.EndDate                > add_months(sysdate,-3))
    AND hiterm.hikeruleitem_id     IS NULL
    ORDER BY fli.id DESC
    )
  WHERE rownum = 1
  ), fli.Quantity) AS Total_Quantity,
  NVL(
  (SELECT *
  FROM
    (SELECT hiterm.UsedQuantity
    FROM NUfliT hiterm
    WHERE hiterm.fli_id = fli.Id
    AND (hiterm.EndDate             IS NULL
    OR hiterm.EndDate                > add_months(sysdate,-3))
    AND hiterm.hikeruleitem_id     IS NULL
    ORDER BY fli.id DESC
    )
  WHERE rownum = 1
  ), fli.UsedQuantity) AS Used_Quantity,
  NVL(
  (SELECT *
  FROM
    (SELECT hiterm.StartDate
    FROM NUfliT hiterm
    WHERE hiterm.fli_id = fli.Id
    AND (hiterm.EndDate             IS NULL
    OR hiterm.EndDate                > add_months(sysdate,-3))
    AND hiterm.hikeruleitem_id     IS NULL
    ORDER BY fli.id DESC
    )
  WHERE rownum = 1
  ), fli.StartDate) AS Term_Start_Date,
  NVL(
  (SELECT *
  FROM
    (SELECT hiterm.EndDate
    FROM NUfliT hiterm
    WHERE hiterm.fli_id = fli.Id
    AND (hiterm.EndDate             IS NULL
    OR hiterm.EndDate                > add_months(sysdate,-3))
    AND hiterm.hikeruleitem_id     IS NULL
    ORDER BY id DESC
    )
  WHERE rownum = 1
  ), fli.EndDate) AS Term_End_Date,
from n3a_usage_contractlineitem fli
    join contract con on fli.contract_id = con.id
    join customer cust on con.customer_id = cust.id
    join organization org on org.customer_id = cust.id
    join product prod on fli.product_id = prod.id
    left join gfaccount bsa on cust.sfscc = bsa.bsac
where 
    fli.ratestatus   = 'A' and 
    fli.status = 'A' and 
    fli.active = 1 and 
    con.status in ('A') and 
    cust.status = 'active' and 
    bsa.test_account = 0;

Answer 1

I did not analyze your query in detail, however the solution could be similar to this:

select distinct
    bsa.bsac customer_number,
    fli.id lineitemid,
    NVL(MIN(hiterm.Quantity) KEEP (DENSE_RANK FIRST ORDER BY fli.id DESC), fli.Quantity) AS Total_Quantity,
    NVL(MIN(hiterm.Used_Quantity) KEEP (DENSE_RANK FIRST ORDER BY fli.id DESC), fli.Used_Quantity) AS Used_Quantity,
    NVL(MIN(hiterm.EndDate) KEEP (DENSE_RANK FIRST ORDER BY fli.id DESC), fli.EndDate) AS Term_End_Date
from n3a_usage_contractlineitem fli
    join contract con on fli.contract_id = con.id
    join customer cust on con.customer_id = cust.id
    join organization org on org.customer_id = cust.id
    join product prod on fli.product_id = prod.id
    left join gfaccount bsa on cust.sfscc = bsa.bsac
    left outer join NUfliT hiterm on hiterm.fli_id = fli.Id
       AND hiterm.hikeruleitem_id IS NULL
       AND (hiterm.EndDate IS NULL OR hiterm.EndDate > add_months(sysdate,-3)
where
    fli.ratestatus   = 'A' and
    fli.status = 'A' and
    fli.active = 1 and
    con.status in ('A') and
    cust.status = 'active' and
    bsa.test_account = 0;