Query Optimization
Database Query Optimization
Query optimization is a critical process in database management that transforms high-level SQL queries into efficient execution plans. Developed systematically by IBM researchers in the late 1970s, it aims to minimize execution time, disk I/O, and resource consumption by selecting the most effective access paths and join strategies.
Read the full method
Sign in with a free account to read this section.
Method map
The neighbourhood of related methods — select a node to explore.
When to use it
Query optimization is essential for all database systems handling structured queries. It becomes critical when query performance is unpredictable, queries access large tables, or joins involve multiple relations. Use when execution time must be minimized or resources are limited. Assumptions include stable table statistics and a comprehensive set of available indexes.
Strengths & limitations
- Dramatically reduces query execution time without application code changes
- Adapts to changing data distributions through statistics updates
- Provides systematic approach applicable across different database architectures
- Optimizer accuracy depends on statistics quality and cost model fidelity
- Enumeration of all plans becomes computationally expensive for many joins
- Cannot optimize queries without adequate indexes or statistics
Frequently asked
Why does the same query sometimes run fast and sometimes slow?
Table statistics change as data is modified, causing the optimizer to select different plans. Running ANALYZE or UPDATE STATISTICS refreshes statistics and can improve consistency.
How can I see what plan the optimizer chose?
Use EXPLAIN or EXPLAIN PLAN commands to display the optimizer's chosen execution plan in a tree format showing access methods, join algorithms, and estimated costs.
When should I create an index to help optimization?
Create indexes on columns used in WHERE clauses, join conditions, and ORDER BY clauses that appear in frequent queries. Profile actual queries with EXPLAIN before adding indexes.
Can I force the optimizer to use a specific plan?
Yes, most databases support optimizer hints (e.g., /*+ INDEX() */ in Oracle) to override automatic plan selection, but this should be a last resort after confirming the hint improves performance.
Sources
- Jarke, M., & Koch, J. (1984). Query optimization in database systems. ACM Computing Surveys, 16(2), 111-152. DOI: 10.1145/356924.356928 ↗
- Selinger, P. G., Astrahan, M. M., Chamberlin, D. D., Lorie, R. A., & Price, T. G. (1979). Access path selection in a relational database management system. Proceedings of the 1979 ACM SIGMOD International Conference on Management of Data, 23-34. DOI: 10.1145/582095.582099 ↗
- Garcia-Molina, H., Ullman, J. D., & Widom, J. (2009). Database Systems: The Complete Book (2nd ed.). Pearson Education. link ↗
How to cite this page
ScholarGate. (2026, June 3). Database Query Optimization. ScholarGate. https://scholargate.app/en/information-systems/query-optimization
Which method?
Set this method beside its closest kin and read them side by side — the library lays the books on the table; the choice is yours.
- Database NormalizationInformation Systems↔ compare
- Indexing StrategyInformation Systems↔ compare