Oracle Optimize Query

Question

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.

Provide sample data, desired results, and an explanation of what you want to accomplish. — Gordon Linoff
– Gordon Linoff, Commented Jun 26, 2020 at 17:57
You are processing more than a month of data. I assume this is for a nightly/batch processing, not for interactive users. What's the current execution time? What's your target execution time? — The Impaler
– The Impaler, Commented Jun 26, 2020 at 17:59
order by max(ids) desc has no effect in the query. You should remove it. — The Impaler
– The Impaler, Commented Jun 26, 2020 at 18:01
SELECT functions.decrypt(t.card_number) FROM DUAL can be replaced with just functions.decrypt(t.card_number) — Popeye
– Popeye, Commented Jun 26, 2020 at 18:06
@TheImpaler it's for users sadly, and yes it's searching for the last 35 days, the client told me they handle about 90.000 records a day, so in that month they get more than 3 millions, i'll remove the order by max(ids) — Héctor Pérez Silva
– Héctor Pérez Silva, Commented Jun 26, 2020 at 18:07

Henry A. Kissinger · Accepted Answer · 2020-06-26 20:10:33Z

1

Most of the time the index has to match exactly what's in the WHERE clause to be eligible for use. An index on the acquirer_data column cannot be used when your WHERE clause says

 TO_NUMBER(SUBSTR(t.acquirer_data, 13,2))

An index on the INC_DATE cannot be used when your WHERE clause says

 trunc(t.INC_DATE)

You manipulate every column in the WHERE clause and that alone can potentially prevent the use of any normal index.

If however you create function-based indexes, you can make some new indexes that match what's in your WHERE clause. That way at least there's a chance that the DB will use an index instead of doing full table scans.

--example function based index.
 CREATE INDEX TRUNC_INC_DATE ON TBL_TRX (trunc(t.INC_DATE));

Of course, new indexes take up more space and add overhead of their own. Keep using that autotrace to see if it's worth it.

Also, updating table statistics probably wont hurt either.

answered Jun 26, 2020 at 20:10

Henry A. Kissinger

1114 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Héctor Pérez Silva Over a year ago

i'm gonna try the index with the trunc(INC_DATE), well, the thing is the acquirer_data has a lot of necessary information, so it's not optional to substring it.

Henry A. Kissinger Over a year ago

There's no limit on how many function-based indexes you can add to a column. If you still need a normal index on acquirer_data for other queries that use the full value that's fine. You can still add a function-based index on "TO_NUMBER(SUBSTR(t.acquirer_data, 13,2))" and keep all existing indexes.

Henry A. Kissinger Over a year ago

Something else just occurred to me, do you need to truncate the INC_DATE?

Jon Heller · Accepted Answer · 2020-06-27 05:12:36Z

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.

Collectives™ on Stack Overflow

Oracle Optimize Query

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related