Oracle SQL Query Optimizer - How SQL Optimizer Works -Top 20 SQL Query Optimizer Techniques-Explained

June 07, 2023  

 

Top 20 SQL query optimization techniques! Here is the list of the top 20 SQL query optimization techniques 1. Create an index on very large tables 10,00,00,000 records 2. Use EXIST instead of COUNT to find an element in the table 3. SELECT fields instead of using SELECT * 4. Avoid Subqueries in WHERE clause 5. Avoid SELECT DISTINCT where possible 6. Use WHERE Clause instead of HAVING 7. Create joins with INNER JOIN not WHERE 8. Use LIMIT to sample query results 9. Use UNION ALL instead of UNION wherever possible 10. Use UNION where instead of WHERE... or ... query 11. Run your query during off-peak hours 12.Avoid using OR in join queries 13. Choose GROUP BY over window functions 14. Use derived and temporary tables 15. Drop the index before loading bulk data 16. Use materialized views instead of views 17. Avoid or not equal operator 18. Minimize the number of subqueries 19. Try to user INNER join as little as possible when you can get the same output of using LEFT or RIGHT join. 20. For retrieving the same dataset frequently try to user temporary sources.

Hope this helps!

Regards,

Ramesh Kumar Krishnamoorthy