EXPLAIN SELECT in other databases

Question

Hi,

I found EXPLAIN SELECT query 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.

My question is: In databases like MS Sql, Firebird, Ingres, is there a similar command available?

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.

Best regards,

Mauro H. Leggieri

Answer 1

In Oracle:

EXPLAIN PLAN FOR SELECT …

In PostgreSQL:

EXPLAIN SELECT …

In SQL Server:

SET SHOWPLAN_XML ON

GO

SELECT …

GO

Answer 2

In Oracle we have

EXPLAIN PLAN for sql

http://www.adp-gmbh.ch/ora/explainplan.html

In MS SQL Server you can get an text or XML version of the execution plan.

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.

http://msdn.microsoft.com/en-us/library/ms176058.aspx

Something else that is quite handy is

SET STATISTICS IO ON|OFF

Answer 3

For mssql server you can use

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

SET AUTOTRACE ON or EXPLAIN PLAN

(I don't know about firebird or ingres)

Answer 4

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 )

Answer 5

For Ingres, the following will give you the final plan chosen with estimates as to the number of rows, disk IOs and CPU cycles:

set qep

To get the plan but not execute the SELECT also add

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 trace point qe90

See http://docs.ingres.com/Ingres/9.2/SQL%20Reference%20Guide/set.htm for more information on the above.

Answer 6

First, thank you to all people who answered my question.

In the case of Firebird (days ago I test IBExpert trial version), PLAN gives very little information. It shows that FB will use some "index" and no more. I have readed (i don't remember where but I think it was in FB faqs) that, for e.g., if you have an ascending index, it is not useful for a descending order by.

MySQL "explains" that if there is no apropiate index will do a full table scan. FB tries to use any index available and makes one think that doesn't worry if the choosen index is useful or not.

Regards, Mauro.

Answer 7

For Ingres, see also these resources:

Example of Reading and Interpreting a Query Execution Plan (QEP) [pdf]

A brief case study that demonstrates analysis and interpretation of a QEP

Getting Ingres Qep LockTrace Using JDBC

The Query Execution Plan (QEP)