登录查看更多内容

How can you optimize SQL queries with query hinting?

由人工智能和领英社区提供技术支持

Query hinting is a technique that allows you to influence the behavior of the SQL query optimizer by providing hints or suggestions on how to execute a query. Query hinting can help you improve the performance, efficiency, and accuracy of your SQL queries, especially when dealing with complex or large data sets. However, query hinting also requires careful and deliberate use, as it can have unintended consequences or override the default optimizations of the query engine. In this article, you will learn what query hinting is, why it is useful, how to apply it, and what are some common pitfalls to avoid.

此文章中的业界达人

由社区从 9 条内容中精选。了解更多

1 What is query hinting?

Query hinting is a way of adding optional keywords or clauses to your SQL statements that instruct the query optimizer on how to process the query. The query optimizer is the component of the database engine that analyzes the query and decides on the best execution plan, which is the sequence of steps and operations that will retrieve the data from the database. The query optimizer uses various factors, such as statistics, indexes, constraints, and parameters, to determine the optimal execution plan. However, sometimes the query optimizer may not choose the best plan for your specific query, due to various reasons, such as outdated statistics, inaccurate estimates, or lack of information. In such cases, you can use query hinting to override or modify the default behavior of the query optimizer and force it to use a different execution plan that suits your needs.

添加您的观点

Evgeny Puzikov

Oracle/Way4 Technical Expert
举报内容
There is a hint for handling duplicate key violations during an INSERT operation: IGNORE_ROW_ON_DUPKEY_INDEX This hint instructs Oracle to skip the rows that would violate a unique or primary key constraint and continue with the insertion of the remaining rows. On the other hand, other hints like /*+ INDEX() */ or /*+ FULL() */ are used to suggest specific access paths to the optimizer, such as using an index or performing a full table scan. These hints are about influencing the execution plan of a query rather than handling constraint violations.

已翻译

赞
Romain Ferraton

CEO and Founder @ Architecture & Performance | Performance Tuning, Business Intelligence
举报内容
La quantité de données (la cardinalité) est un des principal critères de choix de l'optimiseur pour déterminer le meilleur opérateur. Lors des opérations de jointures ou d'agrégation la cardinalité en sortie n'est pas connue à l'avance et avec des requêtes mettant en jeu plusieurs de ces operations (jointures et/ou agregations) les optimiseurs se trompent la plupart du temps. La question est : se trompent-ils beaucoup ? Cela entra?nant alors un mauvais choix dans les opérateurs (loop join au lieu de hash join par exemple). Plus la requête est complexe et plus le risque d'erreur est important. Le hint est alors une fa?on de remédier manuellement à ces erreurs.

已翻译

赞
Stephane PAQUOT

DBA
举报内容
I always avoid query hinting unless I have no choice !! (Bug). The optimizer has the wrong execution plan ? Having a look before - to statistics - to table and indexes fragmentation (the optimizer may sometimes estimates that a full scan will be better than using indexes due to its level of fragmentation depending of the buffer pools). - to missing covering indexes - to an unoptimized SQL syntax - to pools not large enough for hash, merge.... joins

已翻译

赞
Guillaume Defendini ??

Lead data - Architecte data - DBA -
举报内容
Une bonne définition de la cardinalité est un élément important dans l'optimisation de requête. il faut donc être attentif sur ce point. Autre point à vérifier ce sont les statistiques afin de fournir la meilleure information à votre optimiseur

已翻译

赞

2 Why use query hinting?

Query hinting can be beneficial in various scenarios, such as improving query performance, ensuring accuracy and consistency, solving specific problems, and testing or debugging. It can reduce execution time, resource consumption, or network traffic, as well as avoid non-deterministic results, data anomalies, or concurrency issues. It is particularly useful for complex joins, subqueries, or aggregations that the query optimizer cannot handle well. Additionally, it can be used to compare different execution plans, identify bottlenecks, or isolate errors.

添加您的观点

3 How to use query hinting?

Query hinting is implemented differently by different database systems, so it's important to check the documentation and syntax of your specific database engine before applying query hints. However, some general principles and steps can be followed. Identify the problem or goal you want to achieve with query hinting, such as reducing execution time or ensuring consistent results. Analyze the current execution plan of your query to identify possible causes or factors that affect its performance or accuracy. Then, choose the appropriate query hint that can address the problem or goal. When applying the query hint, take into account the syntax and rules of your database engine. Finally, test and evaluate the impact of the query hint on your query, such as measuring execution time, resource consumption, or result set before and after applying the query hint. Additionally, check for any errors, warnings, or conflicts that may be generated by the query hint.

