08-16-2017, 10:42 PM
Query Optimization
For a given query, there are many plans that a database management system
(DBMS) can follow to process it and produce its answer. The final output will be the same in the different methods followed, but the cost(the amount of time that they need to run) will be different. In short, Query optimization is a function of many relational database management systems in which multiple query plans for satisfying a query are examined and a good query plan is identified. There must be a trade-off between the times required to figure out the plan and run the plan. The plan that needs the least time must be chosen. Therefore a DBMS must have a query optimization. enormous cost difference can occur between two alternatives.The resource footprint of various query plans is evaluated by the cost based query optimizers. The CPU path length, amount of disk buffer space, disk storage service time etc are the resources in the computer system that are costed. The possible access paths are examined before forming the set of query plans. depending on the complexity of the SQL query, the search space can become extremely large. The two methods of optimization are:
logical optimization: a sequence of relational algebra is generated to solve the query.
physical optimization: the means of carrying out each operation is determined here.
For full details see:
http://en.wikipediawiki/Query_optimization
http://infolab.stanford.edu/ hyunjung/cs346/ioannidis.pdf
http://scribddoc/39640304/Query-Optimization
http://scribddoc/36880940/Query-Optimization