oracle EXPLAIN SELECT 在其他数据库中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1357975/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
EXPLAIN SELECT in other databases
提问by Quassnoi
I found EXPLAIN SELECTquery very useful in MySQL because it gives information on how SQL will be executed and gives the opportunity to analyze, for e.g., missing indexes you should add in order to improve response BEFORE doing the query itself and analyzing stats.
我发现EXPLAIN SELECT查询在 MySQL 中非常有用，因为它提供了有关 SQL 将如何执行的信息，并提供了分析的机会，例如，在执行查询本身和分析统计数据之前，您应该添加以改善响应的缺失索引。
My question is: In databases like MS Sql, Firebird, Ingres, is there a similar command available?
我的问题是：在 MS Sql、Firebird、Ingres 等数据库中，是否有类似的命令可用？
In Firebird we have PLAN, but is very weak because many times one has to run very long queries in order to view a simple mistake.
在 Firebird 中，我们有 PLAN，但它非常弱，因为很多时候人们必须运行很长的查询才能查看一个简单的错误。
Best regards,
此致，
Mauro H. Leggieri
毛罗·H·莱吉里
回答by Quassnoi
In Oracle:
在Oracle：
EXPLAIN PLAN FOR SELECT …
In PostgreSQL:
在PostgreSQL：
EXPLAIN SELECT …
In SQL Server:
在SQL Server：
SET SHOWPLAN_XML ON
GO
SELECT …
GO
回答by olle
For mssql server you can use
对于 mssql 服务器，您可以使用
SET SHOWPLAN_TEXT ON and SET SHOWPLAN_TEXT OFF
this will prevent queries from actually being exectued but it will return they query plan.
这将阻止实际执行查询，但它会返回他们的查询计划。
For oracle you can use
对于 oracle，您可以使用
SET AUTOTRACE ON or EXPLAIN PLAN
(I don't know about firebird or ingres)
（我不知道 firebird 或 ingres）
回答by pjp
In Oraclewe have
在Oracle 中，我们有
EXPLAIN PLAN for sql
http://www.adp-gmbh.ch/ora/explainplan.html
http://www.adp-gmbh.ch/ora/explainplan.html
In MS SQL Serveryou can get an text or XML version of the execution plan.
在MS SQL Server 中，您可以获得文本或 XML 版本的执行计划。
SET SHOWPLAN_XML ON|OFF
SET SHOWPLAN_TEXT ON|OFF
However these are best viewed using the visual tool in Sql Server Management Studio/TOAD.
但是，最好使用 Sql Server Management Studio/TOAD 中的可视化工具查看这些。
http://msdn.microsoft.com/en-us/library/ms176058.aspx
http://msdn.microsoft.com/en-us/library/ms176058.aspx
Something else that is quite handy is
另一件很方便的事情是
SET STATISTICS IO ON|OFF
回答by grantc
For Ingres, the following will give you the final plan chosen with estimates as to the number of rows, disk IOs and CPU cycles:
对于 Ingres，以下将为您提供选择的最终计划，其中包含对行数、磁盘 IO 和 CPU 周期的估计：
set qep
To get the plan but not execute the SELECT also add
要获取计划但不执行 SELECT 还添加
set optimizeonly
re-enable query execution:
重新启用查询执行：
set nooptimizeonly
to get the the actual statistics for the executed query, to compare with the output from "set qep":
获取已执行查询的实际统计信息，与“set qep”的输出进行比较：
set trace point qe90
See http://docs.ingres.com/Ingres/9.2/SQL%20Reference%20Guide/set.htmfor more information on the above.
有关上述内容的更多信息，请参阅http://docs.ingres.com/Ingres/9.2/SQL%20Reference%20Guide/set.htm。
回答by Adrian
For Ingres, see also these resources:
对于 Ingres，另请参阅以下资源：
Example of Reading and Interpreting a Query Execution Plan (QEP) [pdf]
A brief case study that demonstrates analysis and interpretation of a QEP
一个简短的案例研究，展示了 QEP 的分析和解释
Getting Ingres Qep LockTrace Using JDBC
使用 JDBC 获取 Ingres Qep LockTrace
回答by Jaimal Chohan
MS SQL has a utility in Management Studio called Display Execution Plan (Estimated and Exact) when executing a query. it can also display statistics for the query (run time, number of rows, traffic etc )
在执行查询时，MS SQL 在 Management Studio 中有一个名为 Display Execution Plan (Estimated and Exact) 的实用程序。它还可以显示查询的统计信息（运行时间、行数、流量等）
