![]() The Oracle database, for example, offers “index description” hints to avoid both issues: instead of specifying the index name, it accepts a description of the ideal index (column list) and it selects the index that matches this definition best. Of course there are ways around these problems. Further, if you restrict the optimizer you can no longer expect it to adjust the execution plan if you add another index that servers the query better. Example: if you use a hint to use index ABC for a query, the hint becomes ineffective when somebody changes the name of the index to ABCD. ![]() So, what’s wrong with them? Well, the two main problems are that they (1) restrict the optimizer and that they (2) often need volatile object names as parameters (e.g., index names). Typical examples for restricting query hints are hints that force the database to use or not use a particular index (e.g., INDEX and NO_INDEX in the Oracle database, USE INDEX and IGNORE INDEX in MySQL, or INDEX, FORCESEEK and the like in SQL Server). Quite often it becomes slower and sometimes I even realize that the execution plan I though of does not work at all-at least not with the database I’m working at that moment. It usually goes like this: when I believe a different execution plan could (should?) give better performance, I just hint it to see if it really gives better performance. I do not like restricting hints, yet I use them sometimes to test different execution plans. “Hint” is an incredibly bad name for these things as they force the optimizer to do what it has been told-probably the reason MySQL uses the FORCE keyword for those. ![]() Most query hints are restricting hints: they limit the optimizers’ freedom to choose an execution plan. I distinguish two major types: Restricting Hints The most important fact about query hints is that not all query hints are born equally. However, to answer this question once and forever, I though I should write it down. The answer is more lengthy and probably also more two-fold than most people expect it to be. Quite often I’m asked what I think about query hints.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |