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 😀


You are reading an archived discussion.

Related Posts

The current job scenario is such that several above 80% candidates are unable to land up on jobs. What adds more weight age to our resume now a days? Will...
Not sure whether I am allowed to post my blog here. I hope that's fine. Comments welcomed.
Hello CE, I'm doing a project on servlets using NETBeans IDE 7.3. I'm not able to find web.xml file in EEB-INF folder. actually in earlier versions of netbeans we can...
Basically I know that only the source code is freely available to view or you may have to pay for modifying the source code. But what's besides that? I want...
Couple of months back Twitter had twitted that they had hired Cynthia Gaylor from Morgan Stanley to run its corporate development team. Cynthia joined Twitter board with experience in acquisitions...