如何使用解释计划来优化查询? [英] How to use Explain Plan to optimize queries?

问题描述

我的任务是在工作中优化一些sql查询.我发现的所有内容都指向使用解释计划"来识别问题区域.我无法确切地知道什么解释计划告诉我的问题.您将获得成本，基数和字节数.

I have been tasked to optimize some sql queries at work. Everything I have found points to using Explain Plan to identify problem areas. The problem I can not find out exactly what explain plan is telling me. You get Cost, Cardinality, and bytes.

这表示什么，以及我应该如何使用它作为指南.低数字更好吗?高更好?任何投入将不胜感激.

What do this indicate, and how should I be using this as a guide. Are low numbers better? High better? Any input would be greatly appreciated.

或者，如果您有更好的方法来优化查询，我会很感兴趣.

Or if you have a better way to go about optimizing a query, I would be interested.

推荐答案

根据您的所做所为，您可以获得比实际更多的收益.查看此解释计划页面.我在这里假设您正在使用Oracle，并且知道如何运行脚本来显示计划输出.首先可能更重要的是查看左侧是否使用特定索引以及如何利用该索引.如果您正在执行联接，则应该看到诸如(Full)"，(By Index Rowid)"之类的内容.成本将是接下来要考虑的问题，成本越低越好，您会注意到，如果执行的联接不使用索引，则可能会产生非常大的成本.您可能还想阅读有关解释计划列的详细信息.

You get more than that actually depending on what you are doing. Check out this explain plan page. I'm assuming a little bit here that you are using Oracle and know how to run the script to display the plan output. What may be more important to start with is looking at the left hand side for the use of a particular index or not and how that index is being utilized. You should see things like "(Full)", "(By Index Rowid)", etc if you are doing joins. The cost would be the next thing to look at with lower costs being better and you will notice that if you are doing a join that is not using an index you may get a very large cost. You may also want to read details about the explain plan columns.

这篇关于如何使用解释计划来优化查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！