添加您的观点

4 What are some common query hints?

Query hints are a powerful tool for improving the performance, accuracy, and consistency of your queries. Commonly used query hints include index hints, which can help you avoid full table scans or use more selective indexes, but may reduce the flexibility of your queries. Join hints can help you choose a more efficient or consistent join order or type, but may increase the complexity and risk of your queries. Parallel hints can help you distribute the workload among multiple processors or threads, but may require more memory, CPU, or disk space. Lastly, locking hints can help you prevent or allow data modifications or conflicts, but may affect the availability and scalability of your queries.

添加您的观点

5 What are some pitfalls to avoid?

Query hinting is a powerful and useful technique, but it also comes with some challenges and drawbacks. These include being database-specific and non-standard, making it difficult to maintain and debug, as well as potentially counter-productive and harmful. If you migrate or upgrade your database engine, you may need to rewrite or modify your query hints. Similarly, if your data, schema, or statistics change, you may need to update or remove your query hints. Moreover, it’s important to monitor and evaluate your query hints regularly to ensure that they are still relevant and effective.

添加您的观点

Jeff Smith

Product Manager | Databases | Blogger | Software Development | Cloud | Social | Community Management | Product Marketing
举报内容
Before you try a single hint (Oracle), ensure that your optimizer statistics aren't "lying" about your data. If the database engine doesn't understand because your stats are missing or inadequate, the optimizer is at a big disadvantage. You'll also want to make sure your database has been properly patched. Bugs can affect the optimizer. Once you have added a hint, you'll need to test your query and it's execution plan as the nature of your data changes and as you apply database patches and upgrades. Hints are extremely powerful tools, but should be one of the last ones you pull from your toolbox, not the first and only.

已翻译

赞
Dario Vega

Product Manager @Oracle ?? noSQL | inMemory | OCI | cloud-native | serverless | Architecture | Development
举报内容
As an Oracle DB admin, You can influence the optimizer using several techniques, including SQL profiles, SQL Plan Management, outlines, initialization parameters, and hints. By the way, most of them use hints. You have tools in the market to compare the performances with distinct configs. You can even ask the optimizer to ignore the hints if you want to validate if those hints are still helpful after upgrading or refreshing the stats. It is especially true for some applications when you don't even know why those hints were added. (Not mentioning "SQL quarantines" here) Yes, hints are powerful, and not all the DB has the above mechanisms to avoid rewriting the SQL code. Think about it before rewriting code if you are using Oracle DB.

已翻译

赞
Krishna Ramanathan

Database for living
举报内容
With respect to MySQL partitions - Most overlooked pitfall is that people often miss to hint the partitions in a partitioned table. - This ends up in scanning all the partitions available including the Empty ones.

已翻译

赞
John Nagtzaam

Senior Developer Global Finance Reporting Chapter bij ING Nederland
举报内容
Jeff is right, no surprise. By default the optimizer assumes a uniform distribution of rows across the distinct values in a column. The combined predicates in a where clause of a query can result in too low calculation of cardinalities by the optimizer. Correct histograms are important and extended statistics can help. But sometimes you need hints.

已翻译

赞

6 Here’s what else to consider

This is a space to share examples, stories, or insights that don’t fit into any of the previous sections. What else would you like to add?

添加您的观点

Guillaume Defendini ??

Lead data - Architecte data - DBA -
举报内容
D'après mon expérience les hints sont à éviter autant que possible. En effet ils vont avoir une influence qui peuvent être bénéfique à un moment donnée puis être une véritable catastrophe dans les semaines ou mois à venir. Il existe bien entendu quelques cas où ils peuvent être utiles, comme par exemple contourner un bug d'optimiseur qui en serait pas encore patché mais ils doivent absolument être documenté et revue de manière récurrente.

已翻译

赞

Database Engineering

+ 关注

给文章评分

我们借助人工智能创建了此文章。您认为这篇文章怎么样？

很棒不太好

举报此文章

查看全部

How can you optimize SQL queries with query hinting?

1

2

3

4

5

6

1 What is query hinting?

2 Why use query hinting?

3 How to use query hinting?

4 What are some common query hints?

5 What are some pitfalls to avoid?

6 Here’s what else to consider

Database Engineering

给文章评分

感谢您的反馈

更多Database Engineering相关文章

更多相关阅读内容