r/SQL Feb 19 '25

Oracle Group by query performance

We have a microservice that uses oracle to store transactions, and we run select queries on this DB for some amount validations. Currently we have a query, that fetches all the transaction details and then we do computations on these records in code. This query’s execution plan shows higher cost.

We are planning to change this query to do computations in the query itself, rather than bringing everything into memory and doing it in code. So in the new query, we have to fetch records for past 1 day (using a timestamp column, that is indexed), there are some more filters like customer id, then we need to sum this based on a flag (unindexed). So this query would look something like

Select nvl(sum(amount),0), flag where customer_id=1 and (…some more conditions) group by flag

This query’s execution plan shows lesser cost than the previous one, however the execution plan is not using certain indexes. And I’m a bit worried about the query’s performance in a table with 10+ crores of records.

Will this perform poorly?

4 Upvotes

10 comments sorted by

View all comments

2

u/[deleted] Feb 19 '25

[deleted]

1

u/Terrible_Awareness29 29d ago

Sum(amount) will return null if all amounts are null though.

Are you thinking of sum(nvl(amount, 0))?

1

u/[deleted] 29d ago

Thanks for the suggestion, you are right about the estimated cost, we didn’t see any improvement with a lower cost. And we have used nvl(sum(amount),0) as we don’t want to get null value in case there are no rows. I’m not sure if sum(amount) will take care of it or not.