General guidelines for Query Optimization

When you work on large volumes of data, it becomes very important that you write your queries in the most optimized way possible. As a bad query can delay your reports and may decrease the efficiency of the resources.
Here are some general guidelines for optimizing SQL queries

- Avoid Union operations and replace it with Union All followed by Group by
I have given an explanation for this here: Oracle SQL: Union vs Union All? Union All wins it.

- Choose indexing parameters in such a way that you can use the in your query. Index scans on large amounts of data is very fast when compared to a non index scan

- Partition Pruning: When scanning through enormous amount of data it becomes very important that you restrict the amount of irrelevant data that you don't want to be processed. Involving filters on partitioning columns can do wonders to the query execution time.

These are some of the general guidelines in which you can optimize your query for better performance. Feel free to add any more that you came across 😀