Query optimizer - Revision history

Christian75: {{R from merge}}

2017-10-23T10:57:56Z

← Previous revision		Revision as of 10:57, 23 October 2017
Line 1:		Line 1:
	#REDIRECT [[Query optimization]]		#REDIRECT [[Query optimization]]

			{{R from merge}}

Beland: Merge with and redirect to Query optimization

2013-03-14T02:17:27Z

Merge with and redirect to Query optimization

← Previous revision		Revision as of 02:17, 14 March 2013
Line 1:		Line 1:
		⚫	#REDIRECT [[Query optimization]]
	{{merge\|Query optimization\|discuss=Talk:Query optimizer#Merger proposal\|date=October 2012}}

	The '''query optimizer''' is the component of a [[database management system]] that attempts to determine the most efficient way to execute a [[Information retrieval\|query]]. The optimizer considers the possible [[query plan]]s for a given input query, and attempts to determine which of those plans will be the most efficient. Cost-based query optimizers assign an estimated "cost" to each possible query plan, and choose the plan with the smallest cost. Costs are used to estimate the runtime cost of evaluating the query, in terms of the number of I/O operations required, the CPU requirements, and other factors determined from the [[data dictionary]]. The set of query plans examined is formed by examining the possible access paths (e.g. index scan, sequential scan) and [[join algorithm]]s (e.g. [[sort-merge join]], [[hash join]], [[nested loop join]]). The search space can become quite large depending on the complexity of the SQL query.

	Generally, the query optimizer cannot be accessed directly by users: once queries are submitted to database server, and parsed by the parser, they are then passed to the query optimizer where optimization occurs. However, some database engines allow guiding the query optimizer with [[hint (SQL)\|hint]]s.

	==Implementation==
	Most query optimizers represent query plans as a [[Tree data structure\|tree]] of "plan nodes". A plan node encapsulates a single operation that is required to execute the query. The nodes are arranged as a tree, in which intermediate results flow from the bottom of the tree to the top. Each node has zero or more child nodes—those are nodes whose output is fed as input to the parent node. For example, a join node will have two child nodes, which represent the two join operands, whereas a sort node would have a single child node (the input to be sorted). The leaves of the tree are nodes which produce results by scanning the disk, for example by performing an [[index scan]] or a sequential scan.

	===Join ordering===
	The performance of a query plan is determined largely by the order in which the tables are joined. For example, when joining 3 tables A, B, C of size 10 rows, 10,000 rows, and 1,000,000 rows, respectively, a query plan that joins B and C first can take several orders-of-magnitude more time to execute than one that joins A and C first. Most query optimizers determine [[Join (SQL)\|join]] order via a [[dynamic programming]] algorithm pioneered by [[IBM\|IBM's]] [[IBM System R\|System R]] database project {{Citation needed\|date=February 2012}}. This algorithm works in two stages:

	# First, all ways to access each [[Relational model\|relation]] in the query are computed. Every relation in the query can be accessed via a sequential scan. If there is an [[Index (database)\|index]] on a relation that can be used to answer a [[Predicate (computer programming)\|predicate]] in the query, an index scan can also be used. For each relation, the optimizer records the cheapest way to scan the relation, as well as the cheapest way to scan the relation that produces records in a particular sorted order.
	# The optimizer then considers combining each pair of relations for which a join condition exists. For each pair, the optimizer will consider the available join algorithms implemented by the [[Database management system\|DBMS]]. It will preserve the cheapest way to join each pair of relations, in addition to the cheapest way to join each pair of relations that produces its output according to a particular sort order.
	# Then all three-relation query plans are computed, by joining each two-relation plan produced by the previous phase with the remaining relations in the query.

	In this manner, a [[query plan]] is eventually produced that joins all the queries in the relation. Note that the algorithm keeps track of the sort order of the result set produced by a query plan, also called an ''interesting order''. During dynamic programming, one query plan is considered to beat another query plan that produces the same result, only if they produce the same sort order. This is done for two reasons. First, a particular sort order can avoid a redundant sort operation later on in processing the query. Second, a particular sort order can speed up a subsequent join because it clusters the data in a particular way.

	Historically, System-R derived query optimizers would often only consider ''left-deep'' query plans, which first join two [[base table]]s together, then join the intermediate result with another base table, and so on. This heuristic reduces the number of plans that need to be considered (n! instead of 4^n), but may result in not considering the optimal query plan.
	This [[heuristic]] is drawn from the observation that join algorithms such as nested loops only require a single [[tuple]] (aka [[Row (database)\|row]]) of the outer relation at a time. Therefore, a left-deep query plan means that fewer tuples need to be held in memory at any time: the outer relation's join plan need only be executed until a single tuple is produced, and then the inner base relation can be scanned (this technique is called "pipelining").

	Subsequent query optimizers have expanded this [[plan space]] to consider "bushy" query plans, where both operands to a join operator could be intermediate results from other joins. Such bushy plans are especially important in [[parallel computers]] because they allow different portions of the plan to be evaluated independently.

	===Query planning for nested SQL queries===
	A SQL query to a modern relational DBMS does more than just selections and joins. In particular, SQL queries often nest several layers of [[select-project-join\|SPJ]] blocks (Select-Project-Join), by means of [[GROUP BY (SQL)\|group by]], [[exists (SQL operator)\|exists]], and [[not exists]] operators. In some cases such nested SQL queries can be [[flattened]] into a select-project-join query, but not always. Query plans for nested SQL queries can also be chosen using the same dynamic programming algorithm as used for join ordering, but this can lead to an enormous escalation in query optimization time. So some database management systems use an alternative rule-based approach that uses a query graph model.

	===Cost estimation===
	One of the hardest problems in query optimization is to accurately estimate the costs of alternative query plans. Optimizers cost query plans using a mathematical model of query execution costs that relies heavily on estimates of the [[cardinality]], or number of tuples, flowing through each edge in a query plan. Cardinality estimation in turn depends on estimates of the [[Selectivity\|selection factor]]{{dn\|date=October 2012}} of predicates in the query. Traditionally, database systems estimate selectivities through fairly detailed statistics on the distribution of values in each column, such as [[histograms]]. This technique works well for estimation of selectivities of individual predicates. However many queries have [[Logical conjunction\|conjunctions]] of predicates such as <code>select count(*) from R where R.make='Honda' and R.model='Accord'</code>. Query predicates are often highly correlated (for example, <code>model='Accord'</code> implies <code>make='Honda'</code>), and it is very hard to estimate the selectivity of the conjunct in general. Poor [[cardinality]] estimates and uncaught correlation are one of the main reasons why query optimizers pick poor query plans. This is one reason why a [[database administrator]] should regularly update the database statistics, especially after major data loads/unloads.

	==See also==
	* [[Join Selection Factor]]
⚫	* [[Query optimization]]
	* [[Sargable\|Sargable Query]]

	==References==
	* {{cite conference
	\| first = Surajit
	\| last = Chaudhuri
	\| authorlink = Surajit Chaudhuri
	\| date = 1998
	\| pages = pages 34–43
	\| title = An Overview of Query Optimization in Relational Systems
	\| doi = 10.1145/275487.275492
	\| url = http://citeseer.ist.psu.edu/chaudhuri98overview.html
	\| booktitle = Proceedings of the ACM Symposium on Principles of Database Systems}}
	* {{cite journal
	\| first = Yannis
	\| last = Ioannidis
	\| title = Query optimization
	\| journal = ACM Computing Surveys
	\| volume = 28
	\| issue = 1
	\| pages = 121–123
	\| date = March 1996
	\| doi = 10.1145/234313.234367
	\| url = http://citeseer.ist.psu.edu/487912.html}}
	* {{Cite journal
	\| first1 = P. G. \| last1 = Selinger
	\| first2 = M. M. \| last2 = Astrahan
	\| first3 = D. D. \| last3 = Chamberlin
	\| first4 = R. A. \| last4 = Lorie
	\| first5 = T. G. \| last5 = Price
	\| author1-link = Patricia Selinger
	\| author3-link = Donald D. Chamberlin
	\| year = 1979
	\| pages = 23–34
	\| contribution = Access Path Selection in a Relational Database Management System
	\| doi = 10.1145/582095.582099
	\| title = Proceedings of the 1979 ACM SIGMOD International Conference on Management of Data
	\| postscript = <!--None-->
	\| isbn = 089791001X}}

	==External links==
	* [http://www.itbully.com/articles/sql-indexing-and-performance-part-3-queries-indexes-and-query-optimizer SQL Indexes, Statistics and the Query Optimizer]

	{{Databases}}

	{{DEFAULTSORT:Query Optimizer}}
	[[Category:Database algorithms]]
	[[Category:Database management systems]]
	[[Category:SQL]]

Addbot: Bot: Migrating 4 interwiki links, now provided by Wikidata on d:q390567 (Report Errors)

2013-02-28T08:35:58Z

Bot: Migrating 4 interwiki links, now provided by Wikidata on d:q390567 (Report Errors)

← Previous revision		Revision as of 08:35, 28 February 2013
Line 80:		Line 80:
	[[Category:Database management systems]]		[[Category:Database management systems]]
	[[Category:SQL]]		[[Category:SQL]]

	[[de:Anfrageoptimierer]]
	[[es:Optimizador de consultas]]
	[[ja:クエリ最適化]]
	[[th:Query optimizer]]

Mindmatrix: Reverted edits by 122.178.123.121 (talk) to last version by EmausBot

2013-02-05T13:31:09Z

Reverted edits by 122.178.123.121 (talk) to last version by EmausBot

← Previous revision		Revision as of 13:31, 5 February 2013
Line 1:		Line 1:
	{{merge\|Query optimization\|discuss=Talk:Query optimizer#Merger proposal\|date=October 2012}}		{{merge\|Query optimization\|discuss=Talk:Query optimizer#Merger proposal\|date=October 2012}}

	The '''query optimizer''' is the ~~anand~~ of a [[database management system]] that attempts to determine the most efficient way to execute a [[Information retrieval\|query]]. The optimizer considers the possible [[query plan]]s for a given input query, and attempts to determine which of those plans will be the most efficient. Cost-based query optimizers assign an estimated "cost" to each possible query plan, and choose the plan with the smallest cost. Costs are used to estimate the runtime cost of evaluating the query, in terms of the number of I/O operations required, the CPU requirements, and other factors determined from the [[data dictionary]]. The set of query plans examined is formed by examining the possible access paths (e.g. index scan, sequential scan) and [[join algorithm]]s (e.g. [[sort-merge join]], [[hash join]], [[nested loop join]]). The search space can become quite large depending on the complexity of the SQL query.		The '''query optimizer''' is the component of a [[database management system]] that attempts to determine the most efficient way to execute a [[Information retrieval\|query]]. The optimizer considers the possible [[query plan]]s for a given input query, and attempts to determine which of those plans will be the most efficient. Cost-based query optimizers assign an estimated "cost" to each possible query plan, and choose the plan with the smallest cost. Costs are used to estimate the runtime cost of evaluating the query, in terms of the number of I/O operations required, the CPU requirements, and other factors determined from the [[data dictionary]]. The set of query plans examined is formed by examining the possible access paths (e.g. index scan, sequential scan) and [[join algorithm]]s (e.g. [[sort-merge join]], [[hash join]], [[nested loop join]]). The search space can become quite large depending on the complexity of the SQL query.

	Generally, the query optimizer cannot be accessed directly by users: once queries are submitted to database server, and parsed by the parser, they are then passed to the query optimizer where optimization occurs. However, some database engines allow guiding the query optimizer with [[hint (SQL)\|hint]]s.		Generally, the query optimizer cannot be accessed directly by users: once queries are submitted to database server, and parsed by the parser, they are then passed to the query optimizer where optimization occurs. However, some database engines allow guiding the query optimizer with [[hint (SQL)\|hint]]s.

122.178.123.121 at 10:19, 5 February 2013

2013-02-05T10:19:11Z

← Previous revision		Revision as of 10:19, 5 February 2013
Line 1:		Line 1:
	{{merge\|Query optimization\|discuss=Talk:Query optimizer#Merger proposal\|date=October 2012}}		{{merge\|Query optimization\|discuss=Talk:Query optimizer#Merger proposal\|date=October 2012}}

	The '''query optimizer''' is the ~~component~~ of a [[database management system]] that attempts to determine the most efficient way to execute a [[Information retrieval\|query]]. The optimizer considers the possible [[query plan]]s for a given input query, and attempts to determine which of those plans will be the most efficient. Cost-based query optimizers assign an estimated "cost" to each possible query plan, and choose the plan with the smallest cost. Costs are used to estimate the runtime cost of evaluating the query, in terms of the number of I/O operations required, the CPU requirements, and other factors determined from the [[data dictionary]]. The set of query plans examined is formed by examining the possible access paths (e.g. index scan, sequential scan) and [[join algorithm]]s (e.g. [[sort-merge join]], [[hash join]], [[nested loop join]]). The search space can become quite large depending on the complexity of the SQL query.		The '''query optimizer''' is the anand of a [[database management system]] that attempts to determine the most efficient way to execute a [[Information retrieval\|query]]. The optimizer considers the possible [[query plan]]s for a given input query, and attempts to determine which of those plans will be the most efficient. Cost-based query optimizers assign an estimated "cost" to each possible query plan, and choose the plan with the smallest cost. Costs are used to estimate the runtime cost of evaluating the query, in terms of the number of I/O operations required, the CPU requirements, and other factors determined from the [[data dictionary]]. The set of query plans examined is formed by examining the possible access paths (e.g. index scan, sequential scan) and [[join algorithm]]s (e.g. [[sort-merge join]], [[hash join]], [[nested loop join]]). The search space can become quite large depending on the complexity of the SQL query.

	Generally, the query optimizer cannot be accessed directly by users: once queries are submitted to database server, and parsed by the parser, they are then passed to the query optimizer where optimization occurs. However, some database engines allow guiding the query optimizer with [[hint (SQL)\|hint]]s.		Generally, the query optimizer cannot be accessed directly by users: once queries are submitted to database server, and parsed by the parser, they are then passed to the query optimizer where optimization occurs. However, some database engines allow guiding the query optimizer with [[hint (SQL)\|hint]]s.

EmausBot: r2.7.3) (Robot: Adding es:Optimizador de consultas

2013-01-09T11:38:37Z

r2.7.3) (Robot: Adding es:Optimizador de consultas

← Previous revision		Revision as of 11:38, 9 January 2013
Line 82:		Line 82:

	[[de:Anfrageoptimierer]]		[[de:Anfrageoptimierer]]
			[[es:Optimizador de consultas]]
	[[ja:クエリ最適化]]		[[ja:クエリ最適化]]
	[[th:Query optimizer]]		[[th:Query optimizer]]

67.252.110.194 at 20:53, 2 November 2012

2012-11-02T20:53:37Z

← Previous revision		Revision as of 20:53, 2 November 2012
Line 1:		Line 1:
	{{merge\|Query ~~butt~~\|discuss=Talk:Query optimizer#Merger proposal\|date=October 2012}}		{{merge\|Query optimization\|discuss=Talk:Query optimizer#Merger proposal\|date=October 2012}}

	The '''query optimizer''' is the ~~ass~~ of a [[database management system]] that attempts to determine the most efficient way to execute a [[Information retrieval\|query]]. The optimizer considers the possible [[query plan]]s for a given input query, and attempts to determine which of those plans will be the most efficient. Cost-based query optimizers assign an estimated "cost" to each possible query plan, and choose the plan with the smallest cost. Costs are used to estimate the runtime cost of evaluating the query, in terms of the number of I/O operations required, the CPU requirements, and other factors determined from the [[data dictionary]]. The set of query plans examined is formed by examining the possible access paths (e.g. index scan, sequential scan) and [[join algorithm]]s (e.g. [[sort-merge join]], [[hash join]], [[nested loop join]]). The search space can become quite large depending on the complexity of the SQL query.		The '''query optimizer''' is the component of a [[database management system]] that attempts to determine the most efficient way to execute a [[Information retrieval\|query]]. The optimizer considers the possible [[query plan]]s for a given input query, and attempts to determine which of those plans will be the most efficient. Cost-based query optimizers assign an estimated "cost" to each possible query plan, and choose the plan with the smallest cost. Costs are used to estimate the runtime cost of evaluating the query, in terms of the number of I/O operations required, the CPU requirements, and other factors determined from the [[data dictionary]]. The set of query plans examined is formed by examining the possible access paths (e.g. index scan, sequential scan) and [[join algorithm]]s (e.g. [[sort-merge join]], [[hash join]], [[nested loop join]]). The search space can become quite large depending on the complexity of the SQL query.

	Generally, the query optimizer cannot be accessed directly by users: once queries are submitted to database server, and parsed by the parser, they are then passed to the query optimizer where optimization occurs. However, some database engines allow guiding the query optimizer with [[hint (SQL)\|hint]]s.		Generally, the query optimizer cannot be accessed directly by users: once queries are submitted to database server, and parsed by the parser, they are then passed to the query optimizer where optimization occurs. However, some database engines allow guiding the query optimizer with [[hint (SQL)\|hint]]s.

67.252.110.194 at 20:53, 2 November 2012

2012-11-02T20:53:01Z

← Previous revision		Revision as of 20:53, 2 November 2012
Line 1:		Line 1:
	{{merge\|Query ~~optimization~~\|discuss=Talk:Query optimizer#Merger proposal\|date=October 2012}}		{{merge\|Query butt\|discuss=Talk:Query optimizer#Merger proposal\|date=October 2012}}

	The '''query optimizer''' is the ass of a [[database management system]] that attempts to determine the most efficient way to execute a [[Information retrieval\|query]]. The optimizer considers the possible [[query plan]]s for a given input query, and attempts to determine which of those plans will be the most efficient. Cost-based query optimizers assign an estimated "cost" to each possible query plan, and choose the plan with the smallest cost. Costs are used to estimate the runtime cost of evaluating the query, in terms of the number of I/O operations required, the CPU requirements, and other factors determined from the [[data dictionary]]. The set of query plans examined is formed by examining the possible access paths (e.g. index scan, sequential scan) and [[join algorithm]]s (e.g. [[sort-merge join]], [[hash join]], [[nested loop join]]). The search space can become quite large depending on the complexity of the SQL query.		The '''query optimizer''' is the ass of a [[database management system]] that attempts to determine the most efficient way to execute a [[Information retrieval\|query]]. The optimizer considers the possible [[query plan]]s for a given input query, and attempts to determine which of those plans will be the most efficient. Cost-based query optimizers assign an estimated "cost" to each possible query plan, and choose the plan with the smallest cost. Costs are used to estimate the runtime cost of evaluating the query, in terms of the number of I/O operations required, the CPU requirements, and other factors determined from the [[data dictionary]]. The set of query plans examined is formed by examining the possible access paths (e.g. index scan, sequential scan) and [[join algorithm]]s (e.g. [[sort-merge join]], [[hash join]], [[nested loop join]]). The search space can become quite large depending on the complexity of the SQL query.

67.252.110.194 at 20:51, 2 November 2012

2012-11-02T20:51:52Z

← Previous revision		Revision as of 20:51, 2 November 2012
Line 1:		Line 1:
	{{merge\|Query optimization\|discuss=Talk:Query optimizer#Merger proposal\|date=October 2012}}		{{merge\|Query optimization\|discuss=Talk:Query optimizer#Merger proposal\|date=October 2012}}

	The '''query optimizer''' is the ~~component~~ of a [[database management system]] that attempts to determine the most efficient way to execute a [[Information retrieval\|query]]. The optimizer considers the possible [[query plan]]s for a given input query, and attempts to determine which of those plans will be the most efficient. Cost-based query optimizers assign an estimated "cost" to each possible query plan, and choose the plan with the smallest cost. Costs are used to estimate the runtime cost of evaluating the query, in terms of the number of I/O operations required, the CPU requirements, and other factors determined from the [[data dictionary]]. The set of query plans examined is formed by examining the possible access paths (e.g. index scan, sequential scan) and [[join algorithm]]s (e.g. [[sort-merge join]], [[hash join]], [[nested loop join]]). The search space can become quite large depending on the complexity of the SQL query.		The '''query optimizer''' is the ass of a [[database management system]] that attempts to determine the most efficient way to execute a [[Information retrieval\|query]]. The optimizer considers the possible [[query plan]]s for a given input query, and attempts to determine which of those plans will be the most efficient. Cost-based query optimizers assign an estimated "cost" to each possible query plan, and choose the plan with the smallest cost. Costs are used to estimate the runtime cost of evaluating the query, in terms of the number of I/O operations required, the CPU requirements, and other factors determined from the [[data dictionary]]. The set of query plans examined is formed by examining the possible access paths (e.g. index scan, sequential scan) and [[join algorithm]]s (e.g. [[sort-merge join]], [[hash join]], [[nested loop join]]). The search space can become quite large depending on the complexity of the SQL query.

	Generally, the query optimizer cannot be accessed directly by users: once queries are submitted to database server, and parsed by the parser, they are then passed to the query optimizer where optimization occurs. However, some database engines allow guiding the query optimizer with [[hint (SQL)\|hint]]s.		Generally, the query optimizer cannot be accessed directly by users: once queries are submitted to database server, and parsed by the parser, they are then passed to the query optimizer where optimization occurs. However, some database engines allow guiding the query optimizer with [[hint (SQL)\|hint]]s.

BD2412: Help needed: Selectivity

2012-10-26T21:39:29Z

Help needed: Selectivity

← Previous revision		Revision as of 21:39, 26 October 2012
Line 26:		Line 26:

	===Cost estimation===		===Cost estimation===
	One of the hardest problems in query optimization is to accurately estimate the costs of alternative query plans. Optimizers cost query plans using a mathematical model of query execution costs that relies heavily on estimates of the [[cardinality]], or number of tuples, flowing through each edge in a query plan. Cardinality estimation in turn depends on estimates of the [[~~selectivity~~\|selection factor]] of predicates in the query. Traditionally, database systems estimate selectivities through fairly detailed statistics on the distribution of values in each column, such as [[histograms]]. This technique works well for estimation of selectivities of individual predicates. However many queries have [[Logical conjunction\|conjunctions]] of predicates such as <code>select count(*) from R where R.make='Honda' and R.model='Accord'</code>. Query predicates are often highly correlated (for example, <code>model='Accord'</code> implies <code>make='Honda'</code>), and it is very hard to estimate the selectivity of the conjunct in general. Poor [[cardinality]] estimates and uncaught correlation are one of the main reasons why query optimizers pick poor query plans. This is one reason why a [[database administrator]] should regularly update the database statistics, especially after major data loads/unloads.		One of the hardest problems in query optimization is to accurately estimate the costs of alternative query plans. Optimizers cost query plans using a mathematical model of query execution costs that relies heavily on estimates of the [[cardinality]], or number of tuples, flowing through each edge in a query plan. Cardinality estimation in turn depends on estimates of the [[Selectivity\|selection factor]]{{dn\|date=October 2012}} of predicates in the query. Traditionally, database systems estimate selectivities through fairly detailed statistics on the distribution of values in each column, such as [[histograms]]. This technique works well for estimation of selectivities of individual predicates. However many queries have [[Logical conjunction\|conjunctions]] of predicates such as <code>select count(*) from R where R.make='Honda' and R.model='Accord'</code>. Query predicates are often highly correlated (for example, <code>model='Accord'</code> implies <code>make='Honda'</code>), and it is very hard to estimate the selectivity of the conjunct in general. Poor [[cardinality]] estimates and uncaught correlation are one of the main reasons why query optimizers pick poor query plans. This is one reason why a [[database administrator]] should regularly update the database statistics, especially after major data loads/unloads.

	==See also==		==See also==

← Previous revision		Revision as of 13:31, 5 February 2013
Line 1:		Line 1:
	{{merge\|Query optimization\|discuss=Talk:Query optimizer#Merger proposal\|date=October 2012}}		{{merge\|Query optimization\|discuss=Talk:Query optimizer#Merger proposal\|date=October 2012}}

	The '''query optimizer''' is the ~~anand~~ of a [[database management system]] that attempts to determine the most efficient way to execute a [[Information retrieval\|query]]. The optimizer considers the possible [[query plan]]s for a given input query, and attempts to determine which of those plans will be the most efficient. Cost-based query optimizers assign an estimated "cost" to each possible query plan, and choose the plan with the smallest cost. Costs are used to estimate the runtime cost of evaluating the query, in terms of the number of I/O operations required, the CPU requirements, and other factors determined from the [[data dictionary]]. The set of query plans examined is formed by examining the possible access paths (e.g. index scan, sequential scan) and [[join algorithm]]s (e.g. [[sort-merge join]], [[hash join]], [[nested loop join]]). The search space can become quite large depending on the complexity of the SQL query.		The '''query optimizer''' is the component of a [[database management system]] that attempts to determine the most efficient way to execute a [[Information retrieval\|query]]. The optimizer considers the possible [[query plan]]s for a given input query, and attempts to determine which of those plans will be the most efficient. Cost-based query optimizers assign an estimated "cost" to each possible query plan, and choose the plan with the smallest cost. Costs are used to estimate the runtime cost of evaluating the query, in terms of the number of I/O operations required, the CPU requirements, and other factors determined from the [[data dictionary]]. The set of query plans examined is formed by examining the possible access paths (e.g. index scan, sequential scan) and [[join algorithm]]s (e.g. [[sort-merge join]], [[hash join]], [[nested loop join]]). The search space can become quite large depending on the complexity of the SQL query.

	Generally, the query optimizer cannot be accessed directly by users: once queries are submitted to database server, and parsed by the parser, they are then passed to the query optimizer where optimization occurs. However, some database engines allow guiding the query optimizer with [[hint (SQL)\|hint]]s.		Generally, the query optimizer cannot be accessed directly by users: once queries are submitted to database server, and parsed by the parser, they are then passed to the query optimizer where optimization occurs. However, some database engines allow guiding the query optimizer with [[hint (SQL)\|hint]]s.

← Previous revision		Revision as of 10:19, 5 February 2013
Line 1:		Line 1:
	{{merge\|Query optimization\|discuss=Talk:Query optimizer#Merger proposal\|date=October 2012}}		{{merge\|Query optimization\|discuss=Talk:Query optimizer#Merger proposal\|date=October 2012}}

	The '''query optimizer''' is the ~~component~~ of a [[database management system]] that attempts to determine the most efficient way to execute a [[Information retrieval\|query]]. The optimizer considers the possible [[query plan]]s for a given input query, and attempts to determine which of those plans will be the most efficient. Cost-based query optimizers assign an estimated "cost" to each possible query plan, and choose the plan with the smallest cost. Costs are used to estimate the runtime cost of evaluating the query, in terms of the number of I/O operations required, the CPU requirements, and other factors determined from the [[data dictionary]]. The set of query plans examined is formed by examining the possible access paths (e.g. index scan, sequential scan) and [[join algorithm]]s (e.g. [[sort-merge join]], [[hash join]], [[nested loop join]]). The search space can become quite large depending on the complexity of the SQL query.		The '''query optimizer''' is the anand of a [[database management system]] that attempts to determine the most efficient way to execute a [[Information retrieval\|query]]. The optimizer considers the possible [[query plan]]s for a given input query, and attempts to determine which of those plans will be the most efficient. Cost-based query optimizers assign an estimated "cost" to each possible query plan, and choose the plan with the smallest cost. Costs are used to estimate the runtime cost of evaluating the query, in terms of the number of I/O operations required, the CPU requirements, and other factors determined from the [[data dictionary]]. The set of query plans examined is formed by examining the possible access paths (e.g. index scan, sequential scan) and [[join algorithm]]s (e.g. [[sort-merge join]], [[hash join]], [[nested loop join]]). The search space can become quite large depending on the complexity of the SQL query.

	Generally, the query optimizer cannot be accessed directly by users: once queries are submitted to database server, and parsed by the parser, they are then passed to the query optimizer where optimization occurs. However, some database engines allow guiding the query optimizer with [[hint (SQL)\|hint]]s.		Generally, the query optimizer cannot be accessed directly by users: once queries are submitted to database server, and parsed by the parser, they are then passed to the query optimizer where optimization occurs. However, some database engines allow guiding the query optimizer with [[hint (SQL)\|hint]]s.

← Previous revision		Revision as of 20:53, 2 November 2012
Line 1:		Line 1:
	{{merge\|Query ~~butt~~\|discuss=Talk:Query optimizer#Merger proposal\|date=October 2012}}		{{merge\|Query optimization\|discuss=Talk:Query optimizer#Merger proposal\|date=October 2012}}

	The '''query optimizer''' is the ~~ass~~ of a [[database management system]] that attempts to determine the most efficient way to execute a [[Information retrieval\|query]]. The optimizer considers the possible [[query plan]]s for a given input query, and attempts to determine which of those plans will be the most efficient. Cost-based query optimizers assign an estimated "cost" to each possible query plan, and choose the plan with the smallest cost. Costs are used to estimate the runtime cost of evaluating the query, in terms of the number of I/O operations required, the CPU requirements, and other factors determined from the [[data dictionary]]. The set of query plans examined is formed by examining the possible access paths (e.g. index scan, sequential scan) and [[join algorithm]]s (e.g. [[sort-merge join]], [[hash join]], [[nested loop join]]). The search space can become quite large depending on the complexity of the SQL query.		The '''query optimizer''' is the component of a [[database management system]] that attempts to determine the most efficient way to execute a [[Information retrieval\|query]]. The optimizer considers the possible [[query plan]]s for a given input query, and attempts to determine which of those plans will be the most efficient. Cost-based query optimizers assign an estimated "cost" to each possible query plan, and choose the plan with the smallest cost. Costs are used to estimate the runtime cost of evaluating the query, in terms of the number of I/O operations required, the CPU requirements, and other factors determined from the [[data dictionary]]. The set of query plans examined is formed by examining the possible access paths (e.g. index scan, sequential scan) and [[join algorithm]]s (e.g. [[sort-merge join]], [[hash join]], [[nested loop join]]). The search space can become quite large depending on the complexity of the SQL query.

	Generally, the query optimizer cannot be accessed directly by users: once queries are submitted to database server, and parsed by the parser, they are then passed to the query optimizer where optimization occurs. However, some database engines allow guiding the query optimizer with [[hint (SQL)\|hint]]s.		Generally, the query optimizer cannot be accessed directly by users: once queries are submitted to database server, and parsed by the parser, they are then passed to the query optimizer where optimization occurs. However, some database engines allow guiding the query optimizer with [[hint (SQL)\|hint]]s.

← Previous revision		Revision as of 20:51, 2 November 2012
Line 1:		Line 1:
	{{merge\|Query optimization\|discuss=Talk:Query optimizer#Merger proposal\|date=October 2012}}		{{merge\|Query optimization\|discuss=Talk:Query optimizer#Merger proposal\|date=October 2012}}

	The '''query optimizer''' is the ~~component~~ of a [[database management system]] that attempts to determine the most efficient way to execute a [[Information retrieval\|query]]. The optimizer considers the possible [[query plan]]s for a given input query, and attempts to determine which of those plans will be the most efficient. Cost-based query optimizers assign an estimated "cost" to each possible query plan, and choose the plan with the smallest cost. Costs are used to estimate the runtime cost of evaluating the query, in terms of the number of I/O operations required, the CPU requirements, and other factors determined from the [[data dictionary]]. The set of query plans examined is formed by examining the possible access paths (e.g. index scan, sequential scan) and [[join algorithm]]s (e.g. [[sort-merge join]], [[hash join]], [[nested loop join]]). The search space can become quite large depending on the complexity of the SQL query.		The '''query optimizer''' is the ass of a [[database management system]] that attempts to determine the most efficient way to execute a [[Information retrieval\|query]]. The optimizer considers the possible [[query plan]]s for a given input query, and attempts to determine which of those plans will be the most efficient. Cost-based query optimizers assign an estimated "cost" to each possible query plan, and choose the plan with the smallest cost. Costs are used to estimate the runtime cost of evaluating the query, in terms of the number of I/O operations required, the CPU requirements, and other factors determined from the [[data dictionary]]. The set of query plans examined is formed by examining the possible access paths (e.g. index scan, sequential scan) and [[join algorithm]]s (e.g. [[sort-merge join]], [[hash join]], [[nested loop join]]). The search space can become quite large depending on the complexity of the SQL query.

	Generally, the query optimizer cannot be accessed directly by users: once queries are submitted to database server, and parsed by the parser, they are then passed to the query optimizer where optimization occurs. However, some database engines allow guiding the query optimizer with [[hint (SQL)\|hint]]s.		Generally, the query optimizer cannot be accessed directly by users: once queries are submitted to database server, and parsed by the parser, they are then passed to the query optimizer where optimization occurs. However, some database engines allow guiding the query optimizer with [[hint (SQL)\|hint]]s.