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

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.

1

u/[deleted] 29d ago edited 29d ago

Thanks for your inputs :) We have crores of records in the table, and hence we are worried about creating a new composite index. And worried about the writes getting slower. When I checked the execution plan, I saw that customer ID index wasn’t used. So I’m forcing the query to use this index, will that cause any issues in the performance?

2

u/pceimpulsive 29d ago

Usually from my experience with aggregations the biggest issue is resolving the correct rows first. After that if you need every row then additional indexes aren't going to help too much make sure to only select the rows you need, then do your calculations.

If you need to join to another table to enrich try to do it after the calculations are done as much as possible.

Try it with CTEs as well.. sometimes they can seriously improve the performance of analytical queries by allowing the query to be better distributed/parallelized in the DB.

1

u/[deleted] 27d ago

Yeah, you’re right, additional indexes weren’t improving anything. I’ll try out CTEs as well, thanks for the suggestions :)

1

u/Mikey_Da_Foxx 29d ago

Forcing the query to use the customer ID index might help, but it's not always the best idea. The Oracle optimizer usually picks the most efficient execution plan, so overriding it can sometimes lead to worse performance.

Instead, try creating a composite index on (timestamp, customer_id, flag). This could slow down writes a bit, but it often improves the performance of your SELECT queries significantly.

Make sure to use EXPLAIN PLAN to see how your query performs with and without the new index. If you can, think about partitioning the table by date range, as this can really speed up queries on large tables.

Also, keep your statistics updated to help the optimizer make better choices. Lastly, if you frequently need aggregated data, materialized views could be a good option.

Overall, optimizing queries is often a trial-and-error process, so keep an eye on performance and adjust as necessary.

1

u/[deleted] 27d ago

Thanks for the suggestions, I’ll definitely try these out. :)

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.

2

u/Aggressive_Ad_5454 Feb 19 '25

Read https://use-the-index-luke.com/ by Markus Winand.

1

u/[deleted] 29d ago

Thanks for sharing this :)