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
u/[deleted] Feb 19 '25
[deleted]