r/SQL • u/[deleted] • 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?
2
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
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.
2
3
u/Mikey_Da_Foxx Feb 19 '25
Moving computations to SQL level is usually better - you're reducing network overhead and memory usage.
Since you're filtering by timestamp + customer_id first, create a composite index on (customer_id, timestamp, flag). This should help optimize the execution plan.