|
| |
| | Query optimizer - Wikipedia, the free encyclopedia |
 | | The query optimizer is the component of a database management system that is used to analyze queries submitted to database server for execution, and then determine the optimal way to execute the query ( Query plan). | | | Most query optimizers represent query plans as a tree of "plan nodes". | | | Instead, once queries are submitted to database server, and parsed by the parser, they are then passed to the query optimizer where optimization occurs. |
|
http://en.wikipedia.org/wiki/Query_optimizer
|
|
| |
| | The Columbia Query Optimization Project |
| | Optimizers for commercial relational database systems appeared early in the last decade, and optimization for the basic relational model was considered a solved problem by many. | | | Query optimizers are one of the main means by which modern database systems achieve their performance advantages. | | | Query optimizers were already among the largest and most complex modules of database systems, and they have proven difficult to modify and extend to accommodate these areas. |
|
http://www.cs.pdx.edu/~len/columbia.html
|
|
| |
| | Microsoft SQL Server 7.0 Query Processor (Microsoft SQL Server 7 Technical Articles) |
| | There are two major types of query optimizers in relational databases: a syntax-based query optimizer and a cost-based query optimizer. | | | Unlike the syntax-based query optimizer, it is not dependent on the exact syntax of the query or the order of clauses within the query. | | | A syntax-based query optimizer creates a procedural plan for obtaining the answer to a SQL query, but the particular plan it chooses is dependent on the exact syntax of the query and the order of clauses within the query. |
|
http://msdn.microsoft.com/library/en-us/dnsql7/html/queryproc.asp
|
|
| |
| | A Survey of Database Query Optimization and Genetic Algorithms |
| | Nevertheless, because of the optimizer's paramount importance to the robustness and flexibility of a database, it is worthwhile to engage in a survey of the theory behind the rudimentary components of a basic, cost-based query optimizer. | | | Whenever a SQL query is issued, the query is first parsed and then presented to the database's query optimizer before being executed. | | | The query optimizers for some of the most popular commercial-quality databases are estimated to have required about 50 man-years of development. |
|
http://www.earlham.edu/~chrisma/survey.htm
|
|
| |
| | SQL Server Query Hints |
| | FORCE ORDER : Tells the Query Optimizer to JOIN the tables in a query in the order specified in the query, and not to try to optimally reorder them. | | | KEEP PLAN : Tells the Query Optimizer reduce the estimated recompile threshold for a query. | | | MAXDOP number : Tells the Query Optimizer, for this query only, to override the "max degree of parallelism" setting, specifying the maximum number of CPUs that can be used for parallelism, when creating the query execution plan. |
|
http://www.sql-server-performance.com/hints_query.asp
|
|
| |
| | Columbia Query Optimizer |
| | The Columbia Query Optimization Project is a joint research project of David Maier from the Oregon Graduate Institute and Leonard Shapiro from Portland State University. | | | In query optimization, the problem is to find the cheapest plan for a given query, subject to a certain context. | | | Tracing information provided by the optimizer during the optimization process is one way to let users look into the optimization process and see how the optimizer works. |
|
http://www.cs.pdx.edu/~len/Columbia
|
|
| |
| | iAnywhere.com - SQL Anywhere Cost Based Query Optimizer |
| | The first phase of query optimization performed by the SQL Anywhere optimizer is termed query rewrite optimization. | | | Application designers using queries containing coded estimates should re-evaluate such queries periodically to ensure that the access plan chosen by the optimizer due to the override is still optimal. | | | The optimizer is designed to translate queries into an efficient access plan that for the most part is independent of the query's syntax. |
|
http://www.ianywhere.com/whitepapers/cost_based_queryoptimizer.html
|
|
| |
| | A Query Language for XML |
| | Determining that the output of a query conforms to the output DTD statically, i.e., at query compile time, is an open research problem and the subject of future work. | | | He works on query languages, query optimizations, semistructured databases, complex values, object-oriented databases, and database theory. | | | It is simple enough that known database techniques for query optimization, cost estimation, and query rewriting could be extended to XML-QL. |
|
http://www.research.att.com/~mff/files/final.html
|
|
| |
| | LEO: An autonomic query optimizer for DB2 |
| | Deferred learning exploits empirical results from actual executions of queries to validate the optimizer's model incrementally, deduce what part of the optimizer's model is in error, and compute adjustments to the optimizer's model for future query optimizations. | | | By monitoring queries as they execute, the autonomic optimizer compares the optimizer's estimates with actual cardinalities at each step in a QEP, and computes adjustments to its estimates that may be used during future optimizations of similar queries. | | | Feedback gives the greatest improvement to the modeling of queries that are either repetitive or are similar to earlier queries, that is, queries for which the optimizer's model exploits the same statistical information. |
|
http://www.research.ibm.com/journal/sj/421/markl.html
|
|
| |
| | How the optimizer works |
| | The query execution plan in the ISQL statistics window shows the table ordering for the current query and indicates in parentheses the index that was used for each table. | | | With this estimate, the optimizer estimates ten rows in the query. | | | Since the query optimizer is guessing at the number of rows in a result based on the size of tables and particular restrictions used in the WHERE clause, it almost always makes inexact guesses. |
|
http://gu-ddm01.gtunet.georgetown.edu/SQLHelp/00000155.htm
|
|
| |
| | Fernando Gonzalez Prada |
| | Before the Query Optimizer uses an index, the Query Optimizer evaluates the index to see if it is selective enough. | | | Remember, the Query Optimizer bases its decision on the selectivity of an index, and the Query Optimizer uses the index statistics to determine selectivity. | | | Because of this, you would most likely assume that when this query is run through the Query Optimizer, that the Query Optimizer would use the index to produce the requested results. |
|
http://fgonzalezprada.blogspot.com
|
|
| |
| | MySQL Reference Manual :: 7.5.3 Controlling Query Optimizer Performance |
| | The task of the query optimizer is to find an optimal plan for executing an SQL query. | | | MySQL 5.0.1 introduces a new more flexible method for query optimization that allows the user to control how exhaustive the optimizer is in its search for an optimal query evaluation plan. | | | However, when bigger queries are submitted, the time spent in query optimization may easily become the major bottleneck in the server performance. |
|
http://dev.mysql.com/doc/mysql/en/controlling-optimizer.html
|
|
| |
| | [No title] |
| | However, if the query optimizer determines that there will be a relatively large number of rows, the query optimizer will likely decide to use a table scan to resolve the query. | | | Before walking through the steps that the query optimizer goes through in solving your query, it’s important to understand the query optimizer’s goal. | | | What the query optimizer is looking for is a way that it won’t need to re-sort the results once it’s done. |
|
http://techrepublic.com.com/5102-6313-5161673.html
|
|
| |
| | University of Maryland Dynamic Query Optimization Project |
| | During optimization, subgoal orderings and subgoal restrictions identified in the pre-plan are provided to the relational optimizer, and it respects them while producing a good plan for the subgoals in the query. | | | A prototype of the Web Query Optimizer has been implemented and it has been tested against a number of WebSources including the ACM Digital Library. | | | A paper that describes the Web Query Optimizer Efficient Evaluation of Queries in a Mediator for WebSources will appear in the Sigmod 2002 Proceedings. |
|
http://www.umiacs.umd.edu/labs/CLIP/DARPA/mw97.html
|
|
| |
| | K2 - Query Optimizer |
| | After a query has been translated into an abstract syntax tree, which K2 uses to represent queries internally, it is manipulated by applying a series of rewrite rules. | | | K2 has a flexible, extensible query optimizer which uses both rewrite rules and a cost model. | | | It is functional, but does not always choose the optimal form of the query. |
|
http://db.cis.upenn.edu/K2/optimizer.html
|
|
| |
| | Optimizing SQL Server Queries |
| | The query optimizer uses the foundation table as the base table and joins the other tables to it. | | | The output in Figure 18.5 is the result of the query optimizer’s three basic jobs. | | | The optimizer must also decipher whether the SARG can be used by the optimizer to improve performance. |
|
http://www.windowsitlibrary.com/Content/77/18/1.html
|
|
| |
| | Print - SQL Server Statistics: A Useful Query Optimizer Tool |
| | The optimizer uses the density and all_density values to determine whether it is better to conduct a table scan or to use an index to access the data. | | | During query optimization, the optimizer determines whether an index exists for each condition in the WHERE clause. | | | Much of this improvement results from a dramatically improved query optimizer, which now has many more options to consider when deciding the best way to execute your query. |
|
http://www.windowsitpro.com/Articles/Print.cfm?ArticleID=5660
|
|
| |
| | iis,1997 IASTED International Conference on Intelligent Information Systems (IIS '97) |
| | We concluded that the performance of the optimizer, based on the implementation of the semi-naive algorithm, is adequate for most typical queries, but that it could be improved further by rewriting classes using a magic-sets rewrite algorithm. | | | Consequently, the optimizer evaluates a query by repeatedly cycling through all base classes, and the base classes of the base classes. | | | This paper concerns our experiences in developing a query optimizer for the Cyrano prototype federated database system developed at Virginia Tech. |
|
http://doi.ieeecomputersociety.org/10.1109/IIS.1997.645324
|
|
| |
| | Sybase Inc - Performance and Tuning: Adaptive Server Enterprise Query Optimizer (EDB511) 5 Day |
| | Learn techniques to write high performance queries, allowing the optimizer to use the most efficient query plan, and diagnose and correct performance problems with already existing queries. | | | Tune queries and stored procedures for optimal performance using Adaptive Server Query Optimizer. | | | Interpret optimizer statistics, learn how to use simulated statistics and acquire a benchmarking tuning methodology. |
|
http://www.sybase.com/detail?id=1009024
|
|
| |
| | PostgreSQL: Documentation: Manuals: PostgreSQL 8.0: Genetic Query Optimizer |
| | Performance difficulties in exploring the space of possible query plans created the demand for a new optimization technique to be developed. | | | This makes the ordinary PostgreSQL query optimizer inappropriate for queries that join a large number of tables. | | | The DBMS needed to handle large join queries for the inference machine of the knowledge based system. |
|
http://www.postgresql.org/docs/8.0/interactive/geqo.html
|
|
| |
| | Microsoft SQL Server: How the Query Optimizer Uses Statistics |
| | The most favorable index for the query would contain the row_id as the first column and would contain the last_name and first_name columns. | | | In addition to scanning the table in the shown index, the optimizer investigates other indexes that exist in the table. | | | In such a case, the index seek would be followed by a fetch of the corresponding first_name, last_name column values from the base table, with or without a clustered index. |
|
http://www.microsoft.com/sql/techinfo/tips/development/queryopstats.asp
|
|
| |
| | Babu, Shivnath; Bizarro, Pedro: Adaptive Query Processing in the Looking Glass |
| | Despite this large body of interrelated work, no unifying comparison of adaptive query processing techniques or systems has been attempted; we tackle this problem. | | | We identify three families of systems (plan-based, CQ-based, and routing-based), and compare them in detail with respect to the most important aspects of adaptive query processing: plan quality, statistics monitoring and re-optimization, plan migration, and scalability. |
|
http://dbpubs.stanford.edu:8090/pub/2005-1
|
|
| |
| | Software Talk.info - Query Optimizer processing INSERT statement |
| | Before implementing this process, you want to identify how the query optimizer will process the INSERT statement. | | | Software Talk.info - Query Optimizer processing INSERT statement | | | You want to move old data from the orders table to an archive table. |
|
http://www.softwaretalk.info/about214.html
|
|
| |
| | query optimizer |
| | My guess is the query optimizer decides to change the execution plan because | | | I got this simple dynamic query - just a select...where using the exact | | | By the way, total db size is about 60 GB+ now, the table size is near 15 GB. |
|
http://www.lazydba.com/sql/1__6325.html
|
|
|
