Oracle Optimize Query

i’m working with oracle pl/sql and i have a stored procedure with this query, and it is a bit convoluted, but it gets the job done, the thing is it takes like 35 minutes, and the sql developer Autotrace says that is doing a full scan even though the tables have their indexes.

So is there any way to improve this query?

select tipotrx, sum(saldo) as saldo, 
count(*) as totaltrx from (
 select  max(ids) as IDTRX, max(monto) as monto, min(saldo) as saldo, max(aq_data) as aq_data, thekey, tipotrx
 from (
       select t.SID as ids, (TO_NUMBER(SUBSTR(P.P1, 18, 12))) as monto,
       ((TO_NUMBER(SUBSTR(P.P1, 18, 12)) * (TO_NUMBER(SUBSTR(t.acquirer_data, 13,2)) - 
       TO_NUMBER(SUBSTR(P.P4, 3,2))))) as saldo,
       (TO_CHAR(t.trx_date, 'YYMMDD') || t.auth_code || t.trx_amount || (SELECT 
       functions.decrypt(t.card_number) FROM DUAL)) as thekey,
       t.acquirer_data AS aq_data,
       TO_NUMBER(SUBSTR(t.acquirer_data, 12, 1)) as tipotrx
       from TBL_TRX t INNER JOIN TBL_POS P ON (t.SID = P.transaction) 
       WHERE (TO_NUMBER(SUBSTR(t.acquirer_data, 13,2)) >= TO_NUMBER(SUBSTR(P.P4, 3,2))) 
       AND trunc(t.INC_DATE)  between (TO_DATE('20/06/2020', 'DD/MM/YYYY') - 35) AND TO_DATE('20/06/2020', 'DD/MM/YYYY')
  ) t
  group by thekey,  tipotrx order by max(ids) desc) j 
group by tipotrx;

Thanks.

Answer

Change this:

trunc(t.INC_DATE) between (TO_DATE('20/06/2020', 'DD/MM/YYYY') - 35)
                       AND TO_DATE('20/06/2020', 'DD/MM/YYYY')

To this:

t.INC_DATE between (TO_DATE('20/06/2020', 'DD/MM/YYYY') - 35)
                AND TO_DATE('21/06/2020', 'DD/MM/YYYY') - INTERVAL '1' SECOND

Instead of building a function-based index you can modify the predicate to be sargable (able to use an index). Instead of using TRUNC to subtract from the the column, add a day minus one second to upper bound literal.

The code is more confusing but should be able to take advantage of the index. However, 35 days of data may be a large amount; the date index may not be very useful and you may need to look at other predicates.